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,
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.
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,
Code:
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