#### coconutmango

##### New Member

- Joined
- Mar 10, 2011

- Messages
- 10

__What I'm trying to do:__Using spreadsheet "Raw Sales", for every row in column F that has a product ID # of 4 it would look up column B, a date, and compare it to the date in spreadsheet "Cost of Goods", return the Price, which is listed column 2, and then paste that value in column M

**Additional Information**My "Raw Sales" spreadsheet contains 18,222 rows of data, and grows every day. So the first thing I did was try to find the last row of data so that I could use a if loop to loop through the entire spreadsheet.

**Problem I'm having**I get a run time error of 1004, and it points to this row:

FinalRow = WCogs.Cells(Rows.Count,1).End(x1Up).Row

Since I'm stuck on this line I don't know if there are any other problems in my code.

**My code:**Sub COGSCal()

'

' COGSCal Macro

'

'

'Creates the variables

Dim WData As Worksheet

Dim WCogs As Worksheet

'Defines worksheets

Set WSales = Worksheets("Raw Sales")

Set WCogs = Worksheets("Cost of Goods")

'Finds the end of the COGS list

FinalRow = WCogs.Cells(Rows.Count, 1).End(x1Up).Row

'Redefines the COGS list

WCogs.Range("A2:B" & FinalCOGS).Name = "COGSList"

'Finds the last row of data in the Raw Sales Sheet and returns the row number

LastRow = Cells(Rows.Count, 6).End(x1Up).Row

'Searches in Raw Sales sheet starting from row 2 column F for any product ID = 4 and then looks up column B for the date against the COGS date to return the price sold that day

For i = 2 To LastRow

If WSales.Cells(i, 6).Value = 4 Then

Cells(i, 13).EntireColumn.Insert

Cells("M2:M" & LastRow).FormuulaR2C2 = "=Vlookup(RC1,COGSList,2,False)"

End If

Next i

End Sub

I'd appreciate any help. Thank you!