Code to work on cell values

[Mark Green]

When I import data into cells in Col G I would like to do the following to it:

1. Format the cells to 2 decimal places
2. Multiply each cell by 100
3. If the resulting cell value is less than 5, replace it with a blank cell

I can do 1. but not sure how to achieve 2. and 3.

Any help would be appreciated!

 

[joeyjoejoejrshabidu]

Not sure how you're "importing" data, but if the data is in column A then you could use a formula.

=if(A1*100<5,"",A1*100)

Formatting can be done separately by highlighting the cells you want to format and right click, then format cells etc.

 

[BQardi]

You could use a formula in a free column (temporarily, f.ex. Col Z) to generate the desired outcome and then copy those values to Col G:
Insert in Z1 and drag down:

=IF(ROUND(G1*100,2)<5,"",ROUND(G1*100,2))

Then copy Col Z to Col G as values...

We could do an automated version of this with macros.
Let me know if you want this instead...

 

[Mark Green]

You could use a formula in a free column (temporarily, f.ex. Col Z) to generate the desired outcome and then copy those values to Col G:
Insert in Z1 and drag down:

=IF(ROUND(G1*100,2)<5,"",ROUND(G1*100,2))

Then copy Col Z to Col G as values...

We could do an automated version of this with macros.
Let me know if you want this instead...

Thanks BQardi, the formula works out very nicely.
Yes I would like to automate this in VB.

I tried the following to start but am getting object error, not sure why:

Sheets("SQL Export").Range("Z1").Formula = "=IF(ROUND(G1*100,2)<5,"",ROUND(G1*100,2))"

 

[BQardi]

This should get you a bit further:

Dim LastRow As Long
With Sheets("SQL Export")
    LastRow = .Cells(.Rows.Count, 7).End(xlUp).Row
    .Range("Z1").Formula = "=IF(ROUND(G1*100,2)<5,"""",ROUND(G1*100,2))"
    .Range("Z1:Z" & LastRow).FillDown
End With

 

[Mark Green]

This should get you a bit further:

Dim LastRow As Long
With Sheets("SQL Export")
    LastRow = .Cells(.Rows.Count, 7).End(xlUp).Row
    .Range("Z1").Formula = "=IF(ROUND(G1*100,2)<5,"""",ROUND(G1*100,2))"
    .Range("Z1:Z" & LastRow).FillDown
End With

Thanks BQardi, yes it certainly does get me a bit further.

As I will always have differing amounts or rows to copy the formula to, I needed to adjust your code a bit.

As you will see I came up with my own quirky solution.
There are probably much easier ways to accomplish this.

After I do the copying of values from col Z to col G I will delete the formulas from col Z.

Dim LastRow As Long
With Sheets("SQL Export")
    .Range("Z1").Formula = "=IF(ROUND(G1*100,2)<5,"""",ROUND(G1*100,2))"
End With

 Range("A1").Select   
 ActiveCell.End(xlDown).Offset(0, 25).Select  
 Range(ActiveCell, ActiveCell.End(xlUp)).Select  
 Selection.FillDown

 

[BQardi]

You could also just update the values in Col G directly without using Col Z:

Dim cell As Range
For Each cell In Range("G1", Cells(Rows.Count, "G").End(xlUp))
    If cell.Value > 0.05 Then
        cell.Value = Round(cell.Value * 100, 2)
    Else
        cell.Value = ""
    End If
Next cell

 

[Mark Green]

Yes! That is a much more elegant way to solve my problem.
Thanks very much.

 

[BQardi]

You're welcome