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
 

You've got an active thread in .programming.

"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.

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







--

Dave Peterson