Retrieve a variable's value based on another variable result

[sramsay]

Hi all, I am looking for a way to be able to use a variable name that is stored in a spreadsheet.

Basically, I store column numbers as variable integers. Column header names are looked up and the column number is stored. the columns appear on 3 sheets in different location so will have 3 assigned variables.

I am then looping through textbox controls on a userform. the data for the text box comes from cell values.

I have a translation table in the workbook which states the variable names that will be associated with each text box name. I am looking up the variable name based on the textbox name without issue. But then the result is being stored as a literal string. I can find no way to retrieve the variable's value from this lookup?

Is there an achievable way of doing this?

Sub test_load_f1()


Dim transRange As Range
Dim transLr As Long
Dim varName As Variant


transLr = Sheets("TranslationTable").Range("A1048576").End(xlUp).row
Set transRange = Sheets("TranslationTable").Range("A2:E" & transLr)


Dim c As MSForms.Control
     
    For Each c In Userform1.fra_ten12mnth.Controls
        If TypeOf c Is MSForms.TextBox Then
            [U]varName[/U] = Application.VLookup(Replace(c.Name, "tb_ten12", ""), transRange, 2, False)
            c.value = Sheets("Commited Data").Cells(Sheets("Commited Data").Range("A:A").Find(What:=vTenId & "12" & vTenSupplier & vTenMeterNumber, LookAt:=xlWhole).row, [U]varName[/U]).value
        ElseIf TypeOf c Is MSForms.CheckBox Then
            '...
        Else
            
        End If
    Next c
    
End Sub

as an example, varName would return "cComUniteRate1"
the variable cComUniteRate1 will return the number 10

When I refer to varName in the c.value (Setting the value of the textbox) I need varName to be 10.

Hope this makes sense.

Thanks

 

[dmt32]

Hi,
you should be able to coerce your variable using the Val Function. Val returns the number contained in your variable as a string to a numeric value of appropriate type.

c.Value = Sheets("Commited Data").Cells(Sheets("Commited Data").Range("A:A").Find(What:=vTenId & "12" & vTenSupplier & vTenMeterNumber, LookAt:=xlWhole).Row, Val(varName)).Value

Dave

 

[sramsay]

Hi, Thanks, but this won't work. The VBA is seeing varName literally as 'cComUnitRate1' and not the value that cComUnitRate1 holds