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!
 
Sort by date Sort by votes
I don't think that's the issue... as of now, since the range is completely empty, it is skipping over the AddRiskRow sub since there is no need to add more space.
There also isn't really anything before i set the rng variable, so I don't think anything else is the issue. Is there a different way of setting a range object equal to a string?
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I just realized something... Since the range is empty, the GetRow Function WorksheetFunction.CountA(rng) should be returning 0... but it's not, instead it's returning 1 and therefore trying to set lastrow equal to something other than 0. I'm not sure what the cause of this is, but that's what's creating the error I think...

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.
         Debug.Print WorksheetFunction.CountA(rng)
            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

Instead of going to the Else, it's going through the initial if. So I went through each cell in the range and realized there was an apostrophe in one of them, which you can't see by just looking at the cells... Hence my issue.

I do have a question regarding that though. I have been using clear contents to clear out all the old data from these... however, apparently this left behind apostrophes. Is this normal?
 
Upvote 0
Range().ClearContents should remove all contents from the cells, including those hidden apostrophes. Are you sure those cells are being included in the range to which ClearContents is applied in your code?
 
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