CODEWITHSUNDEEP
arraysvbaexcel
terny
terny

Reputation: 21

Match formula in vba array macro for excel data

  Dim rng As Range
Lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
With Sheets("sample")
    Set rng = .Range(.Range("AM1000000").End(xlUp).Offset(1, 0), .Range("AM" & Lastrow))
End With
rng.FormulaArray = "=IF(ISNUMBER(MATCH(1," & Chr(10) & "  (order!R2C17:R1000000C17=RC[-22])*" & Chr(10) & "  (order!R2C7:R1000000C7=RC[-32])*" & Chr(10) & "  (order!R2C24:R1000000C24=RC[-15])," & Chr(10) & "  0)), ""pass"",""review"")"
rng = rng.Value

I have VBA code to populate a field to compare two worksheets and return the result. However, this code is giving me only result for first line. Is there any way to make this array works for every line?

Thanks!

Upvotes: 1

Views: 173

Answers (1)

A.S.H
A.S.H

Reputation: 29352

You need to set each cell's formula alone, because the formula you are using is an array formula for a single cell. Try it this way:

With rng
  .Cells(1).FormulaArray = "=IF(ISNUMBER(MATCH(1," & Chr(10) & _
                           "  (order!R2C17:R1000000C17=RC[-22])*" & Chr(10) & _
                           "  (order!R2C7:R1000000C7=RC[-32])*" & Chr(10) & _
                           "  (order!R2C24:R1000000C24=RC[-15])," & Chr(10) & _
                           "  0)), ""pass"",""review"")"
  .Filldown
  .Value = .Value2
End With

Upvotes: 1

Related Questions