Help with VBA Code

[xpizzy]

Hello Everyone,

My goal is to automate a vlook up based on the first column in 3 different sheets. The top row has the headers. I have used record macro and some basic knowledge to get the code working fine. But so far I have hard coded all the formulas to Range "B2:G2".

The end goal is for the macro to run a vlook up anytime there is data in any row in column A of the 3 sheets.

If no data in one of the sheets, i want the code to still run and not throw up an error.

I also want to automate the macro to copy data from columns B from all 3 sheets and paste in rows in column A of a sheet called "Log" and for column B to have the sheet name from the sheet the data was copied from and column C to have the date i run the macro.

Here is my sample code so far



Sheets(Array("1st", "2nd", "Final")).Select
Sheets("1st").Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Main Address List'!C[-1]:C[5],2,FALSE)"

Sheets(Array("1st", "2nd", "Final")).Select
Sheets("1st").Activate
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Main Address List'!C[-2]:C[4],3,FALSE)"

Sheets(Array("1st", "2nd", "Final")).Select
Sheets("1st").Activate
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'Main Address List'!C[-3]:C[3],4,FALSE)"

Sheets(Array("1st", "2nd", "Final")).Select
Sheets("1st").Activate
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'Main Address List'!C[-4]:C[2],5,FALSE)"

Sheets(Array("1st", "2nd", "Final")).Select
Sheets("1st").Activate
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'Main Address List'!C[-5]:C[1],6,FALSE)"

Sheets(Array("1st", "2nd", "Final")).Select
Sheets("1st").Activate
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'Main Address List'!C[-6]:C[0],7,FALSE)"

Sheets(Array("1st", "2nd", "Final")).Select
Sheets("1st").Activate
Range("B2:G2").Copy
Range("B2:G2").PasteSpecial xlPasteValues

Application.CutCopyMode = False


Sheets("Processing").Activate

 

[offthelip]

I thinkl I have understood what you are trying to do. I assume that you are entering a name in column A of sheet 1 and you want to find this name in column A of the Main Address list and then copy the columns B to G on that row to columns B to G of sheet 1 on the row you entered the name
This is what this code does, if you put it in the Worksheet code for any or all the sheet 1 to 3 it will work. It will trigger automatically if you enter something in column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column = 1 Then
        inarr = Target
        With Worksheets("Main Address List")
        lastadd = .Cells(Rows.Count, "A").End(xlUp).Row
        addarr = .Range(.Cells(1, 1), .Cells(lastadd, 7))
        End With
        Dim temparr(1 To 1, 1 To 7)
        Rowno = Target.Row
         For i = 2 To lastadd
          If UCase(addarr(i, 1)) = UCase(inarr) Then
             For j = 1 To 7
               temparr(1, j) = addarr(i, j)
             Next j
             Application.EnableEvents = False
             Range(Cells(Rowno, 1), Cells(Rowno, 7)) = temparr
             Application.EnableEvents = True
             Exit For
          End If
         Next i
  
    End If
End If
End Sub

 

[xpizzy]

I thinkl I have understood what you are trying to do. I assume that you are entering a name in column A of sheet 1 and you want to find this name in column A of the Main Address list and then copy the columns B to G on that row to columns B to G of sheet 1 on the row you entered the name
This is what this code does, if you put it in the Worksheet code for any or all the sheet 1 to 3 it will work. It will trigger automatically if you enter something in column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column = 1 Then
        inarr = Target
        With Worksheets("Main Address List")
        lastadd = .Cells(Rows.Count, "A").End(xlUp).Row
        addarr = .Range(.Cells(1, 1), .Cells(lastadd, 7))
        End With
        Dim temparr(1 To 1, 1 To 7)
        Rowno = Target.Row
         For i = 2 To lastadd
          If UCase(addarr(i, 1)) = UCase(inarr) Then
             For j = 1 To 7
               temparr(1, j) = addarr(i, j)
             Next j
             Application.EnableEvents = False
             Range(Cells(Rowno, 1), Cells(Rowno, 7)) = temparr
             Application.EnableEvents = True
             Exit For
          End If
         Next i
 
    End If
End If
End Sub

Thanks for your help.