IIf VBA Problem
 

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?





U¿ytkownik "Tom Ogilvy" napisa³ w
wiadomo¶ci ...
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.

--
Regards,
Tom Ogilvy


"Pawe3 Zalewski" wrote:

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-(B2B3)
and recive boolean result

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

Paul





U?ytkownik "Joel" napisa3 w wiadomo?ci
...
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)



"Pawe3 Zalewski" wrote:

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+X30
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

IIf VBA Problem
 

On Mon, 26 Mar 2007 12:10:38 +0200, "Pawe³ Zalewski"
wrote:

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.


Your syntax seems completely screwed up, and I don't see you doing anything
with the result. Perhaps you are trying to use the syntax for the Excel IF
worksheet function, which is a different function.

Look at HELP and the Example for the IIF function in VBA HELP.

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

In your statement:

expr = Replace(Sheet1.Cells(2,5),"X", "B")

I don't see how that expr will evaluate to True or False

truepart = "="& FJan

Is FJan defined someplace in your VBA routine? Or are you not using Option
Explicit?

Finally, since the IIF function returns something, you need to store that
return value in something. I don't see that being done.
--ron

IIf VBA Problem
 

Under
expr = Replace(Sheet1.Cells(2,5),"X", "B") in the cells i have syntax to
check
fex.B6B7 or B3+B50
value under theb cells are True or False values







Uzytkownik "Ron Rosenfeld" napisal w wiadomosci
...
On Mon, 26 Mar 2007 12:10:38 +0200, "Pawe³ Zalewski"

wrote:

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.


Your syntax seems completely screwed up, and I don't see you doing
anything
with the result. Perhaps you are trying to use the syntax for the Excel
IF
worksheet function, which is a different function.

Look at HELP and the Example for the IIF function in VBA HELP.

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

In your statement:

expr = Replace(Sheet1.Cells(2,5),"X", "B")

I don't see how that expr will evaluate to True or False

truepart = "="& FJan

Is FJan defined someplace in your VBA routine? Or are you not using
Option
Explicit?

Finally, since the IIF function returns something, you need to store that
return value in something. I don't see that being done.
--ron

IIf VBA Problem
 

Well, what do you get when, in the Immediate Window, when you enter:

?Replace(Sheet1.Cells(2,5),"X", "B")


Also, where are you returning the result of the IIF function???

Look at the Example in HELP!!


On Mon, 26 Mar 2007 13:43:41 +0200, "Pawel Zalewski"
wrote:

Under
expr = Replace(Sheet1.Cells(2,5),"X", "B") in the cells i have syntax to
check
fex.B6B7 or B3+B50
value under theb cells are True or False values







Uzytkownik "Ron Rosenfeld" napisal w wiadomosci
.. .
On Mon, 26 Mar 2007 12:10:38 +0200, "Pawe³ Zalewski"

wrote:

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.


Your syntax seems completely screwed up, and I don't see you doing
anything
with the result. Perhaps you are trying to use the syntax for the Excel
IF
worksheet function, which is a different function.

Look at HELP and the Example for the IIF function in VBA HELP.

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

In your statement:

expr = Replace(Sheet1.Cells(2,5),"X", "B")

I don't see how that expr will evaluate to True or False

truepart = "="& FJan

Is FJan defined someplace in your VBA routine? Or are you not using
Option
Explicit?

Finally, since the IIF function returns something, you need to store that
return value in something. I don't see that being done.
--ron


--ron

IIf VBA Problem
 

Even getting a TRUE or a FALSE result here, you still have the other issues I
mentioned.



On Mon, 26 Mar 2007 13:43:41 +0200, "Pawel Zalewski"
wrote:

Under
expr = Replace(Sheet1.Cells(2,5),"X", "B") in the cells i have syntax to
check
fex.B6B7 or B3+B50
value under theb cells are True or False values







Uzytkownik "Ron Rosenfeld" napisal w wiadomosci
.. .
On Mon, 26 Mar 2007 12:10:38 +0200, "Pawe³ Zalewski"

wrote:

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.


Your syntax seems completely screwed up, and I don't see you doing
anything
with the result. Perhaps you are trying to use the syntax for the Excel
IF
worksheet function, which is a different function.

Look at HELP and the Example for the IIF function in VBA HELP.

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

In your statement:

expr = Replace(Sheet1.Cells(2,5),"X", "B")

I don't see how that expr will evaluate to True or False

truepart = "="& FJan

Is FJan defined someplace in your VBA routine? Or are you not using
Option
Explicit?

Finally, since the IIF function returns something, you need to store that
return value in something. I don't see that being done.
--ron


--ron