VBA Cell value mutiplication

[Adonaioc]

The code i have works but make the sheet messy, My goal is to convert all values in a column (except the header row) from a millimeter value to an inch value by multipling by .0393700787

[VBA]
Sub increase()
For Each Cell In Range("J2:K100")

Cell.Value = Cell.Value * 0.0393700787

Next Cell

End Sub
[/VBA]

I want it to skip blank cells and the the first row and leave 0's as blanks.

Hope that makes sence,
Thanks

[mikerickson]

[VBA]For Each Cell In Range("J2:K100").SpecialCells(xlCellTypeConstants) [/VBA]
will skip the formulas and blanks and convert only the constant entries in your range. It will a) replace 0 with 0 and b) error if cell.value is a string.

To deal with b) you could either test for IsNumeric(Cell.Value) before multiplying (which witll slow the routine v. slightly)
or use [VBA]Cell.Value = Val(Cell.Value) * 0.0393700787 [/VBA], which will replace any non-numeric text values with 0.

[Bob Phillips]

[vba]

cell.Value = Evaluate("Convert(" & cell.Address & ",""m"",""in"")/1000")
[/vba]

[Adonaioc]

I inserted the code from xld into my sheet but i may not have done it right,

[VBA]
Sub increase()
For Each Cell In Range("J2:J10000,K2:10000")

Cell.Value = Evaluate("Convert(" & Cell.Address & ",""m"",""in"")/1000")

Next Cell

End Sub
[/VBA]

Its not working.

[Bob Phillips]

Do you really want to go that far down the rows?

[vba]

Sub increase()
Dim Cell As Range

For Each Cell In Range("J2:K10000").SpecialCells(xlCellTypeConstants)

Cell.Value = Evaluate("Convert(" & Cell.Address & ",""m"",""in"")/1000")

Next Cell

End Sub
[/vba]