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