Expert: Bob Umlas - 11/1/2009

Question
Hi
I have been trying to record and execute a macro involving two separate workbooks but am not being successful.  I don’t know enough about VB to try to write one on my own.  Your assistance is very much appreciated.
I have two workbooks (WB1 & WB2) with only one worksheet in each.  Here is what I am trying to accomplish.

Column D of WB1 contains ID numbers.
Find that ID number in column F of WB2.
If the ID number matches, change/update/insert the characters “VAC” in adjacent cells of same row in columns H, I, J, K, and L of WB2.
Return to WB1, move to next cell down in column D of WB1 and repeat steps for finding, matching, and updating until all ID numbers in WB1 are processed.

thanks

Answer
Sub InsertVac()
  Dim n as Long
  Set WB2=Workbooks("WB2.xls").Sheets(1)
  Set WB1=Workbooks("WB1.xls").Sheets(1)
  On Error Resume Next
  For i=1 to WB2.range("F65536").end(xlup).row
     n=0
     n=Application.Match(wb2.cells(i,6).value,wb1.range("D:D"),0)
     if n<>0 then
        for j=8 to 12
           wb2.cells(i,j).value = "VAC" & wb2.cells(i,j).value
        Next
     End If
  Next
End Sub