Search code examples
exceloffsetvba

Referencing a cell when using offset in VBA

I don't exactly understand how to reference the cell in a table based on a condition and get the information from a cell one column over. Basically, I have a table from which the user can select a variable and in the column next to it, they input the number of instances of the variable. I am trying to use offset but I think I need to reference the first cell with the variable.

For Each cell In [Table]
    If cell = "Variable1" Then
        Var1 = .Offset(0, 1).Value
    ElseIf cell = "Variable2" Then
        Var2 = .Offset(0, 1).Value
    ElseIf cell = "Variable3" Then
        Var3 = .Offset(0, 1).Value
    ElseIf cell = "Variable4" Then
        Var4 = .Offset(0, 1).Value
    End If
Next cell

Basically, the code will look at each cell in [Table] and if the cell equals "Variable1" the Var1 will = the value in the column next it, if equal to "Varible2" then Var2 will = the value in the column next to it... and so on. I'm not referencing the cell that contains "Variable1" appropriately. How do I do that? I thought it would no where to offset from since it has to look at "cell" but apparently I'm wrong.

Solution

  • I'm sorry I figured out the answer. Simply adding ActiveCell allows the code to function. Please intelligent community.. Answer actual problems:)