Insert Decimal Places and Clear Zeros

[Guest]

I have users that import data into a large spreadsheet . The data is setup
without any decimal places. I also have a blank rows in between the data so
if I multiply the column by .01 the empty cells now have a zero (0). These
cells need to stay blank. Does anyone have any suggestions on how to
accomplish this task in one formula or code.

Example Cells G1 = 12345 and G2 = Blank and G3 = Blank and G4 = 45678

I would like data to be G1 = 123.45 G2 = Blank G3 = Blank and G4 = 456.78

Thanks for your help.

Gladys

 

[Guest]

Hi,

I have recorded a macro while doing the following actions:
- Copy the range "I1" (it has the value 0.01)
- Select the range "G1:G3", where the example data was
- Menu Edit -> Go to -> Special -> Constants to select non blank cells
- Menu Edit -> Paste special -> Multiply.

This is the macro recorded:
Sub Macro1()
Range("I1").Select
Selection.Copy
Range("G1:G3").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
End Sub

You can change the Ranges to match your case in the macro.

Hope this helps,
Miguel.

 

[Guest]

Thanks Miguel,

After I wrote the original question a light turned on.

I wrote a simple if statement =If(G1>0,G1*.01," ")

Duh.....

Thanks for your help!