Help! Use array string value to set variable

[Legacy 218534]

I have been googling for a couple of hours now, and would like some help. I am looking to shorten this code (I have 95 variables):

For j = 1 To icolumncount[INDENT]Select Case Cells(header_row, j)[/INDENT]
[INDENT=2]Case NONTEMP(1): ModeNumber = j[/INDENT]
[INDENT=2]Case NONTEMP(2): TestNumber = j[/INDENT]
[INDENT=2]Case NONTEMP(3): DataNumber = j[/INDENT]
[INDENT=2]Case NONTEMP(4): LoopNumber = j[/INDENT]
[INDENT=2]Case NONTEMP(5): StageNumber = j[/INDENT]
[INDENT=2]Case NONTEMP(6): CycleNumber = j[/INDENT]
[INDENT=2]Case NONTEMP(7): speed = j[/INDENT]
[INDENT=2]Case NONTEMP(8): Torque = j[/INDENT]
[INDENT=2]Case NONTEMP(9): HP = j[/INDENT]
[INDENT=2]Case NONTEMP(10): throttle = j[/INDENT]
[INDENT=2]Case NONTEMP(11): p_baro = j[/INDENT]
[INDENT=2]Case NONTEMP(12): g_air = j[/INDENT]
[INDENT=2]Case NONTEMP(13): g_fuel = j[/INDENT]
[INDENT=2]Case NONTEMP(14): fuel_tot = j[/INDENT]
[INDENT=2]Case NONTEMP(15): humidity = j
'continues  until....
Case NONTEMP(95): FSN2 = j[/INDENT]
[INDENT]End Select[/INDENT]
Next j

NONTEMP(x) stores data column header names based on a separate sheet that is setup prior to running. This code opens a data file, then searches for these header names. When if finds a header name it stores the column location for later use. eg:

[LEFT][INDENT]For x = 1 to irowcount[/INDENT]

[INDENT=2]'calculate fuel flow: g_exhaust = g_air + g_fuel[/INDENT]

[INDENT=2]Cells(x, g_exhaust) = Cells(x, g_air)+Cells(x, g_fuel)[/INDENT]

[INDENT]Next x[/INDENT]
[/LEFT]

I realize it would be extremely easy to just make NONTEMP(1 to 95, 1 to 2) and store the header names and location values all in the single array.

    For j = 1 To icolumncount[INDENT]For x = 1 to 95[/INDENT]
            Select Case Cells(header_row, j)
                Case NONTEMP(x,1): NONTEMP(x,2) = j
            End Select[INDENT]Next x[/INDENT]
    Next j

BUT calculations would look like:

For x = 1 to irowcount[INDENT]'calculate fuel flow: g_exhaust = g_air + g_fuel
Cells(x, g_exh) = Cells(x, NONTEMP(13, 2)) + Cells(x, NONTEMP(12, 2))[/INDENT]

However, this makes the code very hard to debug for anyone in my office who isn't me. I am doing all of this inside a Sub in a module. I have tried using CallByName, but none of my code is in a Class module, and I don't really understand what I'm doing with the function.

The header names stored in NONTEMP(x) are the same as the variable names I would like to be using. It would be nice if I could do something like this:

    For j = 1 To icolumncount[INDENT]For x = 1 to 95[/INDENT]
            Select Case Cells(header_row, j)
                Case NONTEMP(x): Set Variable(NONTEMP(x)) = j
            End Select[INDENT]Next x[/INDENT]
    Next j

Where NONTEMP(x) is a string value containing the column header to search for, and the variable name that is used later in the equations. Please help!

Thanks,
Tim

 

[Scott Huish]

Why not use VBA find and store the column numbers in an array where it finds them?

Sub test()
Dim srchValues, c As Range, x As Long, NonTemp(0 To 4) As Long
srcvalues = Array("ABC", "DEF", "GHI", "JKL")
For x = LBound(srcvalues) To UBound(srcvalues)
    Set c = Rows(1).Find(srcvalues(x), lookat:=xlWhole)
    If Not c Is Nothing Then NonTemp(x) = c.Column
    MsgBox NonTemp(x)
Next
End Sub

 

[Legacy 218534]

Thank you for the response! This is another possibility. But again, the end goal is to have the column numbers linked to the variable names somehow. That way, when I am performing calculations later on it is easy to see what is being calculated without having to track down through the array.

Having a final equation g_air + g_fuel = g_exhaust is preferred to Nontemp(24) + Nontemp(47) = g_exhaust

So I am really looking for a way to iteratively declare and set variables using the names stored in the array.

 

[Legacy 218534]

I should clarify. Using the find function is an excellent suggestion for the locating of column numbers. However, my goal is to store the values it finds in separate variables. So this will help, but I still need a way to iterate through and set the variables to the values it finds.

 

[Norie]

You can't loop through individual variables.

 

[Legacy 218534]

I've figured it out!!!! After more searching I finally figured out how to use CallByName.

Essentially the variables that I would like to iterate through, but keep using later on, are declared in a separate class module (class_CalcVar).

Then my code boils down to:

        header_row = 1        Dim obj As class_CalcVar
        Set obj = New class_CalcVar


            For j = 1 To 95
                Set col_loc = Rows(header_row).Find(NONTEMP(j), lookat:=xlWhole)
                If Not col_loc Is Nothing Then: Call CallByName(obj, NONTEMP(j), VbLet, col_loc.Column)
            Next j
        
        Set obj = Nothing

This code will:
1. Iterate through each of the names stored in NONTEMP
2. Search for the header in the data file that corresponds to that name
3. Return the column number (col_loc) for that header in the data file
4. If there is a column number, changes the variable's value (whose name is stored in NONTEMP) to that column number

Thank you for HOTPEPPER for the .Find tip. And thank you Norie for telling me I can't do this (it made me search harder for an answer).

 

[Norie]

You seem to be referring to a class in that code.

Is there more code?

 

[Legacy 218534]

The class that I refer to just has the variables that I would have declared otherwise in the main module.

    Private p_tto As Long                           'turbo out pressure column location
    Private speed As Long                           'engine speed
    Private Int_Val_Ref As Long                     'intake valve position
    Private f_air_kghr As Long                      'inlet airflow (kg/hr)
    Private HP As Long                              'Raw horsepower data column location
    Private HP_nonnegative As Long                  'Horsepower (will be stored with negative values set to 0)
    Private gwetexh As Long                         'wet exhaust flow
    Private Gfuel As Long                           'engine fuel
    Private BSFC As Long                            'brake specific engine fuel
    Private Torque As Long                          'Engine torque
    Private EO_temp As Long                         'Engine outlet temperature
    Private F_Inj_gpm As Long                       'Injection flow in gpm (urea/fuel)
    ........
    ........
    ........

This helps organize my variables a little, allows me to iterate through them when I want to set their value, and then use them individually to keep equations easily readable.

 

[Norie]

Thought that might be what you had.

I was going to suggest using a user defined type but wasn't sure how or if you could have iterated through that.