so the row count comes from sheet supplier list, and the formula also
goes in this same sheet, but the lookup takes place on sheet input
status?

Sub formula()

'Count how many rows are there in a sheet
Dim GRC2 As Integer
Sheets("Supplier list").Select
GRC2 = Range("A65536").End(xlUp).Row
sheets("input status").range(cells(1,1),cells(GRC2,17)).name =
"lookuprange"
sheets("supplier list").range(cells(1,1),cells(17,GRC2)).current
region.name = "fillrange"
Range("K4").Select
ActiveCell.formula = "=VLOOKUP(A4,lookuprange,2,FALSE)"
Selection.AutoFill Destination:=Range("fillrange"),
Type:=xlFillDefault
End Sub


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=556310