shorten Column ID code to use for multiple columns.

[kds14589]

I have a workbook that i made to experiment with to help my coding.
The following code I've been using for some time to give me the column (columns C thru V) number and letter to use in my code. An example follows,

Dim WS As Worksheet
        Set WS = ShGE03  'this worksheet

With WS
.Range("C3:Q3,S3:T3,V3").Clear
    .Range("C3").Value = "COLUMN " & .Cells(3, "C").Column & " (" & Split(.Cells(.Cells(3, "C").Column).Address, "$")(1) & ")"
    .Range("D3").Value = "COLUMN " & .Cells(3, "D").Column & " (" & Split(.Cells(.Cells(3, "D").Column).Address, "$")(1) & ")"
    .Range("E3").Value = "COLUMN " & .Cells(3, "E").Column & " (" & Split(.Cells(.Cells(3, "E").Column).Address, "$")(1) & ")"
    .Range("F3").Value = "COLUMN " & .Cells(3, "F").Column & " (" & Split(.Cells(.Cells(3, "F").Column).Address, "$")(1) & ")"
    .Range("G3").Value = "COLUMN " & .Cells(3, "G").Column & " (" & Split(.Cells(.Cells(3, "G").Column).Address, "$")(1) & ")"
    .Range("H3").Value = "COLUMN " & .Cells(3, "H").Column & " (" & Split(.Cells(.Cells(3, "H").Column).Address, "$")(1) & ")"
    .Range("I3").Value = "COLUMN " & .Cells(3, "I").Column & " (" & Split(.Cells(.Cells(3, "I").Column).Address, "$")(1) & ")"
    .Range("J3").Value = "COLUMN " & .Cells(3, "J").Column & " (" & Split(.Cells(.Cells(3, "J").Column).Address, "$")(1) & ")"
    .Range("K3").Value = "COLUMN " & .Cells(3, "K").Column & " (" & Split(.Cells(.Cells(3, "K").Column).Address, "$")(1) & ")"
    .Range("L3").Value = "COLUMN " & .Cells(3, "L").Column & " (" & Split(.Cells(.Cells(3, "L").Column).Address, "$")(1) & ")"
    .Range("M3").Value = "COLUMN " & .Cells(3, "M").Column & " (" & Split(.Cells(.Cells(3, "M").Column).Address, "$")(1) & ")"
    .Range("N3").Value = "COLUMN " & .Cells(3, "N").Column & " (" & Split(.Cells(.Cells(3, "N").Column).Address, "$")(1) & ")"
    .Range("O3").Value = "COLUMN " & .Cells(3, "O").Column & " (" & Split(.Cells(.Cells(3, "O").Column).Address, "$")(1) & ")"
    .Range("P3").Value = "COLUMN " & .Cells(3, "P").Column & " (" & Split(.Cells(.Cells(3, "P").Column).Address, "$")(1) & ")"
    .Range("Q3").Value = "COLUMN " & .Cells(3, "Q").Column & " (" & Split(.Cells(.Cells(3, "R").Column).Address, "$")(1) & ")"
    ' Blank range("R3")
    .Range("S3").Value = "COLUMN " & .Cells(3, "S").Column & " (" & Split(.Cells(.Cells(3, "S").Column).Address, "$")(1) & ")"
    .Range("T3").Value = "COLUMN " & .Cells(3, "T").Column & " (" & Split(.Cells(.Cells(3, "T").Column).Address, "$")(1) & ")"
    ' Blank range("U3")
    .Range("V3").Value = "COLUMN " & .Cells(3, "V").Column & " (" & Split(.Cells(.Cells(3, "V").Column).Address, "$")(1) & ")"
 End With

As you can see, I had to write the code for each column. But now I'm wondering if there is a shorten code that will give the same result (see attached example) I can use over multiple column's.

 

[Marc L]

As explained in Cells VBA help just use a numeric index rather than a letter.​

​

Or maybe all can be replaced with a single codeline using an Excel formula …​

​

 

[p45cal]

Set WS = ShGE03  'this worksheet
For Each cll In WS.Range("C3:Q3,S3:T3,V3").Cells
  cll.Clear                                      'may not need this line.
  cll.Value = "COLUMN " & cll.Column & " (" & Split(cll.Address, "$")(1) & ")"
Next cll
End Sub

and horribly:

Set WS = ShGE03  'this worksheet
For Each area In WS.Range("C3:Q3,S3:T3,V3").Areas
  With area
    .Clear 'may not need this line.
    .Formula2R1C1 = "=""COLUMN "" & COLUMN() & "" ("" & INDEX(TEXTSPLIT(CELL(""address"",INDIRECT(""R1C"" & COLUMN(),FALSE)),""$""),2) & "")"""
    .Calculate
    .Value = .Value
  End With
Next area

Just one question:
Is this intentional:?

 

[kds14589]

Set WS = ShGE03  'this worksheet
For Each cll In WS.Range("C3:Q3,S3:T3,V3").Cells
  cll.Clear                                      'may not need this line.
  cll.Value = "COLUMN " & cll.Column & " (" & Split(cll.Address, "$")(1) & ")"
Next cll
End Sub

and horribly:

Set WS = ShGE03  'this worksheet
For Each area In WS.Range("C3:Q3,S3:T3,V3").Areas
  With area
    .Clear 'may not need this line.
    .Formula2R1C1 = "=""COLUMN "" & COLUMN() & "" ("" & INDEX(TEXTSPLIT(CELL(""address"",INDIRECT(""R1C"" & COLUMN(),FALSE)),""$""),2) & "")"""
    .Calculate
    .Value = .Value
  End With
Next area

Just one question:
Is this intentional:?
View attachment 86556

Your first code works
THANKS
and also, thanks about the Q & R errors, I never noticed, the R should have been a Q. I never saw the error on my worksheet (Shame on me)