PDA

View Full Version : VBA Cell value mutiplication



Adonaioc
06-10-2008, 12:25 PM
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
06-10-2008, 12:34 PM
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.

xld
06-10-2008, 12:54 PM
cell.Value = Evaluate("Convert(" & cell.Address & ",""m"",""in"")/1000")

Adonaioc
06-11-2008, 10:35 AM
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.

xld
06-11-2008, 10:45 AM
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