Run time error 13 in an If Else

[coconutmango]

Still new to VBA, and I'm on the verge of tearing my hair out. I'm running into a run time error 13 in my If Else statement. What I'm trying to do is run through data in column F "Product ID" of my sheet called "Raw Sales" and if the value is a 4 it uses a vlookup to look up value in column B (a date) and pull column 2 data in spreadsheet Cost of Goods. If the value is a 5 then it does the same process, except it pulls column 3 data from spreadsheet Cost of Goods. I end up with a run time error 13 on this line of code:

WSales.Cells(i, 14).Value = (WSales.Cells(i, 8) - WSales.Cells(i, 13)) * WSales.Cells(i, 7)

Full code:

Sub LoopCOGSCal()
'
' LoopCOGSCal 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
FinalCOGS = WCogs.Range("A" & WCogs.Rows.Count).End(xlUp).Row
 
'Redefines the COGS list
WCogs.Range("A2:C" & 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(xlUp).Row
 
'Inserts the word "Margin" in cell M1
WSales.Range("M1").Value = "Margin"
 
'Inserts the word "Profit" in cell N2
WSales.Range("N1").Value = "Profit"
 
'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
 
'This inserts the Vlookup value of the date on Raw Sales for product ID #4 against the data on COGS, and returns the price
WSales.Cells(i, 13).FormulaR1C1 = "=VLOOKUP(RC2,COGSList,2,False)"
WSales.Cells(i, 14).Value = (WSales.Cells(i, 8) - WSales.Cells(i, 13)) * WSales.Cells(i, 7)
 
Else: WSales.Cells(i, 6).Value = 5
'This inserts the Vlookup value of the date on Raw Sales for productID #5 against the data on COGS, and returns the price
WSales.Cells(i, 13).FormulaR1C1 = "=VLOOKUP(RC2,COGSList,3,False)"
WSales.Cells(i, 14).Value = (WSales.Cells(i, 8) - WSales.Cells(i, 13)) * WSales.Cells(i, 7)
 
End If
Next i
 
End Sub

 

[MrKowz]

Perhaps you meant to have ElseIf there?

Sub LoopCOGSCal()
'
' LoopCOGSCal 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
FinalCOGS = WCogs.Range("A" & WCogs.Rows.Count).End(xlUp).row
'Redefines the COGS list
WCogs.Range("A2:C" & 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(xlUp).row
'Inserts the word "Margin" in cell M1
WSales.Range("M1").Value = "Margin"
'Inserts the word "Profit" in cell N2
WSales.Range("N1").Value = "Profit"
'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
    
        'This inserts the Vlookup value of the date on Raw Sales for product ID #4 against the data on COGS, and returns the price
        WSales.Cells(i, 13).FormulaR1C1 = "=VLOOKUP(RC2,COGSList,2,False)"
        WSales.Cells(i, 14).Value = (WSales.Cells(i, 8) - WSales.Cells(i, 13)) * WSales.Cells(i, 7)
    
    ElseIf WSales.Cells(i, 6).Value = 5 Then
        'This inserts the Vlookup value of the date on Raw Sales for productID #5 against the data on COGS, and returns the price
        WSales.Cells(i, 13).FormulaR1C1 = "=VLOOKUP(RC2,COGSList,3,False)"
        WSales.Cells(i, 14).Value = (WSales.Cells(i, 8) - WSales.Cells(i, 13)) * WSales.Cells(i, 7)
    
    End If
Next i
End Sub

Also, when posting code, please wrap your code in [CODE][/CODE] tags so that it retains indention.

 

[coconutmango]

Thanks for the note on the code. I'll remember that from now on. I tried Else If and it It still has the same run time error.

ElseIf WSales.Cells(i, 6).Value = 5 Then
'This inserts the Vlookup value of the date on Raw Sales for productID #5 against the data on COGS, and returns the price
WSales.Cells(i, 13).FormulaR1C1 = "=VLOOKUP(RC2,COGSList,3,False)"
WSales.Cells(i, 14).Value = (WSales.Cells(i, 8) - WSales.Cells(i, 13)) * WSales.Cells(i, 7)

 

[Norie]

Have you checked if any of the cells in that piece of code refer to cells/ranges with errors?

Or even the wrong data type for a calcualtion, eg text?

PS That line of code doesn't actually appear to be anything to do with an if.

Just saw your last post, and I think you should check if thatVLOOKUP in the first line of code is returning #N/A!.

If it is then that might be what's causing the problem in the second line of code.

 

[coconutmango]

actually cell B2 has the same date as B3, but different product ID and different quantities in Column G. So Column B can have multiple dates with the same product ID.

Column B Column F Column G
1/1/11 4 25
1/1/11 5 35
1/1/11 4 10
1/1/11 4 2

 

[coconutmango]

Before I started the If Else statement I use just a simple If statement to check if product ID was 4, which was the first line of vlookup, and that worked. Then I figured I could add a IfElse statement since I had more than 1 product ID, and that's when things finally went to hell.

 

[Norie]

I really don't think this has anything to do with the If statements.

The line of code you say causes the error seems to be doing some sort of calculation.

One of the cells you are using for that calculation contains a VLOOKUP formula.

If that formula is returning an error, eg N/A, then the code for the calculation won't work.

 

[coconutmango]

Can you clarify what you mean that the cells have a vlookup in them? When I try to run the macros it stops at row 3 with a "#Ref"

 

[Norie]

This line of code puts a VLOOKUP formula in a cell you then use in the next line.

WSales.Cells(i, 13).FormulaR1C1 = "=VLOOKUP(RC2,COGSList,3,False)"

If that formula is returning a #REF! error, or any other error then the next line of code which uses the value (actually error) from that cell will fail.

 

[coconutmango]

Oh well, I still can't figure it out. I just decided to duplicate the code and instead of searching for the value 4 it searches for 5, and it works just fine. Thanks again to everyone who tried to help me.