Problem with IIf

[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

Can anyone have me with this, please

Paul

 

[Guest]

You should write
Val(Replace(Sheet1.Cells(2,5), "X", "B")) >0

The replace function requires 4 parameters and your example only has three.
REPLACE(old_text,start_num,num_chars,new_text)

 

[Pawe³ Zalewski]

Hi (Still help needed),

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

I realy dont know how to do it
HELP...

Paul

 

[Guest]

I don't see the point of the replace:

? val("123X")
123
? val("123B")
123
? val("B123")
0
? val("X123")
0


val stops evaluating as soon as it hits a characters that isn't a number.
If you string starts with a character, then it returns zero (as shown)
regardless of what that character is. Zero and False are seen by IIF as
False. True and any other number than zero are seen as true.

 

[Pawe³ Zalewski]

Hi,

Ok so I know why its allways False

but.. still have problem with IIf

i can write
IIf(Replace(Sheet1.Cells(2,5),"X", "B"), "="& FJan, 0)
but than i get an error: Type mismatch.

For VBA: Replace(Sheet1.Cells(2,5),"X", "B") is String value
what schould I do to check i IIF what is under this value and execute this
on sheet2? and get boolean value?

 

[Guest]

Your question is

=iif(something that evaluates to a string, "="& FJan, 0)

since I have no idea what you are testing for or what the string is, best I
can say is add commands that will produce either a boolean or a number from
the string that will then produce the desired result from the IIF statement.
If you want to tell us what your string will look like and what condition
you want to test for, then someone may be able to help.

 

[Guest]

fro debugging i often add intermediate steps to help isolate problems. I your
case I would add

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

The look and see the value of test. if you need help post the results.