Excel VBA with Index Match

Question

Have been trying to solve below but no luck so far, seeking for your help :

vba formula return with extra two "@", how to fix the code or remove the "@" in cell R1?

If i want to do a loop and run the vba code from cell R1 to R30, which part of the code should i modify?

 Dim PartPoError As Range
 Dim PART As Range
 Dim PO As Range
 Dim lastrow As Long


   lastrow1 = Sheets("TEMPLATE").Cells(Rows.Count, "A").End(xlUp).Row
   x = "J" & lastrow1
   t = "A" & lastrow1
   V = "B" & lastrow1

   Set PartPoError = Sheets("rpt_sense").Range("A1", x)
   Set PART = Sheets("rpt_sense").Range("A1", t)
   Set PO = Sheets("rpt_sense").Range("B1", V)

   With Worksheets("rpt_sense")
     .Range(.Cells(2, 23), .Cells(lastrow1, 23)).ClearContents
     .Range(.Cells(2, 23), .Cells(lastrow1, 23)).Formula = "=A2&B2"
   End With

   lastrow2 = Sheets("template").Cells(Rows.Count, "A").End(xlUp).Row

   For s = lastrow2 To 16 Step -1
  If Sheets("template").Cells(s, 10).Value = "" Then

      ' Stuck in below formula error....it retunred with extra two "@" in cell formula R1.....

      Sheets("template").Cells(s, 10) = "=INDEX('rpt_sense'!r:r" & ",MATCH('template'!c8" & "&'template'!B" & s & ",'rpt_sense'!a:a" & "&'rpt_sense'!f:f" & ",0),1)"

     End If

     Next s

picture file - index match id and date in yellow from another worksheet

picture 2 - data from another worksheet

The addition of @ in the formula isn't a mistake in itself. Excel adds this to control spill which may well be what you want. The real question is, does the formula work? Another one is whether you want the formula in the cell or its result. I'm always rather reluctant to get VBA to write a formula instead of doing the job itself (maybe using the same function). — Variatus
Hi, thanks for your info and noted. The formula is not working with the extra "@", i need to replace it with a blank with CTRL + H. My reason of doing this is because i got a range from R1 to R30 need to search and look up the data from another worksheet, and i am searching for better way to do this. — talent greatness
If you would tell us the lookup value, where the data are and where you want the result we might well come up with the code you want. But as things stand we just can't get a picture of what you need by looking at code that doesn't work. Try another approch of explaining your question. — Variatus
What about .Formula2 (e.g. rng.Formula2 = "=...") ? @talentgreatness — T.M.

MD Ismail Hosen MD Ismail Hosen · Accepted Answer · 2020-09-02T04:45:45

Sorry i don't understand fully what you wanted to do..Can you share screenshot with what your final output will be look like..

 Dim PartPoError As Range
Dim PART As Range
Dim PO As Range
Dim lastrow As Long


lastrow1 = Sheets("TEMPLATE").Cells(Rows.Count, "A").End(xlUp).Row
 
'I think this variant variable is not required if you just want to use range.

x = "J" & lastrow1
t = "A" & lastrow1
V = "B" & lastrow1
'The change will be like this.
Set PartPoError = Sheets("rpt_sense").Range("A1","J" & lastrow1)
  Set PART = Sheets("rpt_sense").Range("A1", "A" & lastrow1)
  Set PO = Sheets("rpt_sense").Range("B1","B" & lastrow1)

  With Worksheets("rpt_sense")
 .Range(.Cells(2, 23), .Cells(lastrow1, 23)).ClearContents
 .Range(.Cells(2, 23), .Cells(lastrow1, 23)).Formula = "=A2&B2"
   End With

   lastrow2 = Sheets("template").Cells(Rows.Count, "A").End(xlUp).Row

   For s = lastrow2 To 16 Step -1
  If Sheets("template").Cells(s, 10).Value = "" Then
  'Please clear what actually you want to do here..
  ' Stuck in below formula error....it retunred with extra two "@" in cell formula R1.....

  Sheets("template").Cells(s, 10) = "=INDEX('rpt_sense'!r:r" & ",MATCH('template'!c8" & "&'template'!B" & s & ",'rpt_sense'!a:a" & "&'rpt_sense'!f:f" & ",0),1)"

 End If

 Next s

Excel VBA with Index Match

2 Answers