Method "Range" of object "global" failed

[zack8576]

I have this code that calculates the total number of "Rainguards" in the excel file, adds an additional row to the file with the following information:
column A: copy the information from one row above
column B: add "!"
column C: the total number of Rainguards
column I: add "Purchased"
if column K contains 170 or 580 then column D in the new row will show "F89998", and column K in the new row will show "170 580 Rainguard"
if column K contains 420 or 440 then column D in the new row will show "F89999", and column K in the new row will show "420 440 Rainguard"....etc

see code below, Method "Range" of object "global" failed. I think specifically the range on the If statements are failing.

Public Lr6 As Long
Public c As Range
Sub Rainguards()
    'ADD A ROW FOR THE TOTAL NUMBER OF RAINGUARDS
    lr6 = lr2 + 1                  
    Set c = Range("C" & lr6)   
    With c
        .Offset(, -2).Value = .Offset(-1, -2).Value
        .FormulaR1C1 = "=COUNTIF(R2C11:R" & lr2 & "C11,""Rainguards"")"
        .Value = .Value
        .Offset(, -1).Value = "!"
        .Offset(, 6).Value = "Purchased"
        .Offset(, 8).Value = "Rainguard"
    If Range("K" & i7).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89997"
    ElseIf Range("K" & i7).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89998"
    ElseIf Range("K" & i7).Value Like "*170**580*" And _
       Range("N" & i10).Value Like "*Hernando*" Then
        .Offset(, 1).Value = "F89998U"
    ElseIf Range("K" & i7).Value Like "*420**440*" Then
        .Offset(, 1).Value = "F89999"
    ElseIf Range("K" & i7).Value Like "*667*" Then
        .Offset(, 1).Value = "F90003"
    End If
        .EntireRow.Font.Bold = True
    End With
    If ws1.Range("C" & i7).Value Like "*0*" Then
            ws1.Rows(lr6).Delete
    End If
End Sub

desired output of this code:

 

[NdNoviceHlp]

You have no "lr2" variable. HTH. Dave

 

[zack8576]

You have no "lr2" variable. HTH. Dave

Dave, lr2 was also defined as a Public variable and used in another part of this macro, code updated below

Public lr2 As Long
Public Lr6 As Long
Public c As Range
Sub Rainguards()
    'ADD A ROW FOR THE TOTAL NUMBER OF RAINGUARDS
    lr6 = lr2 + 1                  
    Set c = Range("C" & lr6)   
    With c
        .Offset(, -2).Value = .Offset(-1, -2).Value
        .FormulaR1C1 = "=COUNTIF(R2C11:R" & lr2 & "C11,""Rainguards"")"
        .Value = .Value
        .Offset(, -1).Value = "!"
        .Offset(, 6).Value = "Purchased"
        .Offset(, 8).Value = "Rainguard"
    If Range("K" & i7).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89997"
    ElseIf Range("K" & i7).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89998"
    ElseIf Range("K" & i7).Value Like "*170**580*" And _
       Range("N" & i10).Value Like "*Hernando*" Then
        .Offset(, 1).Value = "F89998U"
    ElseIf Range("K" & i7).Value Like "*420**440*" Then
        .Offset(, 1).Value = "F89999"
    ElseIf Range("K" & i7).Value Like "*667*" Then
        .Offset(, 1).Value = "F90003"
    End If
        .EntireRow.Font.Bold = True
    End With
    If ws1.Range("C" & i7).Value Like "*0*" Then
            ws1.Rows(lr6).Delete
    End If
End Sub

 

[zack8576]

Dave, lr2 was also defined as a Public variable and used in another part of this macro, code updated below

Public lr2 As Long
Public Lr6 As Long
Public c As Range
Sub Rainguards()
    'ADD A ROW FOR THE TOTAL NUMBER OF RAINGUARDS
    lr6 = lr2 + 1                 
    Set c = Range("C" & lr6)  
    With c
        .Offset(, -2).Value = .Offset(-1, -2).Value
        .FormulaR1C1 = "=COUNTIF(R2C11:R" & lr2 & "C11,""Rainguards"")"
        .Value = .Value
        .Offset(, -1).Value = "!"
        .Offset(, 6).Value = "Purchased"
        .Offset(, 8).Value = "Rainguard"
    If Range("K" & i7).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89997"
    ElseIf Range("K" & i7).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89998"
    ElseIf Range("K" & i7).Value Like "*170**580*" And _
       Range("N" & i10).Value Like "*Hernando*" Then
        .Offset(, 1).Value = "F89998U"
    ElseIf Range("K" & i7).Value Like "*420**440*" Then
        .Offset(, 1).Value = "F89999"
    ElseIf Range("K" & i7).Value Like "*667*" Then
        .Offset(, 1).Value = "F90003"
    End If
        .EntireRow.Font.Bold = True
    End With
    If ws1.Range("C" & i7).Value Like "*0*" Then
            ws1.Rows(lr6).Delete
    End If
End Sub

When I tried to pinpoint the issue, debugger was pointing at this line

 If Range("K" & i7).Value Like "*170**580*" Then

 

[NdNoviceHlp]

I think U want...

If Range("K" & (Range("i" & 7).Value)).Value Like "*170**580*" Then

Dave

 

[zack8576]

I think U want...

If Range("K" & (Range("i" & 7).Value)).Value Like "*170**580*" Then

Dave

Thank you Dave, iI think this solved the issue

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave