dave or someone, quick question

[Guest]

Hey dave, on this code you helped me out with yesterday, I was wondering you
could help me modify it.. instead of it displaying "no match" in the cell, I
would like it to leave the cell untouched, and only change the cell if there
is a match.. i have been messing with it and cant figure it out..
If I have it return "" then it deletes over whatever else was there..

res = Application.Evaluate(myFormula)
If IsError(res) Then
BringBack = " no match "
Else
BringBack = SourceWks.Cells(res, 7).Value
End If
.Offset(0, 6).Value = BringBack
End With

 

[Tim Coddington]

res = Application.Evaluate(myFormula)
if not iserror(res) then .Offset(0, 6).Value = SourceWks.Cells(res,
7).Value

Could what you are asking be this simple or am I missing something?
-Tim

 

[Guest]

Tim, i am extremely new to VB, chances are it is this easy..

 

[Guest]

well.. that didn't work. Now it poplulates with all the information from the
previous page instead of the select information.. i have tried messing with
it to the bets of my abilities..now i need an expert..

 

[Dave Peterson]

res = Application.Evaluate(myFormula)
If IsError(res) Then
'do nothing
Else
.Offset(0, 6).Value = SourceWks.Cells(res, 7).Value
End If

(Drop the BringBack completely--even the Dim statement.)

But I think Tim's suggestion should have worked.

Here's his code slightly reformatted.

res = Application.Evaluate(myFormula)
if not iserror(res) then
.Offset(0, 6).Value = SourceWks.Cells(res,7).Value
end if

 

[Tim Coddington]

Was you certain to include the 'NOT' in the If IsError statement where I
added it in?

General VBA coding tip:
One thing that has helped me that isn't obvious is the use of Option
Explicit. What that does is requires a Dim statement for EVERY variable you
use. Then if you miss-spell, you know immediatelly.

Yea. Lots of times I find that when something is easy to answer, it is
because I never understood the question.

-T im

 

[Bob Phillips]

Was you certain to include the 'NOT' in the If IsError statement where I
added it in?

By doing so, the do nothing statement could be removed

 

[Guest]

I could have messed it up. The way dave posted worked great. thanks everyone
for your help.