use a cell value as a range in vba

[bpoese]

This may be a silly question, but I'm stumped and can't find an answer while searching.
Maybe I'm going about it the wrong way, so feel free to propose a different solution.

I have this formula cell u22

=VLOOKUP(T22,LineErrors,2,FALSE)


I am now trying to reference the result of that formula as a range in vba in this snippet.

        If u22 <> "okay" Then
            strPart = MsgBox(msg1 & [parterror].Value & msg2, vbExclamation, "Required Information Missing")
            [u22.value].select
            Exit Sub
        End If

Any help you can give is greatly appreciated... =)

 

[AlphaFrog]

Try something like this...

Range([U22].Value).Select

 

[johnmeyer]

I have this formula cell u22

=VLOOKUP(T22,LineErrors,2,FALSE)

I am now trying to reference the result of that formula as a range in vba in this snippet.

If u22 <> "okay" Then

You cannot simply use a cell name in VBA because VBA needs to know which workbook, which worksheet, etc. Instead, you need to reference cells in VBA code by specifying the complete "path" to the cell. Here is one set of code that should work:

Sub Test()
With Workbooks(ThisWorkbook.Name).Worksheets("Template")
        If .Range("B1").Value = "okay" Then
              MsgBox ("hello")
        Else
              MsgBox ("goodby")
        End If
    End With
End Sub

Substitute the name of the worksheet that contains the code for the worksheet name "Template" in the above code

 

[bpoese]

I knew that didn't work but I was substituting actual cell values instead of the range names I was using for clarity.

Either way, alphafrog's basic idea worked... here's my code now as it properly functions the way I wanted as copied directly from vba.

        If [parterror] <> "okay" Then
            Dim errorlocation As Range
            Set errorlocation = Range(Range("errorlocation").Value)
            strPart = MsgBox(msg1 & [parterror] & msg2, vbExclamation, "Required Information Missing")
            errorlocation.Select
            Exit Sub
        End If