Looping in columns

YCJ

New Member
Hi there

I'm currently working on a loop to move from one column to another. Example:

For a = 1 To UBound(file)
Cells(2, 3 + a - 1).Select
Next a
(This is incomplete, I know this loop makes no sense. )

I'm well aware that this is just moving from one cell to another from left to right.
What I'm trying to achieve is something like, Cells(2, 3 + a - 1 : 100, 3 + a - 1), but i know this is incorrect and invalid. Btw, I used 3 + a - 1 because my work starts from the 3rd column, putting it this way makes it easier for myself and others to see what am I trying to do with the columns.

I know this would be easy if I'm working it in rows, since i can put it like Range("A" & 2 + a), but in this case, changing columns, i can't add the 'a' to the 'A', since A + 1 doesn't equal to B in this case. Any ideas? Any help would be very much appreciated.

CJ

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

pedie

Well-known Member
Trying to help, do you mean something like this?
Code:
``````[/FONT]
[FONT=Courier New]Sub LoopinColumns_TilllastColumn()
Dim lc As Long
Dim i As Long
lc = Cells(2, Columns.Count).End(xlToLeft).Column
For i = 1 To lc
MsgBox i
Next i
End Sub``````

YCJ

New Member

But, what I'm trying to do is like selecting from A2 to A100, then copy it, then pasting it somewhere else, and then move on to B2 to B100. I have done the part involving copying and pasting, but I'm kinda stucked in the switching from A2 to A100 --> B2 to B100 --> C2 to C100...... part. As you can see in my first post,

For a = 1 To UBound(file)
Cells(2, 3 + a - 1).Select
Next a

I will loop depending on my UBound, since the 'a' variable is involve in other procedures in this very same loop, I would like to implement it in this action as well, because the amount of columns I have to go is the same as my UBound value.

Hope you understand what I'm saying, i know it's kinda puzzling. @.@

Thank you for your precious time,
CJ

YCJ

New Member
Hi there

Thank you all for your time, I've solved the problem. Sorry if I have wasted any of your time, trying to help me out.

Thanks,
CJ

YCJ

New Member
Okay then,

For a = 1 To UBound(file)Range(Cells(2, 3 + a - 1), Cells(100, 3 + a - 1)).Select
Selection.Copy
Next

Of course, I still have coding after the copy part, but it is too lengthy, so that is pretty much how I move from one column to another.

CJ

YCJ

New Member
For a = 1 To UBound(file)
Range(Cells(2, 3 + a - 1), Cells(100, 3 + a - 1)).Select
Selection.Copy
Next

Thanks you!

YCJ

New Member
Lol no, Thank YOU...appreciate your helping hand..

YCJ

New Member
Hi guys

Now I have another question, refering above, I used
Code:
``Range(Cells(2, 3 + a - 1), Cells(100, 3 + a - 1)).Select``
to select a particular column, what if I need to select another column at the same time? What can i do with it?

CJ

Replies
3
Views
157
Replies
10
Views
331
Replies
9
Views
166
Replies
8
Views
220
Replies
1
Views
490

1,191,211
Messages
5,985,298
Members
439,956
Latest member
FrazzledCat

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.

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

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