Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

modify formula Index Match in Macro to "go to next" if match not found

modify formula Index Match in Macro to "go to next" if match not found

modify formula Index Match in Macro to "go to next" if match not found

I will highly appreciate your help.
I have this code which searches and Match corresponding column with exact values from another workbook. to make it more clear, in WB1 i have two columns a:a and b:b with data and on the other WB2 same BUT with more data in column a:a. all the data in column a:a WB1 are in WB2 column a:a but not the other way round.
My code searches and matches and copy paste the exact data from the two WB's but the only thing it doesn't do is to Skip the data which are in WB2 but absent in WB1.
can somebody help me modify this code to skip this cells and go to next OR if you have a better code, can you help.
Thank you. Here is the Macro that i am using at the moment.

Dim DataFile, WorkingFile As Workbook
Dim DataLastRow, WorkingLastRow As Double
'Turn off screen updating
Application.ScreenUpdating = False
'Open the combined file
Set DataFile = ActiveWorkbook
DataLastRow = Range("A65536").End(xlUp).Row
Workbooks.Open Filename:="H:\A350_PREPS\Macro\Strain_Prediction_FST_All_LCs_using_DFEMs_v2.xls"
Set WorkingFile = ActiveWorkbook
WorkingLastRow = Range("A65536").End(xlUp).Row
On Error Resume Next
Range("B2:B" & WorkingLastRow).Formula = _
"=INDEX(" & DataFile.Name & "!B$2:B" & DataLastRow & ",MATCH($A$2:$A$" & WorkingLastRow & _
"," & DataFile.Name & "!$A$2:$A$" & DataLastRow & ",0))"
On Error GoTo 0

Range("B2:B" & WorkingLastRow).Copy
Range("B2:B" & WorkingLastRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False
'Turn on screen updating
Application.ScreenUpdating = True
End Sub

RE: modify formula Index Match in Macro to "go to next" if match not found

Have you tried an if statement in a loop?
For example:
If WB2.cell(x,1) = "" then
WB2.cell(x,1) = WB2.cell(x,1)
end if
x = x+1

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close