Hi all,
rather than beat about the bush with a ling description, here's what i'm trying to do....
Situation :
i've got the following code so far, but for some reason it isn't working... it appears that it finds nil values in my WB2 and therefore continually exits the 'find' if loop moving to the next WB2 row.
any help greatly appreciated!!
rather than beat about the bush with a ling description, here's what i'm trying to do....
Situation :
- 2 workbooks - WB1 = main data, WB2 = new data (filtered)
- 1 'helper' column on each sheet with data formatted the same (e.g. pn/order no/customer)
- icounter = rows counter for WB2
- look at icounter, find this row in WB2
- take value in helper column of WB2 (Col A) from this row
- find a match for value in WB1 helper column (Col AA)
- for each match found: Copy value from 'L' in row of WB2
- paste WB2 value into WB1 cell 'R'
- increase iCounter value by 1
- repeat until end of rows of WB2
i've got the following code so far, but for some reason it isn't working... it appears that it finds nil values in my WB2 and therefore continually exits the 'find' if loop moving to the next WB2 row.
VBA Code:
iLast1 = ws1.Range("A" & Application.Rows.Count).End(xlUp).Row
Set r = ws1.Range("A9:A15000")
For icounter = 1 To iLast1
exists = False
Set f = r.Find(ws1.Range("A" & icounter).Value, , xlValues, xlWhole)
If Not f Is Nothing Then
Cell = f.Address
Do
If ws1.Range("A" & f.Row).Value = ws.Range("AA" & icounter).Value Then
exists = False
Exit Do
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> Cell
End If
If exists = True Then
ws1.Range("L" & icounter).Copy
ws.Range("R" & ws.Range("R" & Application.Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteAll
End If
ufProgress.LabelProgress.Width = 0
ufProgress.Show
pctDone = icounter / iLast
With ufProgress
.LabelCaption.Caption = pctDone * 100 & "% Complete"
.LabelProgress.Width = pctDone * (.FrameProgress.Width)
End With
DoEvents
Next icounter
Unload ufProgress
Workbooks(Wb2.Name).Close SaveChanges:=False
any help greatly appreciated!!