vba =IFERROR(MATCH(B2,B3:B$2904,),"")

[montecarlo2012]

Hello.

I attempted to write a loop for this formula

=IFERROR(MATCH(B2,B3:B$2904,),"")

FOR XX = 2 TO Cells(Rows.Count, "B").End(xlUp).Row

        FOR YY = 11 TO 16

                         CELLS(XX, YY).FORMULA =”=IFERROR(MATCH(CELLS(2, 2).ADDRESS & “ , “ CELLS(3, 2).ADDRESS & “ : “ & CELLS(2904, 2,),””””)”

        NEXT

NEXT

but, I really don't know to change the cells value for variables,
I understand that I will need more than xx and yy but......

My source data is a dynamic array located at ("B2:G2904") at this time
and then the output would be (" I2:N ")

Please
some help here.

Thanks.

 

[DanteAmor]

Try this.
If you want only the formulas, then delete this line: .value = .value

Sub matchformula()
  With Range("K2:P" & Range("B" & Rows.Count).End(3).Row)
    .Formula = "=IFERROR(MATCH(B2,B3:B$2904,),"""")"
    .Value = .Value
  End With
End Sub

 

[Michael M]

A couple of points to note.
1. When posting please post ALL the relevent code , not just a snippet
2. declare ALL variables, or at least show us that you have
3. When using a FOR ? NEXT loop, refer to you variable at the next lin..ie, Next xx or Next yy....it will make it easier in future to debug AND read
Try

Sub MM1()
For XX = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    For YY = 11 To 16
        Cells(XX, YY).Formula = "=IFERROR(MATCH(" & Cells(2, 2).Address & "," & Cells(3, 2).Address & ":" & Cells(2904, 2).Address & " ),"""")"
    Next
Next
End Sub

 

[Michael M]

@DanteAmor
I wasn't sure if the OP was needing the actual cell address as they have requested same in previous posts when we have replied with simple cell references

 

[montecarlo2012]

Sorry Mr. Michael about that, for future references I will put attention, and thank you for your reply.

 

[montecarlo2012]

DanteAmor I already mark as solution, work good.
I have just one question.
this line ►Formula = "=IFERROR(MATCH(B2,B3:B$2904,),"""")" ◄
the 2904 I have to change everytime or the first line will do that.
and thank you so much
for your help.

 

[DanteAmor]

The number $2904 is static in the formula, the macro will always put the number $ 2904 in all formulas.
If you want it dynamic, let's say the last row with data from column B would be like this:

Sub matchformula()
  Dim lr As Long
  lr = Range("B" & Rows.Count).End(3).Row
  With Range("K2:P" & lr)
    .Formula = "=IFERROR(MATCH(B2,B3:B$" & lr & ",),"""")"
    .Value = .Value
  End With
End Sub

 

[montecarlo2012]

Thank you DateAmor work perfect. I tried to mark as solution, but, the algorithm they use just let me click one time sorry, \
You are great.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.