do an if...then loop if match is found

[rrands1]

I have the following VBA line:

tsheet.Range("N" & x).Value = WorksheetFunction.IfNa(Application.Index(ssheet.Range("I2:I" & Sourcelastrow), _
Application.Match(tsheet.Range("G" & x).Value & " " & tsheet.Range("H" & x).Value, _
ssheet.Range("U2:U" & Sourcelastrow), 0)), "")

Where:
tsheet = workbook1, sheet1 (target sheet)
ssheet = workbook2, sheet1 (source sheet)
x = a for/next loop cycling through all the rows in tsheet
slastrow = last row # in ssheet

Goal:
if we find a match from ssheet.range in tsheet.range, then instead of just filling in tsheet.range("N") & x).value), I want to start an if/then loop

The above statement works, in that it does fill in the single cell when a match is found, but I need to do a couple more things, so hoping I can start the if/then, but I have tried a few things w/o success. Any ideas?


Secondarily (or bonus Q... : in the above statement, the IFNA has the option of putting an alternate value into tsheet.range("N") & x).value), which I currently have set to "". This is causing an issue in that excel is interpreting that as "0" (zero), when what I actually want is an empty cell. Is there something I can put in there instead that would make it just empty?


Thank you so much!!

-randy

 

[offthelip]

Using index match the way you have programmed it is a very slow way of doing this task. It is ok for a few lines of code but if you wnat to put it inot a loop it will take a long time. The fast way of doing this is to use variant arrays . I have rewritten your code using variant arrays which load all the data from both sheets into memory and then does the whole thing in memory. you haven't said where you want the output so I added sheet and wrote the values to the new sheet.

Sub tst()
Dim outarr()
'tsheet.Range("N" & x).Value = WorksheetFunction.IfNa(Application.Index(ssheet.Range("I2:I" & Sourcelastrow), _
'Application.Match(tsheet.Range("G" & x).Value & " " & tsheet.Range("H" & x).Value, _'
'ssheet.Range("U2:U" & Sourcelastrow), 0)), "")

xlastrow = 20 ' set this to the last row of data on the tsheet worksheet
ReDim outarr(1 To xlastrow, 1 To 2)
datarr = ssheet.Range("a1:u" & sourcelastrow) ' load all the data from Sheet into a variant array
inarr = tsheet.Range("a1:H" & xlastrow) ' load all the data from T Sheet into a variant array
  For x = 2 To xlastrow  ' loop round all the values you want to match on tsheet
     matchitem = inarr(x, 7) & " " & inarr(x, 8) ' this concatenates values from column g (7) and H (8) you have to use number with arrays, not letters!!
  ' you can add if statements in here quite easily
     For y = 2 To sourcelastrow
      If matchitem = datarr(y, 21) Then ' column U  '   ' you can add if statements in here quite easily
                                                            ' or here
        outarr(x, 1) = matchitem
        outarr(x, 2) = datarr(y, 9) 'column I
        Exit For
      End If
     Next y
  Next x
Worksheets.Add
Range(Cells(1, 1), Cells(xlastrow, 2)) = outarr

End Sub

this could be up to a 1000 times faster than your code depending on the ranges