# Debugging A Nested Function

Oct 2, 2009I am receiving an error using the following string as I am not using the OR request properly.

=IF((+$B7&$C7&$D7&$E7)="","",IF(+D7="",+I6,+D7) OR IF(+D7="(blank)","",+D7))

I am receiving an error using the following string as I am not using the OR request properly.

=IF((+$B7&$C7&$D7&$E7)="","",IF(+D7="",+I6,+D7) OR IF(+D7="(blank)","",+D7))

ADVERTISEMENT

I am presently trying to debug a large and repetitive piece of code and find that I keep ending up in a function within the same module. I assume this is because the function is being triggered as I manipulate data. If this is the case, does anyone know how to suspend the function while I debug? I guess I could comment it out as I do not need the data it is returning but during normal running, this is not possible and I expect if it is fired off then it will slow things down considerably if my code keeps getting interrupted by interreupts from the function.

View 2 Replies View RelatedI keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

I am trying to create a nested IF function (has 5 IF statements), however,

every time I put the brackets at the end of the function, it puts a little .

next to the false answer, removes the brackets and comes up with an error

message?

This is only happening on one particular PC, when I create the same formula

on other pC's, it works fine so I know my formula is correct.

is there a setting on the PC that must be wrong?

I have an assignment in which I have to use nested, IF, AND and OR functions. Does anyone know the syntax for this kind of function? Basically I have to determine leave time based on years of service and whether the employee is full time or part time. Part-time with less than 1.5 years of service = 0 days of leave Part-time over 1.5 and full-time under 1year = 3 days of leave and full time over 1 year = 5 days of leave. I know how to do a nested function, but I have not worked with both an AND and OR together.

View 10 Replies View RelatedThis formula seems to work fine for the first two logical tests, but when it come to adding the third, I get an error.

