loop through a column

richey

New Member
Joined
Feb 17, 2002
Messages
7
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top