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]

These are just arrays. They only store values. They are not range or actual cell referances. You can't format them.

However, you can define rule for skipping. What would be the rule?
If I were you I would go for conditional formatting for the color. What will be the coloring rule?

Well the cells I want to skip are linked cells, they get their value from a different sheet. I mark them manually with a colour.
These cells contain:

=Sheet2!I11

(for example)

 

[Legacy 143009]

Are there other cells with formula or are they pure values?

 

[Supernerd81]

Are there other cells with formula or are they pure values?

No only those. I think if you can make a rule that skips cells with red text or cells with red filling that would be great!

 

[Legacy 143009]

As I told you, it is not working with cell references. I can't test it right now but it should work.

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)
    ElseIf Not InStr(oldPrices(i), "=Sheet2!I11") > 0 Then
       newPrices(i, 1) = oldPrices(i)
    End If
  Next
  End With
 
  Range("B2").Resize(UBound(newPrices)) = newPrices
End Sub

 

[Supernerd81]

These are just arrays. They only store values. They are not range or actual cell referances. You can't format them.

However, you can define rule for skipping. What would be the rule?
If I were you I would go for conditional formatting for the color. What will be the coloring rule?

Ah, now I understand what you are saying.

No there are more formulas than the one in my example. My conditioning formatting rule would be: highlight cell rules --> use a formula to determine which cells to format --> Formula:

=ISFORMULA(C2)

(wow that actually saves me from doing that manually!)

Can you use that rule to skip and copy the old values instead?
Your last macro makes everything zero again!

 

[Legacy 143009]

If I were you I would only check if value contains "=" sign.
So what the code does is, check if it is a number, then multiply and round up.
Then check if it has "=" sign (if it is a formula). If it is not, then copy the text (like "Price on request")
If it is not numeric, if it contains "=" then, skip. Skip to next value.

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)
    ElseIf Not InStr(oldPrices(i), "=") > 0 Then
       newPrices(i, 1) = oldPrices(i)
    End If
  Next
  End With
 
  Range("B2").Resize(UBound(newPrices)) = newPrices
End Sub

 

[Legacy 143009]

If I were you I would check if value contains only "=" sign.
So what the code does is, check if it is a number, then multiply and round up.
Else if, it has "=" sign (if it is a formula). If it is Not, then copy the text (like "Price on request")
If it is not numeric, if it contains "=" then, end if. Do nothing. Skip to next value.

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)
    ElseIf Not InStr(oldPrices(i), "=") > 0 Then
       newPrices(i, 1) = oldPrices(i)
    End If
  Next
  End With
 
  Range("B2").Resize(UBound(newPrices)) = newPrices
End Sub

 

[Supernerd81]

If I were you I would check if value contains only "=" sign.
So what the code does is, check if it is a number, then multiply and round up.
Else if, it has "=" sign (if it is a formula). If it is Not, then copy the text (like "Price on request")
If it is not numeric, if it contains "=" then, end if. Do nothing. Skip to next value.

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)
    ElseIf Not InStr(oldPrices(i), "=") > 0 Then
       newPrices(i, 1) = oldPrices(i)
    End If
  Next
  End With
 
  Range("B2").Resize(UBound(newPrices)) = newPrices
End Sub

It doesn't work. I can get it to copy and multiply the values. I can get it to copy the cells with the formula. But not both at the same time.

 

[Legacy 143009]

Ok what are your conditions, please state them one by one again from the beginning.

 

[Supernerd81]

Ok what are your conditions, please state them one by one again from the beginning.

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.