Dynamic range based on column order

[Haikal]

Hi
I want to refer to columns by column order (i.e. A=1, B=2, etc.)

I want to sum a range for dynamic columns and fixed widths (i.e. B2:B5 or F2:F5, etc).

i have C_N as variant and i am trying to write the formula but desperately getting syntax error.

Range("M5") = WorksheetFunction.Sum(Range(C_N& "2" : C_N&"5")))

i tried before to make another easier formula like

=Range("N5:N" & LastRow)

and it worked.

Kindly advise

 

[NeonRedSharpie]

You could use a for loop:

For x = 2 to 6 Step 4
     ' Your code goes here
     msgbox Excel.WorksheetFunction.Sum(Range(Cells(2, x),Cells(5, x)))
Next x

 

[Haikal]

This is not what i need, i need to refer to the column number as i am doing this in other context, the columns are read in a listbox, so i need to calculate a formula for the selected listbox item (i.e. selected Column).