Problem with IIf, help needed

[Pawe³ Zalewski]

Hi,

I've problem with VBA & excel:
main idea of this is:

in sql database i have mountly accounts balance

on sheet1 i have formula fex.
(#123..199)
its mean that i have to add balance for accounts from 123 to 199

sometimes formula look like that : ((#120)+(#130..150)-(#155))*0,5

on sheet2 I create table for result of this formula on every mount
i do connection to database, get all static (#120) and range (#130..150)
values
and put it in to values, so this values can look like this:
string value FJan = ((-85)+((45)+(-3))+(123))*0,5 for january
string value FFeb = ((10)+((-50)+(-3))+(0))*0,5 for febuary

on sheet1.B5 if have: X2+X3>0
it means that I have to add values from sheet2.B2+sheet2.B3 for January
(sheet2.C2+sheet2.C3 for Febuary) and check if its >0 if yes than put value
in to cell on sheet2 from FJan

I have problem with IIf in VBA

for January: Sheet2.Cells(1,3).Value = IIf(Val(Replace(Sheet1.Cells(2,5),
"X", "B")), "="& FJan, 0)
for Febuary: Sheet2.Cells(2,3).Value = IIf(Val(Replace(Sheet1.Cells(2,5),
"X", "C")), "="& FFeb, 0)

i get false everytime
Val(Replace(Sheet1.Cells(2,5), "X", "B")) isnt boolean it could be the
problem

REPLACE()
works with 3 parameters too, it takes 0 as start point then
but this is not a problem main issue is that IIf doesnt works
i can do like this:

String Temp = Replace(Sheet1.Cells(2,5),"X", "B")
IIf(Val(Temp), "="& FJan, 0)

Temp is string value
Function VAL is double
i need to check on values on sheet2 this: Temp->(B2>B3)
and recive boolean result


Can anyone have me with this, please

Paul

 

[Cindy M.]

Hi Paul,

I've problem with VBA & excel:

Since you haven't gotten a response here, try asking in the
more specialized excel.programming newsgroup.

FWIW, IIF is not something you should really use in
programming code. It's fine in a cell formula, where you
don't have the option to write out an IF code block. But in
VBA you should use a construct with

If [comparison] Then

Else

End If

With ElseIf as required. You may think it's more work /
more lines of code and thus less efficient, but in
execution that isn't the case. And if you break it down
then walk through your code the problem may become more
apparent.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail