I am new to coding and need some assistance. I have created an update button in Excel 2013 that will use the value in column A to fill in values in columns B through H using Index and Match from another spreadsheet. The number of entries in column A will vary and the spreadsheet containing the values for columns B through H has over 6,000 rows and many columns.
I want the code I've written to fill down through the last entry in column A.
Private Sub cmdUpdate_Click()
With ActiveSheet
.Range("B2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("B:B"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("C2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("H:H"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("D2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("I:I"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("E2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("G:G"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("F2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("L:L"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("G2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("D:D"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("H2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("E:E"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
End With
End Sub
Thanks in advance for your help, I appreciate it.
Update: I integrated the code from @Linga as follows. The formulas fill down through the last entry in column A, however it is only copying the data from row 2. It is ignoring the values in column A in successive rows.
Private Sub cmdUpdateWBID_Att_Click()
Dim a As String
a = ActiveCell.Row
With ActiveSheet
.Range("B2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("B:B"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("C2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("H:H"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("D2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("I:I"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("E2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("G:G"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("F2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("L:L"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("G2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("D:D"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
.Range("H2") = Application.WorksheetFunction.Index(Sheets("Sheet2").Range("E:E"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A2"), (Sheets("Sheet2").Range("A:A")), 0))
End With
Range("A2").Select
Selection.End(xlDown).Select
Range("B" & a & ":H" & a).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
End Sub
Update: I wrote a VBA form of the Excel Index and Match formulas in cells B through H. The following formula sits in cell B;
=INDEX(Sheet2!B:B,MATCH(Sheet1!A:A,Sheet2!A:A,0))
A similar formula sits in cells C through H. I wanted to automate this with a button instead of writing 7 formulas and dragging them down. This is an action that I have been repeating a lot with very large datasets.
Sorry, I don't have Snap.