Variable Value not being passed in one Select Case

N

ncortez

New Member
Joined
Jul 8, 2011
Messages
38
Hello All,

The issue I am having is that I am trying to set a variable (rng) equal to a range. I have six different Select Cases in which I do this, and it works in all but one of them. I'm not quite sure what the reason behind this is. Could someone please look at my code and let me know if they have any ideas? I have highlighted the instance where this has been occurring. I also included other subs that are called, however I do not think these have anything to do with the issue as all the other instances work fine.

Code: 
Private Sub AddTcTemplate()


Dim rng As Range
Dim lrow As Long
Dim ws As Worksheet
Set ws = Worksheets("R&O")
Dim l As Long
Dim j As Long
Dim k As Long


ws.Select


If Me.cbo_type = "Opportunity" Then
    ws.Select
    'Find range max boundary
    l = Application.WorksheetFunction.Match("RISK", Range("A1:A1500"), 0)
     j = l - 2


    Select Case Me.cbo_probability
        Case "High"
            Set rng = Range("B5:F" & j)
            
            'If all rows are filled, add new row
            If Application.WorksheetFunction.CountBlank(rng) = 0 Then
                Call AddOppRow
              End If
            
            'If all rows are not filled, add to next blank row
            lrow = GetRow(rng)
                If lrow = 0 Then
                    lrow = 5
                Else
                    lrow = lrow + 1
                 End If
            
            'Format to blue when new
              Range("B" & lrow & ":F" & lrow).Select
                With Selection.Font
                    .Color = -3394765
                    .TintAndShade = 0
                End With
                
            'Add values to block
            ws.Cells(lrow, 2).Value = Me.tbo_ID.Value
            ws.Cells(lrow, 3).Value = Me.tbo_item.Value
            ws.Cells(lrow, 4).Value = Me.tbo_amt.Value
            
            If Me.tbo_investment.Value = "" Or IsNull(Me.tbo_investment.Value) Then
                ws.Cells(lrow, 5) = "'"
            Else
                ws.Cells(lrow, 5) = Me.tbo_investment.Value
            End If


            If Trim(Me.tbo_ECD.Value) = "" Or IsNull(Me.tbo_ECD.Value) Then
                ws.Cells(lrow, 6) = "'"
             Else
                ws.Cells(lrow, 6) = Me.tbo_ECD.Value
            End If
            
        Case "Medium"
            Set rng = Range("G5:K" & j)
            
            'If all rows are filled, add new row
            If Application.WorksheetFunction.CountBlank(rng) = 0 Then
                Call AddOppRow
            End If
            
            'If all rows are not filled, add to next blank row
             lrow = GetRow(rng)
                If lrow = 0 Then
                    lrow = 5
                Else
                    lrow = lrow + 1
                 End If
                 
            'Format to blue when new
              Range("G" & lrow & ":K" & lrow).Select
                With Selection.Font
                    .Color = -3394765
                    .TintAndShade = 0
                End With
          
            ws.Cells(lrow, 7).Value = Me.tbo_ID.Value
            ws.Cells(lrow, 8).Value = Me.tbo_item.Value
            ws.Cells(lrow, 9).Value = Me.tbo_amt.Value
            
            If Me.tbo_investment.Value = "" Or IsNull(Me.tbo_investment.Value) Then
                ws.Cells(lrow, 10) = "'"
             Else
                 ws.Cells(lrow, 10) = Me.tbo_investment.Value
             End If


            If Trim(Me.tbo_ECD.Value) = "" Or IsNull(Me.tbo_ECD.Value) Then
                ws.Cells(lrow, 11) = "'"
            Else
                ws.Cells(lrow, 11) = Me.tbo_ECD.Value
            End If
            
        Case "Low"
            Set rng = Range("L5:P" & j)
            
            'If all rows are filled, add new row
            If Application.WorksheetFunction.CountBlank(rng) = 0 Then
                Call AddOppRow
            End If
            'If all rows are not filled, add to next blank row
             lrow = GetRow(rng)
                If lrow = 0 Then
                    lrow = 5
                Else
                    lrow = lrow + 1
                 End If
            
                'Format to blue when new
              Range("L" & lrow & ":P" & lrow).Select
                With Selection.Font
                    .Color = -3394765
                    .TintAndShade = 0
                End With
            
            ws.Cells(lrow, 12).Value = Me.tbo_ID.Value
            ws.Cells(lrow, 13).Value = Me.tbo_item.Value
            ws.Cells(lrow, 14).Value = Me.tbo_amt.Value
            
            If Me.tbo_investment.Value = "" Or IsNull(Me.tbo_investment.Value) Then
                ws.Cells(lrow, 15) = "'"
            Else
                ws.Cells(lrow, 15) = Me.tbo_investment.Value
            End If


            If Trim(Me.tbo_ECD.Value) = "" Or IsNull(Me.tbo_ECD.Value) Then
                ws.Cells(lrow, 16) = "'"
            Else
                ws.Cells(lrow, 16) = Me.tbo_ECD.Value
            End If
            
    End Select
    
    'Recalculate grand total
    
    HighTotal = Application.Sum(Range(Cells(5, 4), Cells(j, 4)))
    MediumTotal = Application.Sum(Range(Cells(5, 9), Cells(j, 9)))
    LowTotal = Application.Sum(Range(Cells(5, 14), Cells(j, 14)))
    
    NewTotal = (HighTotal * 0.9) + (MediumTotal * 0.5) + (LowTotal * 0.1)
    NewTotal = -NewTotal
       
    k = l - 1
    ws.Cells(k, 14) = NewTotal
    
