Convert spreadsheet formula to VBA

[automaticbaby]

I have a really long formula that works in an Excel spreadsheet, but I'd like to get it out of the cell due to people accidentally deleting it... continuously. I'd like the cell to only show the end result (yes/no). Is there a way to convert the formula to VBA?

Here's the formula (broken down to make it easier to read):
=IF(C2="","",
IF(ISNA(
INDEX(Price!$G$1:$G$3000,MATCH(C2&D2&E2,Prices!$C$1:$C$3000&Prices'!$D$1:$D$3000&Prices!$E$1:$E$3000,0))),
"no",
IF(B2="C",
IF(INDEX(Prices!$G$1:$G$3000,MATCH(C2&D2&E2,Prices!$C$1:$C$3000&Prices!$D$1:$D$3000&Prices!$E$1:$E$3000,0))<1,
"no",
"yes"),
IF(B2="P",
IF(INDEX(Prices!$H$1:$H$3000,MATCH(C2&D2&E2,Prices!$C$1:$C$3000&Prices!$D$1:$D$3000&Prices!$E$1:$E$3000,0))<1,
"no",
"yes")))))

 

[earthandfire]

Even if you convert the formula to a VBA function, then that will be in the cell and available for deletion.

Would it not be simpler and easier just to protect the cell?

Hope this helps.

 

[automaticbaby]

Sadly, no. For me, protecting the cell is a pain in the a$$. And then trying to remember passwords? Forget about it!

In the end, though, I would like to have just a Yes or No appear in the cell, not the formula. If that can't be done, then I would at least have the formula in the code so that if it is deleted, running the code will bring it back. (My current solution is to pull out an older version of the spreadsheet and copy the formula.)

Thanks for the suggestion, though.

 

[SkipVought]

Hi,

Here's how the standard converter works:

YOU analyze the spreadsheet expression.

For instance...
[tt]
=if(isblank(a1),"",a1)
translation
if [a1] = "" then
cell.value = ""
else
cell.value = [A1]
end if
[/tt]
I've done it many times.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[automaticbaby]

The problem I'm having is analyzing the expression.

Currently, I'm stuck on getting MATCH to work. I get a Type Mismatch error when I combine the 3 cells, but when I do each of them individually, to see where mismatch occurred, it works fine.

 

[GlennUK]

When I have to do this kind of thing ( hiding the formula ), I format all cells as unlocked ( user has free range to enter anything into almost any cell ), and have my complex formula formatted as Format/Cells/Protection/Hidden ( and maybe Locked too ). If I leave a cell like this unlocked, and need to reset it, I have a hidden sheet with the formula on it, and have a macro to copy it back into the entry worksheet if required.

Hope that gives you some ideas.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

so you are having a problem with MATCH
[tt]
MATCH(C2&D2&E2,Prices!$C$1:$C$3000&Prices'!$D$1:$D$3000&Prices!$E$1:$E$3000,0)
[/tt]
this syntax will not work in a formula.

1. first find a match on the C2 value

2. find the range having the C2 value

3. within that domain find a match on the D2 value

4. find the range within that domain having the D2 value

5. within the second domain find a match on the E2 value.

check out How can I rename a table as it changes size faq68-1331 for dynamic range using the OFFSET function as one technique using spreadsheet functions.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[automaticbaby]

Thanks, Skip.

Not the answer I wanted to hear, but it does solve my problem.