VBA code help with mod

Ron Abba

New Member
Joined
Jun 25, 2020
Messages
12
Office Version
  1. 2016
Good evening,
New to VBA and looking to mod some code to preform an additional step.
VBA Code:
Private Sub commandbutton2_click()
ActiveSheet.Unprotect "national"
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim c1 As Range, c2 As Range, c4 As Range, c5 As Range, wk As String, x As Integer
wk = "WEEK " & Worksheets(1).Range("N2")
                For Each cell In Sheets(1).Range("R2", Sheets(1).Range("IV2").End(xlToLeft))
    If cell <> "" Then
        TotalEmployees = TotalEmployees + 1
    End If
Next cell
If TotalEmployees = 2 Then TotalEmployees = 1
If TotalEmployees = 3 Then TotalEmployees = 3
If TotalEmployees = 4 Then TotalEmployees = 2
For x = 18 To 21 'names of installers
Dim cells As Range
Set c1 = Worksheets(wk).Range("C5:I5").Find(Worksheets(1).Range("I2"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False) 'searches dates
Set c2 = Worksheets(wk).Range("B6:B37").Find(What:=Worksheets(1).cells(2, x), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False) 'searches item list
Set c3 = Sheet1.Range("B5, F5, J5")
Set c4 = Worksheets("Paysheets").Range("c6:c12").Find(Worksheets(1).Range("I2"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
Set c5 = Worksheets("Paysheets").Range("D2,D57,D111,D165,D219").Find(What:=Worksheets(1).cells(2, x), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False) 'Range("D2,D57,D111,D165,D219") names
    If Worksheets("PAY CALCULATOR").cells(2, x) <> "" Then
        If Worksheets(wk).cells(c2.Row, c1.Column) <> "" Then
            ans = MsgBox("$" & Worksheets(1).cells(62, x) & " + " & "$" & Worksheets(wk).cells(c2.Row, c1.Column) & "  = " & "$" & _
                Worksheets(1).cells(62, x) + (Worksheets(wk).cells(c2.Row, c1.Column)) & " You are about to overwrite " & (Worksheets("PAY CALCULATOR").cells(2, x)) & "'s" & " current total! Proceed?", vbYesNo, "Confirmation")
            If ans = vbNo And Worksheets("PAY CALCULATOR").cells(2, 18) <> "" Then Exit Sub
     
End If

            Worksheets(wk).cells(c2.Row, c1.Column) = Worksheets(1).cells(62, x) + (Worksheets(wk).cells(c2.Row, c1.Column))
            'Worksheets("Paysheets").cells(c4.Row, c5.Column).Offset(3, 0) = Worksheets("PAY CALCULATOR").cells(2, x) 'intersection of C4 and C5 than count 3 cells to the right

What Im looking to do is from Worksheets("PAY CALCULATOR") is if S2 = Foreman-ExtraEffort then copy the total in S63($200) and add that total to R2 name and his total in R63($240) on Worksheet(wk).
1612224183322.png


Below is the Worksheet(wk) which is where the total $480 would be copied to once the Save & Clear button is excuted. Date and employee name intersect where the total $480 would be copied to.
1612225171306.png


Any questions please let me know and Thank you for your help in advance.
 

MK64

New Member
Joined
Jan 31, 2021
Messages
7
Ron - I do wonder whether what I said earlier about naming variables the same as keywords/reserved words is the problem here. Where you are referring to a cell or cells as a property of the worksheet, the 'C' automatically change to be in upper case to denote it is a keyword/reserved word, but in your code all references to 'cells' are in lower case, meaning that it is referencing your range variable rather than the Cells property.

My advice would be to change the name of the 'cells' range to 'rngCells', amend all references to that range, then you should fine that all remaining occurances of the Cells property should change to capitalise the 'C' in the reserved word and hopefully the code will work.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,128,017
Messages
5,628,153
Members
416,296
Latest member
smartua

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
Top