Hello there!
First post and need a little help... Just started to learn Excel a few days ago and hit a bit of a wall
Here is a sample sheet
I would like to have a VBA script that when run, detects the Weight in the cells in Column F and adds a predefined value to the Price in the cells in Column G if it meets a certain criteria (in this case, if the Price in Column G is > 200, add the defined amount and replace the Base Price in the cells in Column G with the new total)
So far I have the following but I don't think I am on the right path as I feel there has to be an easier way... I'm clueless so far when it comes to For loops and Arrays (have just scratched the surface there). Also not sure if there is another easier function or tool in Excel that would do this? I'd probably prefer a Macro so I can run it across multiple files/sheets...
Option Explicit
Sub AddShipping()
Dim a As Double, b As Double, c As Double, d As Double, e As Double, f As Double, g As Double, h As Double, i As Double, j As Double, BasePrice As Double, PriceA As Double, PriceB As Double, PriceC As Double, PriceD As Double, PriceE As Double, PriceF As Double, PriceG As Double, PriceH As Double, PriceI As Double, PriceJ As Double
' these are the prices corresponding to weights that need to be added to the base price
a = 27: b = 30: c = 33: d = 36: e = 41: f = 50: g = 59: h = 69: i = 79: j = 88
' a corresponds to a value of 1KG, b to 1.5KG etc.
'I know this doesn't work but can you make the BasePrice dynamic somehow so Excel runs through all the cells in the column and updates them all when criteria is met?
BasePrice = Range("G2:G1000").Value
PriceA = BasePrice + a
PriceB = BasePrice + b
PriceC = BasePrice + c
PriceD = BasePrice + d
PriceE = BasePrice + e
PriceF = BasePrice + f
PriceG = BasePrice + g
PriceH = BasePrice + h
PriceI = BasePrice + i
PriceJ = BasePrice + j
If Range("F2:F1000").Value = "1" And BasePrice >= 200 Then
Range("G2:G1000") = PriceA
If Range("F2:F1000").Value = "1.5" And BasePrice >= 200 Then
Range("G2:G1000") = PriceB
If Range("F2:F1000").Value = "2" And BasePrice >= 200 Then
Range("G2:G1000") = PriceC
If Range("F2:F1000").Value = "2.5" And BasePrice >= 200 Then
Range("G2:G1000") = PriceD
' etc. etc.
End If
End Sub
Appreciate any and all suggestions
Cheers,
super
First post and need a little help... Just started to learn Excel a few days ago and hit a bit of a wall
Here is a sample sheet
I would like to have a VBA script that when run, detects the Weight in the cells in Column F and adds a predefined value to the Price in the cells in Column G if it meets a certain criteria (in this case, if the Price in Column G is > 200, add the defined amount and replace the Base Price in the cells in Column G with the new total)
So far I have the following but I don't think I am on the right path as I feel there has to be an easier way... I'm clueless so far when it comes to For loops and Arrays (have just scratched the surface there). Also not sure if there is another easier function or tool in Excel that would do this? I'd probably prefer a Macro so I can run it across multiple files/sheets...
Option Explicit
Sub AddShipping()
Dim a As Double, b As Double, c As Double, d As Double, e As Double, f As Double, g As Double, h As Double, i As Double, j As Double, BasePrice As Double, PriceA As Double, PriceB As Double, PriceC As Double, PriceD As Double, PriceE As Double, PriceF As Double, PriceG As Double, PriceH As Double, PriceI As Double, PriceJ As Double
' these are the prices corresponding to weights that need to be added to the base price
a = 27: b = 30: c = 33: d = 36: e = 41: f = 50: g = 59: h = 69: i = 79: j = 88
' a corresponds to a value of 1KG, b to 1.5KG etc.
'I know this doesn't work but can you make the BasePrice dynamic somehow so Excel runs through all the cells in the column and updates them all when criteria is met?
BasePrice = Range("G2:G1000").Value
PriceA = BasePrice + a
PriceB = BasePrice + b
PriceC = BasePrice + c
PriceD = BasePrice + d
PriceE = BasePrice + e
PriceF = BasePrice + f
PriceG = BasePrice + g
PriceH = BasePrice + h
PriceI = BasePrice + i
PriceJ = BasePrice + j
If Range("F2:F1000").Value = "1" And BasePrice >= 200 Then
Range("G2:G1000") = PriceA
If Range("F2:F1000").Value = "1.5" And BasePrice >= 200 Then
Range("G2:G1000") = PriceB
If Range("F2:F1000").Value = "2" And BasePrice >= 200 Then
Range("G2:G1000") = PriceC
If Range("F2:F1000").Value = "2.5" And BasePrice >= 200 Then
Range("G2:G1000") = PriceD
' etc. etc.
End If
End Sub
Appreciate any and all suggestions
Cheers,
super