ElseIf Me.cbo_type = "Risk" Then


    'Find range max boundary
    l = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    j = l - 1


    Select Case Me.cbo_probability
        Case "High"
    
     [COLOR="#0000FF"]    [B][SIZE=5]Set rng = Range("B17:F" & j)[/SIZE][/B][/COLOR]
            'If all rows are filled, add new row
            If Application.WorksheetFunction.CountBlank(rng) = 0 Then
               Call AddRiskRow
            End If
            
            'If all rows are not filled, add to next blank row
                lrow = GetRow(rng)
                If lrow = 0 Then
                    lrow = 17
                Else
                    lrow = lrow + 1
                 End If
                    
             'Format to blue when new
              Range("B" & lrow & ":F" & lrow).Select
                With Selection.Font
                    .Color = -3394765
                    .TintAndShade = 0
                End With
               
            ws.Cells(lrow, 2).Value = Me.tbo_ID.Value
            ws.Cells(lrow, 3).Value = Me.tbo_item.Value
            ws.Cells(lrow, 4).Value = Me.tbo_amt.Value
            ws.Cells(lrow, 5).Value = "'"
            ws.Cells(lrow, 6).Value = Me.tbo_ECD.Value
            
        Case "Medium"
            Set rng = Range("G17:K" & j)
            
            'If all rows are filled, add new row
            If Application.WorksheetFunction.CountBlank(rng) = 0 Then
                 Call AddRiskRow
            End If
            'If all rows are not filled, add to next blank row
                lrow = GetRow(rng)
                
               If lrow = 0 Then
                    lrow = 17
                Else
                    lrow = lrow + 1
                 End If
                
            'Format to blue when new
              Range("G" & lrow & ":K" & lrow).Select
                With Selection.Font
                    .Color = -3394765
                    .TintAndShade = 0
                End With
            
            ws.Cells(lrow, 7).Value = Me.tbo_ID.Value
            ws.Cells(lrow, 8).Value = Me.tbo_item.Value
            ws.Cells(lrow, 9).Value = Me.tbo_amt.Value 'K
            ws.Cells(lrow, 10).Value = Me.tbo_investment.Value
            ws.Cells(lrow, 11).Value = Me.tbo_ECD.Value
            
        Case "Low"
            Set rng = Range("L17:P" & j)
            
            'If all rows are filled, add new row
            If Application.WorksheetFunction.CountBlank(rng) = 0 Then
                Call AddRiskRow
            End If
            'If all rows are not filled, add to next blank row
            lrow = GetRow(rng)
            
            If lrow = 0 Then
                lrow = 17
            Else
                lrow = lrow + 1
            End If
            
             'Format to blue when new
              Range("L" & lrow & ":P" & lrow).Select
                With Selection.Font
                    .Color = -3394765
                    .TintAndShade = 0
                End With
            
            ws.Cells(lrow, 12).Value = Me.tbo_ID.Value
            ws.Cells(lrow, 13).Value = Me.tbo_item.Value
            ws.Cells(lrow, 14).Value = Me.tbo_amt.Value
            ws.Cells(lrow, 15).Value = Me.tbo_investment.Value
            ws.Cells(lrow, 16).Value = Me.tbo_ECD.Value
    End Select
   
   'Recalculate grand total
    
    HighTotal = Application.Sum(Range(Cells(17, 4), Cells(j, 4)))
    MediumTotal = Application.Sum(Range(Cells(17, 9), Cells(j, 9)))
    LowTotal = Application.Sum(Range(Cells(17, 14), Cells(j, 14)))
    
    NewTotal = (HighTotal * 0.9) + (MediumTotal * 0.5) + (LowTotal * 0.1)
       
    ws.Cells(l, 14) = NewTotal
