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


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

Cell.Value = Cell.Value * 0.0393700787

Next Cell

End Sub


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

Hope that makes sence,
Thanks

[mikerickson]

For Each Cell In Range("J2:K100").SpecialCells(xlCellTypeConstants)
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 Cell.Value = Val(Cell.Value) * 0.0393700787 , which will replace any non-numeric text values with 0.

[Bob Phillips]

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

[Adonaioc]

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


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

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

Next Cell

End Sub


Its not working.

[Bob Phillips]

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



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