Macro

M

Mike

Hello
i have an Excel sheet which contains several thousand rows
of data. some of these rows have some blank entries, but
never the first column in these rows which always has an
entry. this entry relates to data in a second worksheet.
what i need to do is fill in the blank entries. the two
worksheets have idenical row headings in row 1, but the
headings are not necessarily in the same column number in
the two worksheets.
the columns i need to fill in the data for are columns
B,E,H,I,J,K,L,M only.
so what needs to happen is this;
for each of these columns find a blank, then using the
data in column1 for that row find the same row (by
matching the data in column 1 for that row) in the second
worksheet and fill in the blank in the first worksheet,
matching the column headings in row1 for both worksheets.
i don't know how to write this but if someone does i would
be very grateful,
thanks
Mike
 
F

Frank Kabel

Hi Mike
try the following macro. You have to change the name of the lookup
sheet

Sub append_data()
Dim wks_target
Dim wks_source
Dim rng_v_lookup
Dim rng_h_lookup
Dim lastrow As Long
Dim col_index As Integer
Dim row_index As Long
Dim col_heading
Dim col_source As Integer
Dim row_source As Long

Set wks_target = ActiveSheet
lastrow = wks_target.Cells(Rows.count, "A").End(xlUp).row
Set wks_source = Worksheets("Lookup_sheet") 'change this line

Set rng_v_lookup = wks_source.Range("A1:A10000")
Set rng_h_lookup = wks_source.Range("A1:X1")
For col_index = 2 To 13
If col_index <> 3 And col_index <> 4 _
And col_index <> 6 And col_index <> 7 Then
col_heading = wks_target.Cells(1, col_index).Value
col_source = Application.WorksheetFunction.Match _
(col_heading, rng_h_lookup, 0)
For row_index = 2 To lastrow
With wks_target.Cells(row_index, col_index)
If .Value = "" Then
On Error Resume Next
row_source = Application.WorksheetFunction.Match _
(wks_target.Cells(row_index, 1), rng_v_lookup, 0)
If Err.Number <> 0 Then
.Value = "N/A"
Else
.Value = wks_source.Cells(row_source,
col_source).Value
End If
On Error GoTo 0
End If
End With
Next row_index
End If
Next col_index
End Sub
 
Top