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.
Bob Phillips
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.
Bob Phillips
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.