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

rrands1

New Member
Joined
Nov 7, 2004
Messages
29
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top