End If


  


End Sub


Sub AddOppRow()


Dim l As Long
Dim n As Long


    l = Application.WorksheetFunction.Match("RISK", Range("A1:A1500"), 0)
    n = l - 1
  Rows(n).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    
End Sub
Sub AddRiskRow()


Dim lrow As Long
Dim newRow As Long


 lrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
 newRow = lrow + 1
 
 Range("B" & lrow & ":P" & lrow).Select
    Selection.Cut
    Range("B" & newRow).Select
    ActiveSheet.Paste
    
End Sub
Function GetRow(rng As Range) As Long
    Dim lastrow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("R&O")
    
    If WorksheetFunction.CountA(rng) > 0 Then
         'Search for any entry, by searching backwards by Rows.
            lastrow = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            'If Not Lastrow Is Nothing Then Lastrow.Activate
    Else
        lastrow = 0
        
    End If
    GetRow = lastrow
End Function

Thanks for the help!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Are you sure the variables "l" and "j" are being set to valid numeric values? If you step through your code line-by-line using F8 (instead of clicking Run or pressing F5), you can then hover over variable names to see what values they are storing.

So when you're pressing F8 and get to:
Code: 
'Find range max boundary

    l = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    j = l - 1

    Select Case Me.cbo_probability
        Case "High"
            Set rng = Range("B17:F" & j)
After you pass the "l = Cells.Find" line, hover over the variable "l" and what is its value? Once you get past the "j = l - 1" line, what is the value in "j"? And what is the value in Me.cbo_probability? Is it EXACTLY "High"? Or is there an extra space? Or hidden character?

Are you getting an error?

 
Upvote 0
Yes, l = 27 and j = 26.

I get an error when it calls the function GetRow at the bolded line below. Run-time error '91': Object variable or With block variable not set. I'm assuming it's because rng is set to nothing...

Code: 
Function GetRow(rng As Range) As Long
    Dim lastrow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("R&O")
    
    If WorksheetFunction.CountA(rng) > 0 Then
         'Search for any entry, by searching backwards by Rows.
[B]            lastrow = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _[/B]
[B]            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/B]
            'If Not Lastrow Is Nothing Then Lastrow.Activate
    Else
        lastrow = 0
        
    End If
    GetRow = lastrow
End Function

The value "high" is chosen from a combo box, and it works fine in the "high" probability of type "opportunity", so I don't think this is the problem...

I can't seem to figure out why this works fine in every other case, just not this one. There really is nothing different about this one, especially considering i already have a "high" scenario above, just with "opportunity"...
 
Upvote 0
In your ElseIf construct you are missing:

Code: 
ws.Select

so the active sheet will be used when the range variable is set.
 
Upvote 0
Just tried adding ws.select but somehow rng is still not being set equal to the value I'm specifying...
 
Upvote 0
If you add this at the beginning of your GetRow function what does it return?

Code: 
MsgBox rng.Parent.Name & " " & rng.Address
 
Upvote 0
I tried setting the range to a string variable, so

string =
"B17:F" & j

Then doing rng = Range(string)

When I hover over string, it has the right thing in there "B17:F26", but its not getting set to rng

When I put that line of code in my GetRow function I got back: R&O $B$17:$F$26
That's the right worksheet and range, just not sure why it isn't getting set equal to my rng variable. ​
 
Upvote 0
The range has been set correctly, in which case I'm struggling to understand why this line would cause an error:

Code: 
lastrow = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

What's in B17:F26 when you get the error?
 
Upvote 0
Then why does the value of the range not come up when I hover over rng?

B17:F26 is empty in this scenario. in which case the function returns 0 and ...

Code: 
 lrow = GetRow(rng)
                If lrow = 0 Then
                    lrow = 17
                Else
                    lrow = lrow + 1
                 End If

It works fine in all the other select cases when I do this.
 
Upvote 0
You won't see anything when you hover over the variable because it's an object. We have proved that it has been set using the MsgBox function.

If rng is empty maybe AddRiskRow is causing a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
309
Patriot2879
P
I
  • Question Question
Inoice number not being saved in cell
Replies
6
Views
494
ipbr21054
I
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
769
sspatriots
S
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
126
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,531
Messages
6,179,379
Members
452,907
Latest member
Roland Deschain

Share this page

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
Back
Top