Code to work on cell values

Mark Green

Board Regular
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!

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

joeyjoejoejrshabidu

New Member
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

Active Member
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:
Code:
=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

Board Regular
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:
Code:
=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:

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

BQardi

Active Member
This should get you a bit further:
Code:
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

Board Regular
This should get you a bit further:
Code:
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.

Code:
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

Active Member
You could also just update the values in Col G directly without using Col Z:
Code:
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

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

You're welcome

Replies
1
Views
208
Replies
6
Views
127
Replies
2
Views
399
Replies
8
Views
502
Replies
0
Views
281

1,191,195
Messages
5,985,225
Members
439,950
Latest member
Xearo96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back