Is this possible in theory? - Workaround needed working with variables

[sramsay]

I posted a few days ago looking for a solution to this but didn't get much back, however, it was a rather complex way of putting it, so I am hoping by recreating the issue in a simple scenario, I may just get some help

Sub varTest()


Dim i As Integer
Dim theSum As Integer


    i = 1
    X = Left("integer", 1) 'returns the letter 'i', the 1st variable's name
    
    Debug.Print i 'at this point, this returns the number 1
    Debug.Print X 'at this point, this returns the letter i
    
    theSum = X + i 'Essentially, I would want this to be 1+1
    
    Debug.Print theSum
    Debug.Print "The answer I need to get is 2, but it errors"


End Sub

 

[sramsay]

so this is the concept...

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

 

[Kyle123]

You need to have your lookup table return the column index, so this:

Application.VLookup(Replace(c.Name, "tb_ten12", ""), transRange, 2, False)

Should return a Long. You could use a formula in the second column to dynamically update the column index if you wanted to make dynamic, but I'd probably use a collection