Vlookup and insert new data into existing row

[dribble]

Hey Guys,

I have been through multiple forums and cant find what I am looking for. I have tried my best at userforms/input forms and straight Excel sheets.

Information layout:
sheet1
-- list lookup on a defined name in sheet 2
-- some vlookups in cells to allow the user to confirm they are looking at the right item
-- 3 x input cells that will be updated in sheet2 based on an action (submit button)

sheet2
--raw data

-- the user will essentially use the list lookup cell to select a item
-- confirm they selected the right item (vlookups)
-- then enter some data into the 3 input cells
-- press a submit button
--## EXCEL - funky VBA then uses the selected list item in sheet1, takes the new data in the input cells (sheet1) and copies these into the sheet2 into the appropriate columns based on the selection in the list lookup on sheet1

This is now green fields as I have gone through too many other peoples code and try to make it work - so I have given up and asking for help now :-(

I am happy to host a form on the sheet2 if that makes it easier, or have it as standard fields on another sheet...

TIA
Lee

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[dribble]

Thanks davesexcel for your fast responce!

my bad! Please find attached an example.

I have coloured the cells in red on sheet1 that I want to insert data into.
These red cells will then be "copied" to the corresponding cells in sheet2 based on the selection made in cell A2 in sheet1 (list drop down)
So if I select "C" in cell A2 on sheet 1 it populates B2 and C2, then I want to enter the data into circuit and logical1 and logical 2.

Press a button of some form (I can create that easy enough). its the coding on the button to select cells D2:F2 and paste them into sheet2 D:F based on the matching "device"

I hope that makes sence?

Cheers

Lee

Book1.xls

[davesexcel]

Check it out.

Hit Alt & F11 to open the VBA editor, you will find the code there.

Sub Button3_Click()
    Dim a As Range, b As Range, c As Range, ws As Worksheet, ws2 As Worksheet
    Dim d As Range, e As Range, f As Range, cs As Range
    Dim Rws As Long, Rng As Range

    Set ws = Sheets(1)
    Set ws2 = Sheets(2)
    Set a = ws.Range("A2")
    Set b = ws.Range("B2")
    Set c = ws.Range("C2")
    '--------
    Set d = ws.Range("D2")
    Set e = ws.Range("E2")
    Set f = ws.Range("F2")
    '---------------


    With ws2
        Rws = .Cells(Rows.Count, "A").End(xlUp).Row
        Set Rng = Range(.Cells(1, 1), .Cells(Rws, 1))
    End With
    For Each cs In Rng.Cells
        If cs = a.Value And cs.Offset(0, 1) = b.Value And cs.Offset(0, 2) = c.Value Then
            cs.Offset(0, 3) = d
            cs.Offset(0, 4) = e
            cs.Offset(0, 5) = f
        End If
    Next cs



End Sub

[dribble]

You're a scholar and a gent!

Thank you so much my hair can start to grow back now! :-D

Problem solved.