macros within the IF formula

[kendel]

Hello, Is something like this possible?
"=IF(ISNA(vlookup...),"""",macro)" ...a part of a macro.
There's some info here and other sites but I'm just getting more confused.
thanks
Kendel

 

[kendel]

bumped

 

[repairman615]

Hello,

Not exactly. The way in which you have posted is not possible to my knowledge. However, you could use an event (VBA) to test certain cells for their value and then call the 'macro'.

If you already have a macro post it up and I may be able to provide an example or solution.

-Jeff

 

[kendel]

Hello R.man thanks for responding! The macro is fairly long, so let me try to describe it's purpose and go from there. I do scheduling for the T-shirt printing dept of the company, and I get the work all lumped together. Every 2 weeks I'll get a spreadsheet with around 600 rows and 40 columns, no spaces or subtotals. My task is to split it up into individual daily schedules for 6 teams. The team schedule needs to include time to complete each seperate order ave size 150 shirts. The time to complete is determined by several factors included in the spreadsheet. This is where I'm stuck, some shirts get an extra print. I use a Vlookup to label them, places a yes in a cell, now I'm stuck automating the decision to make the additional calculations. I use R1C1 in this workbook, all cell references are relative so if you could keep that in mind. Can you walk me thru the 'event'?
Anyway thanks for any help or advice,
Kendel

 

[mikerickson]

If by "macro", you mean UDF, then =IF(ISERROR(VLOOKUP(...),"",MyUDF()) is a fine formula. But UDF's, when called by from a sheet formula, are restricted in what they can do.

 

[kendel]

Mike you're such a tease, thanks for the response.
The whole situation revolves around the fact that I'm stuck in a cell that has 2 possibilities and the macro can't see what the result is.
Is it possible to run the UDF within the primary Subroutine loop?
I need the UDF to:
look in say RC18 and if RC18=BRAND, then copy R[-1]C6 into RC6...
If RC18 ="", then continue the primary macro.
So as the macro loops, the VLOOKUP will determine this particular order needs an extra print and the UDF will populate a few cells with formulas...

next i.

Thanks again for your help,
Kendel

 

[repairman615]

Hello,

I may be missing something.

Why not use an IF and not a UDF?

Like:

'''Here is the vLookup formula being inserted into RC18
 
IF RC18 = "Brand" Then
    '''TRUE ... Copy Code
Else
    '''FALSE ... Do something or delete this _
    line and the line above when not needed
 
End If

-Jeff

 

[kendel]

Hey R.man thanks again.
I need a way to insert formulas into cells depending on
if C18: =IF(ISERROR(VLOOKUP(R[-2]C[-8],list,2,FALSE)),"",VLOOKUP(R[-2]C[-8],list,2,FALSE)) = BRAND, or "". I tried a nested if but I don't know how to move the active cell from inside the IF statement. I want to use something like 'activecell.offset(-1,3).Select' as the do if true part of it. It won't let me use .Select and without it I get #NAME type error.
Any help is appreciated guys. I think Excel is fun and you guys are making it funner!
Kendel

 

[kendel]

Hello,
R.man it was me missing the forest for the trees, nested IF's did the trick with no need of event horizons and quarks from the UDF.
Thanks again guys,
Kendel

 

[repairman615]

Q

Hello Kendal,

Glad to help!

Check into the Evaluate statement in VBA. That will solve for a formula and return an answer.