VBA to post values via cell address value in worksheet

[Derick63]

Hello everyone,
I created a Payroll Workbook and was just about to complete it when I realized I wanted to include a Corrections worksheet because I don't want the user to go into sensitive worksheets and mess with data they shouldn't be accessing.

So here's my scenario:
In my Corrections worksheet;

A5:F5 - Formulas to retrieve existing data from any of 12 sheets with two criteria (Name & Date)
A6:F6 - Cells to replace the existing data (User Entry)
A7:F7 - Formula to retrieve and display cell address for each value in A5:F5 using two criteria (Name & Date). These cells, A7:F7, are hidden by Format ";;;"

I started to create a module to Update the data by declaring variables for each value of new data (A6:F6) and each of the cell addresses (A7:F7). I don't know how to get the new data into the cell using the cell address variable.

Sub Update()

' The GoTO***** Variables below are taken from Worksheet Corrections Range A7:F7 where a formula
' exists to retrieve the cell address from which the data in A5:F5 resides.
' Worksheet Corrections A6:F6 is the data to replace A5:F5

Dim HrsWkd, HlyDy, BnsCom, Loan, VacBal, SickBal As Integer
Dim GoToHrsWkd, GoToHlyDy, GoToBnsCom, GoToLoan, GoToVacBal, GoToSickBal As String

    
        HrsWkd = Sheets("Corrections").Range("A6").Value   'New Hours
        GoToHrsWkd = Sheets("Corrections").Range("A7")     'Where New Hours will be placed
    
        HlyDy = Sheets("Corrections").Range("B6").Value    'New Holiday Hours
        GoToHlyDy = Sheets("Corrections").Range("B7")      'Where New Holiday Hours will be placed
    
        BnsCom = Sheets("Corrections").Range("C6").Value   'New Bonus/Commissions
        GoToBnsCom = Sheets("Corrections").Range("C7")     'Where New Bonus/Commissions will be placed
    
        Loan = Sheets("Corrections").Range("D6").Value     'New Loan Balance
        GoToLoan = Sheets("Corrections").Range("D7")       'Where New Loan Balance will be placed
    
        VacBal = Sheets("Corrections").Range("E6").Value   'New Vacation Days Balance
        GoToVacBal = Sheets("Corrections").Range("E7")     'Where New Vacation Days Balance will be placed
    
        SickBal = Sheets("Corrections").Range("F6").Value  'New Sick Days Balance
        GoToSickBal = Sheets("Corrections").Range("F7")    'Where New Sick Days Balance will be placed
    
End Sub

Being such a Noob, I tried to figure it out via VBA and wiped out some formulas on the Corrections worksheet and had to start over. Thank goodness for back-ups.
I created a stripped down version of the workbook to include here but XL2BB won't work because the formulas have arrays in them so I have it here on Google Drive Google Drive file location if you want to take a look at it.

Any help would be an education on my part and much appreciated. Thanks!

Derick

 

[Derick63]

If anyone wants to know how I got the Employee's name from the Drop Down List (cell B2) into the macro run button; It'll change when B2 changes

• Select the shape
• In the formula bar, type equal sign (=) then choose the cell, in this case $B$2
• Select the Shape again
• Move the cursor with the left and/or right keys respectively to type your prefix and suffix

 

[Derick63]

I solved it! Thanks!

Sub Update()

' The GoTO***** Variables below are taken from Worksheet Corrections Range A7:F7 where a formula
' exists to retrieve the cell address from which the data in A5:F5 resides.
' Worksheet Corrections A6:F6 is the data to replace A5:F5

Dim HrsWkd, HlyDy, BnsCom, Loan, VacBal, SickBal As Integer
Dim GoToHrsWkd, GoToHlyDy, GoToBnsCom, GoToLoan, GoToVacBal, GoToSickBal As String

    
    HrsWkd = Sheets("Corrections").Range("A6").Value   'New Hours
    GoToHrsWkd = Sheets("Corrections").Range("A7")     'Where New Hours will be placed
    Range(GoToHrsWkd) = HrsWkd
    
    HlyDy = Sheets("Corrections").Range("B6").Value    'New Holiday Hours
    GoToHlyDy = Sheets("Corrections").Range("B7")      'Where New Holiday Hours will be placed
    Range(GoToHlyDy) = HlyDy
    
    BnsCom = Sheets("Corrections").Range("C6").Value   'New Bonus/Commissions
    GoToBnsCom = Sheets("Corrections").Range("C7")     'Where New Bonus/Commissions will be placed
    Range(GoToBnsCom) = BnsCom
    
    Loan = Sheets("Corrections").Range("D6").Value     'New Loan Balance
    GoToLoan = Sheets("Corrections").Range("D7")       'Where New Loan Balance will be placed
    Range(GoToLoan) = Loan
    
    VacBal = Sheets("Corrections").Range("E6").Value   'New Vacation Days Balance
    GoToVacBal = Sheets("Corrections").Range("E7")     'Where New Vacation Days Balance will be placed
    Range(GoToVacBal) = VacBal
    
    SickBal = Sheets("Corrections").Range("F6").Value  'New Sick Days Balance
    GoToSickBal = Sheets("Corrections").Range("F7")    'Where New Sick Days Balance will be placed
    Range(GoToSickBal) = SickBal

End Sub