Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |