VLookup in VBA

[mgw1138]

I keep getting "unable to get vlookup property of the WorksheetFunction class"
The table is 4 columns: id number, description, Width, Height
The sheet has 24 rectanges I want to make each active and then call the macro and input rectange number.
I was going to get more sophisticated with an external loop but I can not get a single retangle to modify.

Thanks in advance


Dim DoBox As Integer
Dim WorkRec As String

DoBox = InputBox("What Rec")
WorkRec = "Rectangle " & DoBox

With ActiveSheet.Shapes.Range(Array(WorkRec))
.Width = Application.WorksheetFunction.VLookup(DoBox, "o2:r24", 3)
.Height = Application.WorksheetFunction.VLookup(DoBox, "o2:r24", 4)
.Text = Application.WorksheetFunction.VLookup(DoBox, "o2:r24", 2)
End With

 

[mart37]

"o2:r24" --> Range("o2:r24")

 

[Kenneth Hobson]

I would use Range's Find rather than =vlookup. vlookup requires that the data be sorted.

Besides the range issue in vlookup, you need the line before End With to set the Text property.

Sub Main()
  Dim DoBox As Integer, WorkRec As String, s As Shape
  
  DoBox = InputBox("What Rec")
  WorkRec = "Rectangle " & DoBox
  'On Error GoTo EndNow
  Set s = Sheet1.Shapes(WorkRec)
  'ActiveSheet.Shapes.Range (Array(WorkRec))
  With s
    .Width = Application.WorksheetFunction.VLookup(DoBox, [o2:r24], 3)
    .Height = Application.WorksheetFunction.VLookup(DoBox, [o2:r24], 4)
    .TextFrame.Characters.Text = Application.WorksheetFunction.VLookup(DoBox, [o2:r24], 2)
  End With
  
EndNow:
End Sub