Using a variable to reference a public constant

[nhandal]

Hi,

This is supposed to be a simple problem, but surprisingly I could not get it to work or find a relevant post about the same kind of problem.

I have 13 parameters called PEMAXX as listed below, the sub should use these parameters to create column tittles by calling the constant the first time and using the string stored in it then adding the word Trend then Adding the word Signal. The sub should create column tittles as follows:
PEMA3, PEMA3Trend, PEMA3Signal, PEMA5, PEMA5Trend, PEMA5Signal, and so on until PEMA13.

However the sub is not using the value in the constant, instead it is labeling the first 3 columns as Param1, then 3 columns as Param2, then 3 columns as Param3, etc.

I appreciate it if anyone can help, and thank you in advance.

Public Const Param1 As String = "PEMA3"        
Public Const Param2 As String = "PEMA5"
Public Const Param3 As String = "PEMA7"
Public Const Param4 As String = "PEMA10"
Public Const Param5 As String = "PEMA12"
Public Const Param6 As String = "PEMA15"
Public Const Param7 As String = "PEMA17"
Public Const Param8 As String = "PEMA20"
Public Const Param9 As String = "PEMA25"
Public Const Param10 As String = "PEMA35"
Public Const Param11 As String = "PEMA50"
Public Const Param12 As String = "PEMA100"
Public Const Param13 As String = "PEMA200"
 
Dim ColumnLabel As String
 
 
Sub FillColumLabels()
 
    Dim i As Integer
    Range(“A1”).Select
        
    For i = 1 To 13
        ActiveCell.Offset(0, 1).Select
        ColumnLabel = "Param" & i
        ActiveCell.Value = ColumnLabel
        ActiveCell.Offset(0, 1).Select
        ColumLabel = "Param" & i & "Trend"
        ActiveCell.Value = ColumnLabel
        ActiveCell.Offset(0, 1).Select
        ColumLabel = "Param" & i & "Signal"
        ActiveCell.Value = ColumnLabel
       
    Next i
 
End Sub

 

[VoG]

Try like this

Sub FillColumLabels()
Dim Param
Dim ColumnLabel As String
Param = Array("PEMA3", "PEMA5", "PEMA7", "PEMA10", "PEMA12", "PEMA15", "PEMA17", "PEMA20", "PEMA25", "PEMA35", "PEMA50", "PEMA100", "PEMA200")

Dim i As Integer
Range("A1").Select
    
For i = LBound(Param) To UBound(Param)
    ActiveCell.Offset(0, 1).Select
    ColumnLabel = Param(i)

 

[MickG]

Perhaps this might do !!

[COLOR="Navy"]Sub[/COLOR] MG02Sep28
[COLOR="Navy"]Dim[/COLOR] Hdr
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
c = 0
Hdr = Array("PEMA3", "PEMA5", "PEMA7", "PEMA10", "PEMA12", "PEMA15", "PEMA17", "PEMA20", "PEMA25", "PEMA35", "PEMA50", "PEMA100", "PEMA200")
[COLOR="Navy"]For[/COLOR] Ac = 0 To UBound(Hdr)
    Range("a1").Offset(, c) = Hdr(Ac)
        c = c + 1
    Range("a1").Offset(, c) = Hdr(Ac) & "Tend"
        c = c + 1
    Range("a1").Offset(, c) = Hdr(Ac) & "Signal"
        c = c + 1
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Rick Rothstein]

Here is my attempt at a macro for you...

Sub FillColumnLabels()
    Dim Col As Long, V As Variant
    Col = 2
    For Each V In Array(3, 5, 7, 10, 12, 15, 17, 20, 25, 35, 50, 100, 200)
        Cells(1, Col).Resize(, 3).Value = Array("Param" & V, "Param" & V & "Trend", "Param" & V & "Signal")
        Col = Col + 3
    Next
End Sub

 

[nhandal]

Dear VoG, MickG, Rick Rothstein

Thank you very much. These are really very helpful. I solved my problem.

Just out of curiosity, is there any way I can use a variable to call a constant? especially by using a loop as I tried to do in my original post?

The turnaround that you all provided through using the Array solved the problem for this task but it might be helpful to know if this is possible for potential future use. Any idea?