Loop through columns 2

[larrykathy]

Using Excel VBA
First I'm finding the number of columns on a sheet with data....
"TheLastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column" This gives me say 28...

Second, I use this line.......
"FirstCol = Chr$(ColumnCounter + 64)" ...It starts at "A"

Everything is A-OK until I get to column "AA" and then it gives me a error 1004 define object. The character "FirstCol" returns is "[".
The program bombs after that.
Any ideas?
Thanks
Larry

 

[Zathras]

Not sure why you want the column letter ID since it is easier to work with the column number (or even easier as a range), but this demonstrates one way to get it:
[blue]

Sub test()
Dim r As Range
Dim sAddress As String
Dim nDollarSign As Integer
Dim sCol As String
  Set r = Range("AA1")
  sAddress = r.Address(1, 0)
  nDollarSign = InStr(sAddress, "$")
  MsgBox Left(sAddress, nDollarSign - 1)
End Sub

[/color]

 

[larrykathy]

Thanks Zathras for your reply

It would be easier to work with column numbers? I couldn't figure that one out but if you look at the above line "TheLastCol" it gives me the number of the last column. If I could just loop through the columns by using column numbers it sure would seem slick to me. Any idea where I can look for samples?
Thanks again
Larry

 

[VBAjedi]

Larry,

You can refer to the columns on a sheet by their number as follows:

Worksheets("MySheetName&quot.Columns(x)

where x is a variable containing the column number you want. This will return a range object that references the specified column. For example, you could loop through the columns on Sheet1 and set the column width to 12 using:

TheLastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For x = 1 To TheLastCol
   Worksheets("Sheet1").Columns(x).ColumnWidth = 12
Next x

If you want to address several adjacent columns at once, you can use:

FirstCol = 3
LastCol = 5
Worksheets("Sheet1").Columns(FirstCol & ":" & LastCol).Somerangeproperty

You can also use the Columns property with a named range. In that case, column 1 is the first column in the named range, even if that isn't column "A". The syntax would look like:

Worksheets("Sheet1").Range("MyNamedRange").Columns(1).SomeRangeProperty

Hope that helps!



VBAjedi

 

[Zathras]

Looks like VBAJedi wants you to jump in at the deep end. [LOL]

Here is somthing for you to dip your toe into:
[blue]

Sub DemoColumnNumbers()
Dim c As Range
  For Each c In ActiveSheet.UsedRange.Columns
    MsgBox c.Address
  Next c
End Sub

[/color]
Each loop sets "c" to a range consisting of the next column (starting with the first). Once you have a range, you have many, many properties and methods you can use (just as if you have selected it, only without the need to actually select it.)

 

[VBAjedi]

Zathras,

"Sink or swim" is the way I was taught as well. . . aside from the occasional wave of panic, it's a fast way to learn. Besides, there's almost always someone here at Tek-Tips willing to throw you a lifeline if you get into serious trouble.

Larry,

Don't hesitate to ask for clarification on anything I didn't explain well!

VBAjedi

 

[larrykathy]

Thank you all very much.... There is a wealth of information here!

Thanks again
Larry