VBA to copy, multiply, roundup and paste values

[Supernerd81]

Hi,
I am trying to automate our price increases. Unfortulately I don't know much about excel vba. What I want:
Take the old price in column C, multiply by the value in F2, roundup to the nearest 10 and paste it in column B.
I am currently using a formula which I copy paste every row:

=ROUNDUP((C2*$F$2);-1)

I am trying to automate this with a macro, but thus far I have no succes. This is what I have:

Sub Copypaste()
Dim SrchRng As Range, cel As Range
Set SrchRng = Range("C2:C200")
For Each cel In SrchRng
    If cel.Value <> "" Then
        cel.Offset(0, -1).Value = cel.Value
    End If
Next cel
End Sub

This at least copies my values in column B (and leaves the 'price on request') but I still have to multiply the entire column. Does anyone have a suggestion on how I can do that? The simple ROUNDUP function doesn't seem to work in VBA?

Thanks!

 

[Supernerd81]

Sorry if I am being unclear.

- If the cell contains a number, copy paste and multiply (and round up to the nearest 10).
- If the cell contains text, just copy paste
- If the cell contains formula (= sign), just copy paste
- If the cell contains nothing, do nothing.

I can't edit my post anymore, how annoying. Below is a mini sheet of what I want the result to be. The blue cells can be values, it contains the roundup formula (this is how I manually make the sheet).

Book1.xlsx
	A	B	C	D	E	F
1		New price	Old price
2	Apple	€ 2.370	€ 2.250		Increase prices by:	1,05
3	Pear	€ 3.420	€ 3.250
4	Banana	Price on request	Price on request		Special tomato price	€ 11.590
5	Tomato	€ 11.590	€ 11.590
6	Potato	€ 140	€ 125
7	Beer	€ 20	€ 10
Sheet1

Cell Formulas
Range	Formula
B2:B3,B6:B7	B2	=ROUNDUP((C2*$F$2),-1)
B5:C5	C5	=$F$4

 

[Legacy 143009]

Sub test()
  Dim oldPrices As Variant, newPrices As Variant, increaseRate As Double
  With Application
  oldPrices = .Transpose(Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Formula)
  ReDim newPrices(1 To UBound(oldPrices), 1 To 1)
  increaseRatio = Range("F2").Value2
 
  For i = 1 To UBound(oldPrices)
    If IsNumeric(oldPrices(i)) Then
      newPrices(i, 1) = .WorksheetFunction.RoundUp(oldPrices(i) * increaseRate, -1)
    Else
       newPrices(i, 1) = oldPrices(i)
    End If
  Next
  End With
 
  Range("B2").Resize(UBound(newPrices)) = newPrices
End Sub

If cell is a number, then do the math.
Else, (it can be eiter a formula, text, or empty) juct copy. It should work. I am not sure if it will paste the formula as text like "=A1+B2" or an actual working formula. I can't test it now. Please let me know. Also let me know if empty results displayed as 0.

 

[Supernerd81]

Sub test()
  Dim oldPrices As Variant, newPrices As Variant, increaseRate As Double
  With Application
  oldPrices = .Transpose(Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Formula)
  ReDim newPrices(1 To UBound(oldPrices), 1 To 1)
  increaseRatio = Range("F2").Value2
 
  For i = 1 To UBound(oldPrices)
    If IsNumeric(oldPrices(i)) Then
      newPrices(i, 1) = .WorksheetFunction.RoundUp(oldPrices(i) * increaseRate, -1)
    Else
       newPrices(i, 1) = oldPrices(i)
    End If
  Next
  End With
 
  Range("B2").Resize(UBound(newPrices)) = newPrices
End Sub

If cell is a number, then do the math.
Else, (it can be eiter a formula, text, or empty) juct copy. It should work. I am not sure if it will paste the formula as text like "=A1+B2" or an actual working formula. I can't test it now. Please let me know. Also let me know if empty results displayed as 0.

This is the solution! It does exactly what I want, many thanks! It copies the formula in the old price column, just like I need. Empty cells are kept empty!

You made one typo, at some point you mixed up "increaseRate" and "increaseRatio". Maybe that's what was wrong in the last one as well.

(Man.. This makes me want to learn VBA, this is so useful!)

 

[Legacy 143009]

I am happy it did work finally
Oh yes.. Absolutely you are right. It is my bad. I assign a value to increaseRate. assignedRatio is basically stayed 0 all time. That's why you had a bunch of zeros each time you multiply with 0 value whatever the number is.

I can help you anytime. I can explain what the code does in detail another time. You can DM me anytime you want to learn VBA.

Take care