[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

object variable or with block variable not set

run time error 91
object variable or with block variable not set

When the code hit this line
" lstRow = Range("A65535").End(xlUp).Row"
I get this error

What would be the proper set statement?

Any help would be appreciated.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set lstRow = Range("A65535").End(xlUp)

If Not Intersect(Target, Range("B15:B23")) Is Nothing Then
    Application.EnableEvents = False
   mysheet.unprotect
 lstRow = Range("A65535").End(xlUp).Row
 If lstRow > 28 Then
    'Rows("28:75").EntireRow.Hidden = False
    'Recalculate if Hybrid Arm
    If Range("Product") = "G23A" Or Range("Product") = "G25A" Or Range("Product") = "G27A" Or Range("Product") = "G2TA" Then
        Rows("28:75").EntireRow.Hidden = False
        Range("D28:D32") = Range("Price1")
        Range("D33:D37") = Range("Price2")
        Range("D38:D42") = Range("Price3")
        Range("D43:D47") = Range("Price4")
        Range("D48:D52") = Range("Price5")
        Range("D53:D57") = Range("Price6")
        Range("D58:D62") = Range("Price7")
        Range("D63:D67") = Range("Price8")
        For x = 28 To 72
            If IsError(Range("M" & x)) Then
                Range("K" & x) = "N/A"
                Range("N" & x) = "N/A"
            ElseIf IsError(Range("G" & x)) Then
                Range("K" & x) = "N/A"
                Range("N" & x) = "N/A"
            Else
                Range("K" & x) = Range("D" & x) + Range("E" & x) + Range("G" & x)
                Range("N" & x) = Range("K" & x) + Range("L" & x) + Range("M" & x)
            End If
        Next x
        lstRow = Range("A65535").End(xlUp).Row
       If lstRow = 72 Then
        For a = 28 To 72
            Note(a) = Range("B" & a)
            If Note(a) = "" Then
                Exit For
            End If
            TotalP(a) = Range("N" & a)
            If TotalP(a) = "N/A" Then
                Rows(a).EntireRow.Hidden = True
            End If
         Next a
         For b = 28 To 72
            For c = 1 To (72 - b)
                If Note(b) = Note(b + c) Then
                    If TotalP(b) >= TotalP(b + c) Then
                        Rows(b + c).EntireRow.Hidden = True
                    Else
                        Rows(b).EntireRow.Hidden = True
                    End If
                End If
            Next c
         Next b
       End If
    End If
0
ca1358
Asked:
ca1358
  • 2
  • 2
1 Solution
 
bruintjeCommented:
Hello ca1358,

you'll need to qualify your range object to be on a sheet like mysheet or activesheet
as in

If Not Intersect(Target, Range("B15:B23")) Is Nothing Then
   Application.EnableEvents = False
   mysheet.unprotect
   lstRow = mysheet.Range("A65535").End(xlUp).Row
.......

and in the rest of your code too, so excel knows what range your code is talking about

hope this helps a bit
bruintje
0
 
ca1358Author Commented:
I am a little dense, could you elaborate.  Thank you!!

0
 
bruintjeCommented:
the error is probably coming from the fact that you say give me the last row from this range

excel asks which range on which sheet and throws an error about the range not being set

so you probably have to reference the range from the sheet object

and without seeing the complete code i'll use what you pasted there

--------
Set mysheet = ActiveSheet

If Not Intersect(Target, Range("B15:B23")) Is Nothing Then
  Application.EnableEvents = False
  mysheet.Unprotect
  lstRow = mysheet.Range("A65535").End(xlUp).Row
  If lstRow > 28 Then
    'Rows("28:75").EntireRow.Hidden = False
    'Recalculate if Hybrid Arm
    If Range("Product") = "G23A" Or Range("Product") = "G25A" Or Range("Product") = "G27A" Or Range("Product") = "G2TA" Then
      With mysheet
        .Rows("28:75").EntireRow.Hidden = False
        .Range("D28:D32") = .Range("Price1")
        .Range("D33:D37") = .Range("Price2")
        .Range("D38:D42") = .Range("Price3")
        .Range("D43:D47") = .Range("Price4")
        .Range("D48:D52") = .Range("Price5")
        .Range("D53:D57") = .Range("Price6")
        .Range("D58:D62") = .Range("Price7")
        .Range("D63:D67") = .Range("Price8")
        For x = 28 To 72
          If IsError(.Range("M" & x)) Then
              .Range("K" & x) = "N/A"
              .Range("N" & x) = "N/A"
          ElseIf IsError(.Range("G" & x)) Then
              .Range("K" & x) = "N/A"
              .Range("N" & x) = "N/A"
          Else
              .Range("K" & x) = .Range("D" & x) + .Range("E" & x) + .Range("G" & x)
              .Range("N" & x) = .Range("K" & x) + .Range("L" & x) + .Range("M" & x)
          End If
        Next x
        lstRow = .Range("A65535").End(xlUp).Row
        If lstRow = 72 Then
          For a = 28 To 72
            Note(a) = .Range("B" & a)
            If Note(a) = "" Then
                Exit For
            End If
            TotalP(a) = .Range("N" & a)
            If TotalP(a) = "N/A" Then
                .Rows(a).EntireRow.Hidden = True
            End If
          Next a
          For b = 28 To 72
            For c = 1 To (72 - b)
                If Note(b) = Note(b + c) Then
                    If TotalP(b) >= TotalP(b + c) Then
                        .Rows(b + c).EntireRow.Hidden = True
                    Else
                        .Rows(b).EntireRow.Hidden = True
                    End If
                End If
            Next c
          Next b
        End If
      End With
    End If
  End If
End If
--------
0
 
ca1358Author Commented:
Thank you for elaborating.      
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now