Loop not working - Run-time Error '1004': Select method of range class failed.

Derick63

Board Regular
Joined
Apr 6, 2018
Messages
71
Hello All. I have an issue in my loop and I have no idea why it's not working. Can anyone please help me. It's the only thing (so far) holding me back from completing this project.

In my Payroll project there is a time, every 2 to 3 years, our Government National Insurance Service (a.k.a. NIS) contribution chart needs to be updated with new Employee Insurance Contribution Rates. The Contribution Rates are based on the employee's Gross Wages.


On every month sheet (12 sheets), I have formulas in column K referencing this chart located on another sheet. When an employee wages are within a range, a contribution amount would display in that cell for that employee on that row.
The chart headings are Low-High-Contribution.
Low is the low end of the employee's Gross Wages.
High is the high end of the employee's Gross Wages.
Contribution is the amount per week for the employee's Gross Wages range. This contribution is shown in the cell of column K which has been multiplied by the number of weeks for that month.


Currently, my macro button to incorporate a new NIS Contribution Rate Chart, when clicked, follows this process:
1. Displays an input box window to input the new NIS Contribution Rate Chart effective date in a format of yyyymmdd.
2. Creates a new sheet
3. Names the sheet "NIS" & yyyymmdd" e.g. NIS20181210
4. Formats the chart area, inserts text in specific cells, inserts the yyyymmdd into cell E2 then creates a button and assigns a macro to it.
5. Selects and names the range A4:A22 to "NISLow" & .Range("E2").value 'eg. NISLow20181210
6. Selects and names the range B4:B22 to "NISHigh" & .Range("E2").value 'e.g. NISHigh20181210
7. Selects and names the range C4:C22 to "NISContr" & .Range("E2").value 'e.g. NISContr20181210


After the user enters the Low, High and Contributions values, this newly created button macro on the new sheet, when clicked, follows this process to initiate changes to the array formulas on the 12 sheets in column K that uses the named ranges: (Formula shown below Step 5)
1. Opens a Userform showing a disclaimer warning the user to verify the chart entries. Once verified, the user clicks the Okay button
2. Activates the month in the sheet array (see code Line 14) and selects cell K8 (Header Row, "Employee NIS Contribution" Column)
3. If K9 is blank it should select K9:K208. If K9 is not blank it looks for the last non-blank row then convert the font to red and bold. This action is to signify it is the last entry using the previous NIS Contribution Rates.
4. It then moves to the blank cell in column K directly below and selects from that cell to cell K208
5. The macro will find and replace the old named ranges from the old NIS Rate Chart in the formulas then replace it with the new range names created on the new NISyyyymmdd sheet.


The array formula in K9:K208 is as follows: H9 is Gross Wages. N5 is the number of weeks in that month and D9 is the Salary/Wage amount
{=IFERROR(IF(ISBLANK($D9),"",INDEX(NISContr20160905,MATCH(1,($H9>=NISLow20160905)*($H9<=NISHigh20160905),0))*$N$5),"")}


It does everything up to Step 5 then goes to February. In February, it does not change the last non-blank row font to bold and red as it did in January but stops at the first blank cell in column K and gives me the following error:
"Run-time Error '1004': Select method of range class failed." at Code Line 38 - Private Sub cmdOkay_Click(). I don't know why it gives an error in February because it worked fine in January.


I need the process to go through every month, select K9:K208 if K9 is blank and if it's not blank go to last non-blank cell and repeat Step 2 to Step 5 and end when done with December. If there's a more efficient way to do this I'm all ears.


Notes:
January through December have the same layout, formatting and formulas in the same cells.
Asterisk bound code is a test I did to make sure the loop worked.



Code:
Code:
Option Explicit


Private Sub cmdCancel_Click()
    Unload Me
End Sub


Private Sub cmdOkay_Click()
    Dim arrSht, i
    
    Unload Me
    
    Application.ScreenUpdating = False
    
    arrSht = Array("January", "February", "March", "April", "May", "June", "July", _
             "August", "September", "October", "November", "December")
    
    For i = LBound(arrSht) To UBound(arrSht)
        With Worksheets(arrSht(i))


'****************************************************************************************


' The following test actions work flawlessly looping through January through December
' but got a "Run-time Error '1004': Select method of range class failed."  when I ran
' the full code. Line 38 is where the error occurs when in February.


    'Worksheets(arrSht(i)).Range("A1").Interior.Color = vbYellow
    'Debug.Print Sheets(arrSht(i)).Range("B2").Value 'B2 is the worksheet title


