loop through a column

[richey]

Hi

I have the following code which first of all gets the column number, e.g. 5 and then I want to say loop through column 5 and affect numeric cells in column 5 only?

any ideas much appreciated
Richey

Dim Bits As Double
Dim col As Integer

'Sheets("May-July").Select
col = ActiveCell.Column
MsgBox col
For Each cell_in_loop In ?????
'For Each cell_in_loop In Range("E8:E100")
If Not IsNull(cell_in_loop.Value) And IsNumeric(cell_in_loop.Value) And Not IsEmpty(cell_in_loop.Value) And cell_in_loop.Value <> "" Then
With cell_in_loop
Bits = cell_in_loop.Value
Bits = Bits / 1024
cell_in_loop.Value = Bits
End With
Else
End If
Next

[jrnyman]

Not really sure what you want, but try this

Dim Bits As Double
Dim col As Integer
Dim Cl as Range

Sheets("May-July").Select
'Choose the appropriate cell here
col = ActiveCell.Column
Cells(1,col).Select
Do While Not IsEmpty(ActiveCell)
Bits = ActiveCell.Value
Bits = Bits / 1024
ActiveCell.Value = Bits
Loop

[richey]

cells(1,col) will only select range("E5") if I'm in column 5.
Thanks anyway - I thought about saying
Range(" & col & 1":" & col & 100")
i.e. go from range(e1:e100)
I just need to say
For Each cell_in_loop In Range("E8:E100")
but I don't know which column I'm in i.e. don't know if its E or G or S

anybody else know?

[jrnyman]

Actually, though it will start at the top of that column, it will run the loop until it hits an empty cell. Thus, it will do what you need on every cell in that column as long as there are no gaps.

[jrnyman]

oops, before the Loop line insert:
ActiveCell.Offset(1,0).Select

sorry

hth

[richey]

gaps!! thats why it didn't work - excellent - thanks - that works now