VBA to get cell address based on a variable

[normpam]

The following code will capture an invoice number on the Excel sheet, then find the number, and return in a MsgBox a value... works fine. Was wondering if there is a more 'direct' way of telling Excel to get to a certain cell as an offset, without using the Find feature.

x = InputBox("Enter number")
Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

MsgBox (Range(ActiveCell, ActiveCell).Offset(0, 3).Offset.End(xlDown).Value)

 

[GWteB]

The Cells.Find(....) statement is supposed to deliver you the invoice number you are talking about, like in:

    x = InputBox("Enter number")
    
    MsgBox Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
           :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
           False, SearchFormat:=False).Value
    
    '    MsgBox Range(ActiveCell, ActiveCell).Offset(0, 3).Offset.End(xlDown).Address

There's no need to use any offset, unless this is explicitly your goal.
Your current code provides a value from a cell on a certain row (the layout of your worksheet determines from which row) three columns to the right of where the found value is located.
Note that your current code will not run on any other computer due to Excel's (undocumented) xlFormulas2 constant.

Was wondering if there is a more 'direct' way of telling Excel to get to a certain cell as an offset, without using the Find feature.

I'm not sure what you mean by that. Perhaps you're able to elaborate.

 

[normpam]

was wondering if maybe once the Invoice number is captured as a variable it could be used to say something like:
Range(X).Offset(0, 3).offset....... so that it is not necessary to use the Find feature.

 

[GWteB]

Considering the content of your code I assumed by capturing you meant to say: get invoice number from user (by inputbox). Apparently that is not the case.
One way or the other, one has to start somewhere in order to be able to apply any shift / displacement / offset. So you will first have to decide / determine what your starting point is (in Excel's terms: which Range object to start from). In your example this is the (unknown) location on your worksheet of a particular invoice number. The Find method is a great way to find the location of that number. Once the desired Range object has been determined, the Offset method can be applied to it.