in VBA for VLookup formula, .range & .cell & : in between range row & column, it gives compile error sub / function not defined

[dilippshah10]

Private Sub UserForm_Initialize()


CoName.Value = (Sheet1.Range("B1"))
CoAddress.Value = (Sheet1.Range("I12"))
CoGSTIN.Value = (Sheet1.Range("I15"))

ColumnN.Value = ActiveCell.Offset(0, 2).Value

ColumnG.Value = ActiveCell.Offset(0, -5).Value
ColumnC.Value = ActiveCell.Offset(0, -9).Value
'ColumnI.Value = ActiveCell.Offset(0, -3).Value
TextBox1.Value = ActiveCell.Offset(0, -3).Value
'On Error Resume Next
'TextBox7.Value = Application.WorksheetFunction.VLookup(ColumnN.Value, ("ARPartiesList"R5C2:R264C22), 15, False)

TextBox7.Value = Index((Sheet35.Range("P515")), Match(ColumnN.Value, (Sheet35.Range("B5:B15")), 0))

'=INDEX($A$2:$A$14, MATCH(1799, $C$2:$C$14, 0))
'TextBox7.Value = IIf(VLookup(ColumnN.Value, ("ARPartiesList"!("B5:B264")), 1, True) = ColumnN.Value, VLookup(ColumnN.Value, (Sheet35.Range("B5:V264")), 13, True), NA())

 

[Marc L]

• Thanks to edit your post and use the appropriate code tags via this icon :
  
  
  
  
  
  
• As your picture does not show the obviously necessary in order to help you, just attach your workbook …

 

[Chihiro]

TextBox7.Value = Index((Sheet35.Range("P5:P15")), Match(ColumnN.Value, (Sheet35.Range("B5:B15")), 0))

I assume issue is with this line. Index or Match isn't VBA function and need to be qualified as something like below...

TextBox7.Value = Application.Index((Sheet35.Range("P5:P15")), Application.Match(ColumnN.Value, (Sheet35.Range("B5:B15")), 0))

Alternately, you can use Application.WorksheetFunction or use other approach (such as use of Evaluate).

 

[Marc L]

Never use WorksheetFunction but just Application in particular when the result can be an error like with MATCH …​

​

Just test the result with IsError or IsNumeric functions.​

​