0

I am trying to make my index-match formula to find values and paste them in a new worksheet. I use a for to swipe the entire range. However, my code is not working and I do not understand why.

Here is an extract of it.

Thanks!

    Dim dimensions As Long
    dimensions = Worksheets("Rel_Raw").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
    Dim j As Long

    For j = 2 To dimensions Step 1
    Worksheets("Data").Cells(j, 1).Value = WorksheetFunction.Index(Worksheets("Rel_Data").Range("I1:I" & dimensions), _
    WorksheetFunction.Match(Worksheets("Data").Cells(k, 16).Value, Range("I2:I" & dimensions), 0))
    Next
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Luis Antonio Gómez
  • 11
  • 3
  • 1
    You are not declaring the parent sheet in the match range. – Scott Craner Mar 17 '20 at 14:44
  • and what is `k`? it is not declared. Nor is it assigned a value. – Scott Craner Mar 17 '20 at 14:45
  • I also see three sheets all using the same `dimensions` as the last row, are all three sheets the same used ranges? – Scott Craner Mar 17 '20 at 14:48
  • Are you using `SpecialCells(xlCellTypeConstants).Count` to try to get the last row? – BigBen Mar 17 '20 at 14:48
  • Thanks for your reply! @Scott, I assigned the parent sheet to that range, and changed the k for j (it was my bad and did not notice). Also, dimensions is intended to get me the number of rows in the table to restrict the copy/paste effort and reduce the time consumed. – Luis Antonio Gómez Mar 19 '20 at 16:26
  • @BigBen, I am using that part of the code to count how many rows there are in order to limit the lookup search and make it more efficient, if that makes sense – Luis Antonio Gómez Mar 19 '20 at 16:43
  • [This approach](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) may be better. – BigBen Mar 19 '20 at 16:46

0 Answers0