Input box and Xlookup Function

Question

I am new to VBA and trying to create a macro in VBA based on the following.

XLookup to count columns going backwards instead of a Vlookup. Once the formula finds the cell, then I would like to replace it with a number entered into an input box.

Here is what I tried. I know the replace with line is incorrect. I am able to return a value for the Xlookup. I just can't figure out the correct piece to add in the replacewith line.

Sub Macro2()
'
' Macro2 Macro
'
Dim myValue As Variant
myValue = InputBox("Enter Club Number")
Range("A1").Value = myValue
myValue2 = InputBox("Enter UNL, PREM, or DSL")
Range("B1").Value = myValue2
Range("C1").Value = Range("A1").Value & Range("B1")
Dim Lookup_Value As String
Dim Lookup_Array As Range
Dim Return_Array As Range
Dim If_Not_Found As String
Dim Result As Variant
  Lookup_Value = Range("A1").Value & Range("B1")
  Set Lookup_Array = Range("O:O")
  Set Return_Array = Range("D:D")
  If_Not_Found = "N/A"
  On Error GoTo CompatibilityIssue
Result = Application.WorksheetFunction.XLookup( _
      Lookup_Value, _
      Lookup_Array, _
      Return_Array, _
      If_Not_Found)


replacewith.InputBox ("Enter New Price")
  Exit Sub
CompatibilityIssue:
  MsgBox "It does not appear that you have access to the XLOOKUP Function"

End Sub

taller_ExcelHome · Answer 1 · 2023-09-02T23:05:20.957

Get it done with VBA's way instead of XLOOKUP. Then Excel 365 isn't must-have for your solution.

Sub Macro2()
    Dim myValue, myValue2, myValue3, Lookup_Value
    Dim c As Range, Lookup_Rng As Range
    myValue = InputBox("Enter Club Number")
    myValue2 = InputBox("Enter UNL, PREM, or DSL")
    Lookup_Value = myValue & myValue2
    ' Get the usedrange in column O
    Set Lookup_Rng = Application.Intersect(Range("O:O"), ActiveSheet.UsedRange)
    ' Search in column O
    Set c = Lookup_Rng.Find(Lookup_Value, , , xlWhole)
    If Not c Is Nothing Then
        ' Update price
        myValue3 = InputBox("Enter New Price")
        Cells(r.Row, "D").Value = myValue3
    Else
        ' Mismatch
        MsgBox "Can't find " & Lookup_Value
    End If
End Sub

Input box and Xlookup Function

1 Answers1