VBA to find row based on cell reference, and then past values

[stephenedger]

Hi Excel gurus,

• I have an Excel 2010 spreadsheet with two main tabs "Input", and "(HIDDEN) RAW DATA".
• "Input" has a row of data (range = CA3:SY3), which I want pasted into a table in "(HIDDEN) RAW DATA".
• Cell "Input!BZ3" contains a person's name, which is variable (dropdown).
• "(HIDDEN) RAW DATA" contains a table (range = E5:QC107).
• E8:E107 contains names. I want the code to find the name in BZ3 in E8:E107, and then paste the row (Input!CA3:SY3) into the identified row.

Here is what I have so far:

Sub Submit_Input()
'
' Copies and pastes values from "Input" to "(HIDDEN) RAW DATA"
'
Application.ScreenUpdating = False
ActiveSheet.Unprotect

'This range remains constant
Range("CA3:SY3").Select
Selection.Copy

'This is where I will be pasting the data
Sheets("(HIDDEN) RAW DATA").Visible = True
Sheets("(HIDDEN) RAW DATA").Select

'I want a vlookup to look up "Input!BZ3" in the range "(HIDDEN) RAW DATA!E8:E107"
Range("??:??").Select

'Once the row is located the copied data should be pasted in column F:QC (of the identified row)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("(HIDDEN) RAW DATA").Visible = xlVeryHidden
Sheets("Input").Select
Range("O5").Select

ActiveSheet.Protect

Application.ScreenUpdating = True

End Sub


The bit I cannot work out is how to write the vlookup (highlighted in RED). Can anybody help me please?

Thanks

Stephen

 

[jbeaucaire]

Something like this is how I would do it, there's no need to unhide the target sheet and "view" it onscreen for VBA to do work on it:

Option Explicit

Sub Submit_Input()
Dim DATArow As Long

With ThisWorkbook.Sheets("(HIDDEN) RAW DATA")

    DATArow = WorksheetFunction.Match(ThisWorkbook.Sheets("Input").Range("BZ3").Value, .Range("E1:E107"), 0)
    If DATArow > 0 Then
        ThisWorkbook.Sheets("Input").Range("CA3:SY3").Copy
        .Range("F" & DATArow).PasteSpecial xlPasteValues
        Beep
        Range("O3").Select
    Else
        MsgBox "Name was not found"
    End If

End With

End Sub

 

[stephenedger]

Holy moly, Jerry!

That worked!!! You are awesome!

What is the reference to "Option Explicit" for? I'm still getting my head around VBA language. Is it needed?

Also, can I remove the MsgBox? The name in cell BZ3 will ALWAYS match the names in CA3:SY3.

Thaks again, you absolute legend!

Stephen

 

[jbeaucaire]

1) Option Explicit at the top of a module basically turns on Excel's builtin "code checking", it forces you to declare all variables before you can use them and it can then spellcheck your entire macro including those variables. It eliminates pretty much 95% of common errors before they occur and get hidden in your code.

2) Yes, you can remove the msgbox, but I wouldn't. Since it will "always" find the name, then it's harmless being there, but the FIRST time something goes wrong with the match you'll be glad it's there.