Run time error 1004 to find last row of data

coconutmango

New Member
Joined
Mar 10, 2011
Messages
10
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!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Welcome to the forums!

It is xLUp, not x1Up.

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

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
images
! :)
 

coconutmango

New Member
Joined
Mar 10, 2011
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
There is no such thing as R2C2, the R1C1 that should be there refers to entering the formula in the R1C1 notation.

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

coconutmango

New Member
Joined
Mar 10, 2011
Messages
10
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top