=IF(E5="Hourly",C5*D5,IF(E5="Daily",(C5*D5)*8,IF(E5="Weekly",(C5*D5)*32,''"))

I am struck in using IF /AND in nested function as follows,

A1B1C1D1E1F1G1

1 3 5 7 9 32

=IF(AND(F2>G2,AND(IF(A2>1,A2,AND(IF(C2>5,C2,AND(IF(E2>9,E2),""))

(Above, A1 to >G1 are cell address , in exact rows are actual values)

I need to keep track of my salesmen's time. However, the company will not pay for the first 45 minutes so I need a formula that says, if the time entered in under "worked" is less than 45 minutes, no time will be deducted. If the time entered is equal to or greater than 45 minutes, 45 minutes will be deducted. These are the columns needed and the Daily Hours would be the total of all the previous columns. WORKED TRAVELED LUNCH DAILY HOURS

View 2 Replies View RelatedI am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T

he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is

=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1)).

VB:

Dim iInput_Rows As Integer

iInput_Rows = Worksheets("Workspace").Range("D3").Value

Dim iOutput_Rows As Integer

iOutput_Rows = Worksheets("Workspace").Range("D1").Value

Dim iAnalog_Rows As Integer

[Code] ....

My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?

VB:

Worksheets("COMMISSION").Range("B19 + iOutputs_Rows").Formula = "=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1))"

I'm writing a spreadsheet where I need to calculate a nested IF function (I think), and I am unsure of how to do it.

The problem is this:

The total $ value of the quote is entered, then depending on two variables, a rebate may be deducted from this total value. The first variable is eligibility (a Y/N response), the second is the type of project - there are two types of project and each attract a different rebate amount.

So I have:

A: total quote $

B: Eligibility - 'Y' or 'N' (if 'Y', then value of 'C' is deducted from 'A'), (if 'N', then no value is deducted from 'A')

C: Type - '1' or '2' (if '1', then 'C' = $1000), (if '2', then 'C' = $1600)

D: final result

I have multiple sheets within one Excel file. Each sheet is structured the same way. Column B lists Client's last names, which differ from sheet to sheet, though some clients may be repeated in several tabs. I have a column K which displays either the number 1 or 0. This is consistent between sheets.

I have created a formula that will look at the last name in a specific cell in column B of sheet 2 (FY 07) and search sheet 1 (FY 06) column B for the same last name.

=IF(MATCH($B5,'FY 06'!$B$5:$B$24,0),1,0)

If the same last name exists and if column K of sheet 2 and sheet 1 display a "1", I want it to place a 0 in the formula cell.

So, if there is a client that is in both sheets and has a "1" in both columns K of each sheet, I want the formula to let me know.

following

i tried formula =IF(F$1

I amtrying to find a way to nest a formula in the Match function.

How can i make the following formula more dynamic so that I do not specify the colum "AY"?

=MATCH(11,Data!$AY:$AY,0)

The information is contained in column AY labeled "New-Rate" in the data sheet

i have several days trying to convert a If nested function in a automate macro, the problem is that i have a lot of formulas, and the machine becomes slow, so i have to apply this formula to "column G"

=IF(LEFT(F2;1)="J";"C";IF(LEFT(F2;1)="g";"C";(IF(ISERROR(VLOOKUP(F2;rifs;1;FALSE));"NC";"C"))))

I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?

I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?

If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.

I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))

View 2 Replies View RelatedI'm using this as a formula =ReturnClient(A1) for example. I put the msgbox in there just to get some feedback on what was going on in there, and it does correcty show the first two digits of the cell it's referencing, but the select case is not ever hit.

Function ReturnClient(refNumCell As Range) As String

Dim cell As Range

Dim clientInit As String

Dim first2Dig As String

For Each cell In refNumCell.Cells

first2Dig = Mid(cell.Value, 1, 2)

MsgBox (first2Dig)

= OFFSET(AutoMatrix!$B$1,MATCH(C3,TEST),MATCH(H1,AutoMatrix!C1:Z1,0))," ")

Can anyone seem to find any fault on this statement?

When i try to SHIFT,CTRL & ENTER - it's complaining about semi cols. on the extreme right

TEST - is a Name definition

AUTOMATRIX - is Worksheet name

I want to substitute the following "special" characters for an underscore. i need to do 9 different characters!!! is there another way?

as you are limited to 7!!!

excel 2003!

=INDIRECT("_animal_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))

is there a way to turn the following formula into a function ?

=IF( COUNTA(A1:A7)=SUMPRODUCT((A1:A7<>" ")/COUNTIF(A1:A7,A1:A7&"")),"No Duplicates","Duplicates")

I just have no idea where to start ?

I had more than 7 nested if statements so I attempted to formulate the named ranged formula. My 2 ranges are as follows: LDD1=IF(MONTH(P1)=MONTH(F2),C5-F5,IF(MONTH(P1)=MONTH(G2),C5-G5,IF(MONTH(P1)=MONTH(H2),C5-H5,IF(MONTH(P1)=MONTH(I2),C5-I5,IF(MONTH(P1)=MONTH(J2),C5-J5,IF(MONTH(P1)-MONTH(K2),C5-K5,IF(MONTH(P1)=MONTH(L2),C5-L5))))))) LDD2=IF(MONTH(P1)=MONTH(L2),C5-L5,IF(MONTH(P1)=MONTH(M2),C5-M5,IF(MONTH(P1)=MONTH(N2),C5-N5,IF(MONTH(P1)=MONTH(O2),C5-O5,IF(MONTH(P1)=MONTH(P2),C5-P5,IF(MONTH(P1)-MONTH(Q2),C5-Q5))))))

Basically what it's doing is comparing the current month with the month in the column heading and calculating appropriately. However, when i input the if statement (=if(LDD1,LDD1,LDD2), i get an error saying that it's an inconsistent formula and the result that it gives is wrong, too. Problem cell is e5

I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.

Issue: All I would like to do is for a NESTED IF Function to be able to say this:

1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square

2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.

3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.

If 'Column A' - 4" Square, Then 'Column B' - A

If 'Column A' - 5" Square, Then 'Column B' - B

& So on...

But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:

User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A

User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B

& So on...

Currently to do the above function, I have utilised the following Excel NESTED IF Function:

[Code] .....

& The following happens:

1. User inputs data into 'Column A'

2. The NESTED IF Function then provides an automatic response.

3. However it relies on the data being inputted without the ' " (Quotation Symbol)'

4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"

Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.

Example - IF Function.xlsx

I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.

View 9 Replies View RelatedI got an error message that I cannot save while debugging. But I have repeatedly hit the stop icon to stop debugging!

I have minimized all the windows (except the spreadsheet as it does not allow me select it and just beeps) to make sure there are no hidden message boxes.

I do not want to force exit and lose my work but I cannot get out of debug mode for some reason!

I sent the attached to a user who said it wouldn't run when the 'Draw Venn' button is click - some sort of RefEdit compile error. I can't replicate the error and can't find what's causing the error.

I had started using RefEdit control but couldn't get it to work the way I wanted, so just went with input boxes. I run this on XL07 SP2, other user has XL03 - not sure if that's relevant to the error they're getting.

Within the code I am asking excel to sort two difference worksheets with virtually the same code, except that one of the worksheets does not get sorted. If I step through the code, it works and the worksheet is sorted, but if I just run the macro the worksheet does not get sorted, as if the line of code in question is being skipped. Pointedly, my question is - Why does the code work while debugging (stepping through) but not when I generally run it?

Also, as far as the code is concerned, I am sure that the all variables contain the information I need them to and am also sure that any ranges created from those variables are also the ranges they should be. Here are the two lines of code that are supposed to sort. The UBOC cash sheet gets sorted, but the LNB cash sheet does not.

'Sort the UBOC cash sheet.

wsUBOC.Range("A5:G" & lUBOCRow).Sort Key1:=Range("A6"), Order1:=xlAscending, Key2:=Range("D6"), _

Order2:=xlAscending, Key3:=Range("G6"), Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, _

MatchCase:=False, Orientation:=xlTopToBottom

'Sort the LNB cash sheet

wsLNB.Range("A1:G" & lLNBRow).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2"), _ .............

What is the quickest/best way to find out exactly what is causing a Type 13 Mismatch error?

Debugging with F8 is taking forever as I have to go through loops that go through 6000+ rows each...

I am entering the following Nested IF(AND statement into a cell and getting a message stating "You've entered too many arguments for this function." Any idea on what formula I can use to get the needed information into this cell based on the fact that there are different inputs that can be entered into the related cell that will trigger the needed respons in the working cell?

=IF(AND(K1051="Down",L1051<2),"Yes","",IF(AND(K1051="Partially Down",L1051<2),"Yes",""))

I have two columns of data. for example:

Column A

1

3

4

0

2

0

0

3

Column B

Product A

Product A

Product B

Product B

Product A

Product A

Product B

Product B

What I need the function to return is:

Product A - 3

Product B - 2

This is what I thought the function should look like but it is not working:

=IF(A6:A50>0,COUNTIF(A6:A50,"Product A"),0)

If Product A or B did not have any values >0, it would return a zero.

I am getting an error at this line:

Set rng5 = Range(.Cells(celle2.Row, celle4.Column), .Cells(celle2.Row, celle4.Column))

Sub UpdateNumbers()

Dim rng1 As Range

Dim rng2 As Range

Dim rng3 As Range

Dim rng4 As Range

Dim celle1 As Range

Dim celle2 As Range

Dim celle3 As Range

Dim celle4 As Range

Dim celle5 As Range

Dim flag1 As Long

Dim flag2 As Long................

Copyrights 2005-15 www.BigResource.com, All rights reserved