Identifying the first row after filtering within a for loop

[steveec]

I have a Master file which monitors shipment progress for my company. I update this on a weekly basis with progress on existing shipments (shipments which are already in the master file) and input new shipments. These updates come in the form of a update sheet which is in the same format as the master file. I have automated the input of new shipments, but I am stuck on how I could update the existing shipments progress.

Currently I perform a vlookup manually based off a shipment reference number. This is a pain because there are multiple shipment progress columns and I have to trawl through each new input to remove the vlookup formula.




FOR EXAMPlE: shipment reference is in column "C", shipment pickup is in column "AG", shipment dispatched is in column "AH" and shipment arrived is in column "AI".. and so on. In the master sheet I might have the reference number and pickup date. The update sheet might have the dispatch date for this shipment. I would want to take the dispatch date and copy it into the master file for this shipment.


I essentially want to search for blanks in each column of the master file and copy this data from the update file based on the shipment reference. I cannot copy and past over the master file data because I have added a lot of data manually the previous week which the updated file may not have or agree with.


Thank you for your help




Please see my attempted code:






Sub Eupdatedates()
Dim lr As Long, rw As Long, extRNG As Range
Set wb = Workbooks("masterfile.xlsm")
Set ws = wb.Sheets("RawDATA")
lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row




Set extRNG = Workbooks("updatefile.csv").Worksheets("report").Range("C:AL")




For i = 33 To 38 ' columns which contain dates that need updating


ws.Range("$A$2:$CV$" & lastRow).AutoFilter field:=i, Criteria1:="=" 'filter out cells containing data



lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row ' find last row after filtering
firstRow = Range("Cells(2,i): Column(i)" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row ' indentify first row after filtering
For rw = firstRow To lastRow ' loop through each each empty row cell and perform a vlookup to the update file
Cells(rw, i) = Application.VLookup(Cells(rw, i), extRNG, i - 2, False)
Next rw

Next i






End Sub


I am getting the error with "firstRow = Range("Cells(2,i): Column(i)" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row ' indentify first row after filtering" I cannot find a way to identify the first row within a for loop. I am new to VBA so a lot of ground to make up. Any thoughts would be greatly appreciated!!

 

[steveec]

I have edited the code with the following changes it seems more logical. However it still is not working. Now when I run the code it simple does nothing. it looks correct to me I will come back monday!


Sub ESourcedate()
Set extRNG = Workbooks("updatefile.csv").Worksheets("report").Range("C:AL")
' BinsertNA Macro
'
Set wb = Workbooks("masterfile.xlsm")
Set ws = wb.Sheets("RawDATA")
lastrow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
For i = 33 To 38
For r = 3 To lastrow
If Cells(r, i).Value = " " Then
Cells(r, i).Value = Application.VLookup(Cells(r, 3), extRNG, i - 2, False)
End If
Next r
Next i




End Sub