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:
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 SubCode:
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
