Hello
i have an Excel spreadsheet that contains 6 columns.
column1 is always populated. columns 2-6 contain other
information. the entries for columns 2-6 for any row
depend on the value on column1 for that row.
now there are several instances where there are blank
values in columns 2-6 for some rows, even though column1
for that row has an entry.
i need to write a macro that will for each of columns 2-6
find a blank cell, then perform a vlookup based on the
value in column1 for that row, then go to the next blank
and do the same thing, then repeat for each of columns 2-6.
the vlookup will find the missing data in another Excel
spreadsheet.
as this changes each month i want to use a macro to
automate this as opposed to manually doing vlookup each
month
i have no idea how to write such a macro. can anyone give
me some ideas,
thanks a lot
Mike

One way:

Public Sub FillInData()
Dim rSource As Range
Dim rCell As Range
Dim rFound As Range

Set rSource = _
Workbooks("Workbook2.xls").Sheets("Sheet1").Range( "A:A")
For Each rCell In Columns("B:F").SpecialCells( _
xlCellTypeBlanks)
Set rFound = rSource.Find( _
What:=Cells(rCell.Row, 1).Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then _
rCell.Value = rFound(1, rCell.Column)
Next rCell
End Sub

I assumed that your source workbook would also have 6 columns. If it has
only two, change

rCell.Value = rFound(1, rCell.Column)

to

rCell.Value = rFound(1, 2)


Note that Workbook2 must be open before starting this macro.

In article ,
"MikeM" wrote:

Hello
i have an Excel spreadsheet that contains 6 columns.
column1 is always populated. columns 2-6 contain other
information. the entries for columns 2-6 for any row
depend on the value on column1 for that row.
now there are several instances where there are blank
values in columns 2-6 for some rows, even though column1
for that row has an entry.
i need to write a macro that will for each of columns 2-6
find a blank cell, then perform a vlookup based on the
value in column1 for that row, then go to the next blank
and do the same thing, then repeat for each of columns 2-6.
the vlookup will find the missing data in another Excel
spreadsheet.
as this changes each month i want to use a macro to
automate this as opposed to manually doing vlookup each
month
i have no idea how to write such a macro. can anyone give
me some ideas,
thanks a lot
Mike