Updating Column

[scott]

If Column F I have numbers with a decimal like 99.64 that I need to multiply
by 1000 all the way down. I tried below code to go down E2 until last cell
and multiply the value of the cell in column F (1 cell to right of E column
I'm testing if data exists). I'm getting an error, can someone help me out?
What am I missing?


Sub ConvertDecimals()

Dim c As Range

'set a range variable equal to the first data cell in column E
Set c = ActiveSheet.Range("E2")

'loop until a blank is encountered
Do While c <> ""
c.Offset(0, 1).Value = (c.Offset(0, 1).Value) * 1000
'set c to the next cell down

Set c = c.Offset(1, 0)
Loop
End Sub

 

[Guest]

Your code seems to work unless it runs into a value in F that is not able to
be converted to a number; that is, it handles the number 99.64 and the text
"99.64" but doesn't handle test "OOPS." Are you sure you don't have anything
but numbers in column F?

 

[scott]

yes, just numbers. i got an error saying sub not defined. is there a better
way to write such a procedure or more efficient route to achieve same goal?
Would you have done it the same way?

 

[Guest]

Sub not defined means it can't find the sub. Where did you put your code and
how are you activating it - are you trying to run it from a command button,
or calling it from another sub, or what? That might be the only problem. If
not doing this, try Insert Module from VBA editor and put it in the module as
a Public Sub:

Public Sub ConvertDecimals()
....
End Sub

Public should allow it to be "visible" to any other code. By default it
will only run with other code in the same module.

The methodology behind your code is fine, though. Just out of preference I
usually use the CurrentRegion property when I can when I have a retangular
range of contiguous cells and want to iterate through it, like this:

Dim MyRange as Range, TestCell as Range
Set MyRange = Intersect(Range("E:E"),Range("E1").CurrentRegion)
' Assuming we are in a rectangular list, this finds all filled cells in
column E)
For Each TestCell in MyRange.Cells ' loops through the cells
If TestCell.Value <> "" Then TestCell.Offset(0,1).value =
TestCell.Offset(0,1).value * 1000
Next TestCell

The only reason is that this makes the task of finding the needed cells and
iterating through them easier to do (no trying to make sure my index value is
correct) and easier to read and debug. But conceptually it is no different.
So you are doing fine!

HTH
K Dales