Run time error 1004 to find last row of data

[coconutmango]

I'm new to VBA and I'm trying to create a macros to do the following:


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!

 

[MrKowz]

Welcome to the forums!

It is xLUp, not x1Up.

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

 

[coconutmango]

Oh man I can't believe I missed that. Thank you.

 

[Ruddles]

!

 

[coconutmango]

I don't know if need to create a separate post, but I get a run time error 5 "Invalid procedure call or argument" for the last row

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

 

[MrKowz]

There is no such thing as R2C2, the R1C1 that should be there refers to entering the formula in the R1C1 notation.

Cells("M2:M" & LastRow).Formula[COLOR=red][B]R1C1[/B][/COLOR] = "=Vlookup(RC1,COGSList,2,False)"

 

[coconutmango]

Again thank you. I know this probably posted already somewhere here on the forum, but do you know of a good place to learn VBA specifically for Excel? I bought Mr. Excel's book VBA and Macros Excel 2007, but I need something a little more beginner.

 

[MrKowz]

The macro recorder is a GREAT tool for beginners. Record a macro, then look at the code. Try to understand what each line means, since you know what steps you made in your worksheet.