'****************************************************************************************
    
' Find last non-blank row then change font to bold and red which
' indicates last use of old NIS Contribution Rates
    Call LastRowRed 'Exits Sub if K9 (First data entry row) is blank
    
' If K9 is blank, select K9:K208 else select from current cell to K208
        If Worksheets(arrSht(i)).Range("k9").Value = "" Then
        Worksheets(arrSht(i)).Range("K9:K208").Select
        End If
    
    Worksheets(arrSht(i)).Range("Table2").Columns(10).Find(what:="", LookIn:=xlValues, lookat:=xlWhole).Select 'This is Line 38
    Worksheets(arrSht(i)).Range(ActiveCell.Cells, ("K208")).Select
    


' With the above selection, within formulas, find old named ranges then
' replace with new range names from newly created NIS sheet.
'    WAS: Call FindReplace
'    NOW: FindReplace code included below to utilize (arrSht(i)) above
    
' Get named ranges names from New/active NIS rate contribution sheet and
' Old NIS rate contribution sheet in ranges A7:A22, B7:B22 and C7:C22 and set names
    Dim NewNISLow  As String
    Dim NewNISHigh As String
    Dim NewNISContr  As String
    Dim OldNISLow  As String
    Dim OldNISHigh As String
    Dim OldNISContr  As String
    Dim Findtext As String
    Dim Replacetext As String


' .Range("E2").Value = yyyymmdd from inputbox entry.
        OldNISLow = "NISLow" & (Worksheets(Worksheets.Count - 1).Range("E2").Value) ' Old range from sheet before last sheet
        NewNISLow = "NISLow" & (Worksheets(Worksheets.Count).Range("E2").Value) ' New range from last sheet
        OldNISHigh = "NISHigh" & (Worksheets(Worksheets.Count - 1).Range("E2").Value) ' Old range from sheet before last sheet
        NewNISHigh = "NISHigh" & (Worksheets(Worksheets.Count).Range("E2").Value) ' New range from last sheet
        OldNISContr = "NISContr" & (Worksheets(Worksheets.Count - 1).Range("E2").Value) ' Old range from sheet before last sheet
        NewNISContr = "NISContr" & (Worksheets(Worksheets.Count).Range("E2").Value) ' New range from last sheet
        
        Findtext = OldNISLow
        Replacetext = NewNISLow
        Selection.Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=True
            
        Findtext = OldNISHigh
        Replacetext = NewNISHigh
        Selection.Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=True
            
        Findtext = OldNISContr
        Replacetext = NewNISContr
        Selection.Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=True
    
        End With
        
    Next i
    
'When all months have been completed, Admin sheet is activated. Commented out for testing
    'Sheets("admin").Activate
    


End Sub


Code:
Code:
Option Explicit


Sub LastRowRed()
' makes the last non-blank row font red and bold


    Dim lngLastRow As Long, _
        lngActiveRow As Long
    
    Application.ScreenUpdating = False
    
    If ActiveSheet.Range("K9").Value = "" Then
    Exit Sub
    End If
        
    lngLastRow = Cells(Rows.Count, "K").End(xlUp).Row 'K is column
    
    For lngActiveRow = lngLastRow To 2 Step -1
        If Cells(lngActiveRow, "K") <> "" Then
            With Rows(lngActiveRow & ":" & lngActiveRow).Font
            .Color = vbRed
            .Bold = True
            End With
            Exit For
        End If
    Next lngActiveRow
    
    Application.ScreenUpdating = True
    
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
1004 indicates that something is missing, is february spelt correctly, or do you have an additional space
 
Upvote 0
You cannot select ranges on a non-active sheet.
Try adding this line as shown
Code:
  For i = LBound(arrSht) To UBound(arrSht)
      [COLOR=#ff0000] Worksheets(arrSht(i)).Select[/COLOR]
        With Worksheets(arrSht(i))
 
Upvote 0
Hello Mole999. Thanks for the reply. February is spelled correctly in the code and on the worksheet tab. No extra spaces either. The problem was solved by Fluff's suggestion by adding "Worksheets(arrSht(i)).Select"

Thanks again
 
Upvote 0
Hello Fluff.
Thank you, thank you, thank you. That solved the issue and now works like a charm.

You cannot select ranges on a non-active sheet.
Try adding this line as shown
Code:
  For i = LBound(arrSht) To UBound(arrSht)
      [COLOR=#ff0000] Worksheets(arrSht(i)).Select[/COLOR]
        With Worksheets(arrSht(i))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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