VAB Code Update Labor Rates

[hiker8117]

Hello All,

New to VBA and just bought a book to learn.

Trying to update labor rates in column B "not null" when column A = FY-19.

I have about 30 excel files and each one tracks the cost of the 30 individual projects. There are about 450 rows that have the individual manufacturing process's and costs are determine by man-hours times labor rate.
Each projects takes about 5 years and I project at the end of each year what I believe the new labor rates will be based on inflation and wage increases.

How can I write VBA code

If Column A = FY-19 and Column B not null change to $34.56

If Column A = FY-20 and Column B not null change to $35.46


I have 5 new projected labor rates FY-19 thru Fy-23.

Appreciate any help

Regards

Gene

 

[footoo]

Here's one way :

Dim lr&, r&, cel As Range
lr = Range([A1], ActiveSheet.UsedRange).Rows.Count
For r = 1 To lr
    Set cel = Cells(r, "B")
    If cel <> "" Then
        Select Case Cells(r, "A")
            Case Is = Cells(r, "FY") - 19: cel = 34.56
            Case Is = Cells(r, "FY") - 20: cel = 35.46
            Case Is = Cells(r, "FY") - 21: cel = 36.36
            Case Is = Cells(r, "FY") - 22: cel = 37.26
            Case Is = Cells(r, "FY") - 23: cel = 38.16
        End Select
    End If
Next

 

[hiker8117]

Thank you I will set this up and give it a try.

Would you mind if I picked your brain with a few questions

The DIM statement declared the variable but did not define the Data type so it becomes a variant correct?
The lr& and r& represent the columns or rows?

Appreciate your help.

Thank you again

Gene

 

[hiker8117]

I gave it a shot on a sample and nothing happened.

Here is a snap shot of the my sample and code

FY-19

FY-20

FY-21

FY-22

FY-23

FY-24

<tbody>
[TD="width: 81"]Fiscal Year
[/TD]
[TD="class: xl65, width: 64"]Rate[/TD]

[TD="class: xl66"]$34.56[/TD]

[TD="class: xl66"]$43.21[/TD]

[TD="class: xl66"]$34.89[/TD]

[TD="class: xl66"]$45.67[/TD]

[TD="class: xl66"]$98.56[/TD]

[TD="class: xl66"]$45.23[/TD]

</tbody>

Code

Dim lr&, r&, cel As Range
lr = Range([A1], ActiveSheet.UsedRange).Rows.Count
For r = 1 To lr
Set cel = Cells(r, "B")
If cel <> "" Then
Select Case Cells(r, "A")
Case Is = Cells(r, "FY") - 19: cel = 34.56
Case Is = Cells(r, "FY") - 20: cel = 35.46
Case Is = Cells(r, "FY") - 21: cel = 36.36
Case Is = Cells(r, "FY") - 22: cel = 37.26
Case Is = Cells(r, "FY") - 23: cel = 38.16
End Select
End If
Next

Appreciate your comments Thank you

<tbody>
[TD="width: 81"][/TD]
[TD="class: xl63, width: 64"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

</tbody>

 

[footoo]

Re your post #3

The variable data types are defined.
The character "&" represents Long. Have a look here : https://docs.microsoft.com/en-us/do.../language-features/data-types/type-characters
"lr" is the number of rows in the range
"r" is a counter from 1 to lr

 

[footoo]

I gave it a shot on a sample and nothing happened.

Here is a snap shot of the my sample and code

Fiscal Year

FY-19

FY-20

FY-21

FY-22

FY-23

FY-24

<tbody>
[TD="class: xl65"]Rate[/TD]

[TD="class: xl66"]$34.56[/TD]

[TD="class: xl66"]$43.21[/TD]

[TD="class: xl66"]$34.89[/TD]

[TD="class: xl66"]$45.67[/TD]

[TD="class: xl66"]$98.56[/TD]

[TD="class: xl66"]$45.23[/TD]

</tbody>

Code

Dim lr&, r&, cel As Range
lr = Range([A1], ActiveSheet.UsedRange).Rows.Count
For r = 1 To lr
Set cel = Cells(r, "B")
If cel <> "" Then
Select Case Cells(r, "A")
Case Is = Cells(r, "FY") - 19: cel = 34.56
Case Is = Cells(r, "FY") - 20: cel = 35.46
Case Is = Cells(r, "FY") - 21: cel = 36.36
Case Is = Cells(r, "FY") - 22: cel = 37.26
Case Is = Cells(r, "FY") - 23: cel = 38.16
End Select
End If
Next

Appreciate your comments Thank you

<tbody>
[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

</tbody>

Sorry, in your original post I read the bit "If Column A = FY-19" to mean "If Column A = Column FY minus 19".
Try this :

Dim lr&, r&, cel As Range
lr = Range([A1], Cells(Rows.Count, "A").End(xlUp)).Rows.Count
For r = 1 To lr
    Set cel = Cells(r, "B")
    If cel <> "" Then
        Select Case Cells(r, "A")
            Case "FY-19": cel = 34.56
            Case "FY-20": cel = 43.21
            Case "FY-21": cel = 34.89
            Case "FY-22": cel = 45.67
            Case "FY-23": cel = 98.56
            Case "FY-24": cel = 45.23
        End Select
    End If
Next