UDF, For A Long If Stmt
Aug 10, 2008
I need to build a udf that simplifies a 10 nested if statement . i could achieve my task by using a if and statement, but it would exceed the 7 nested limit.
I have company names in column A, industry code in column b and four varibles in column C,D,E,F . The g column would be a weighted average of columns C,D,E,F. The weighted average score would have the logic of:
If the industry code is equal to "TE", then (C2*.7)+(D2*.1)+(E2*0)+(E2*0)
if the industry code is equal to "EN", then (C2*.3)+(D2*.3)+(E2*.3)+(E2*.1)
if the industry code is equal to "SF", then (C2*.4)+(D2*.6)+(E2*0)+(E2*0)
if the industry code is equal to "H", then (C2*.1)+(D2*.1)+(E2*.1)+(E2*.7)
if the industry code is equal to "MM", then (C2*.1)+(D2*.2)+(E2*.3)+(E2*.4)
if the industry code is equal to "EE", then (C2*0)+(D2*.0)+(E2*.8)+(E2*.1)
if the industry code is equal to "ENG", then (C2*0)+(D2*0)+(E2*.5)+(E2*.5)
if the industry code is equal to "ETT", then (C2*0)+(D2*.2)+(E2*.3)+(E2*.5)
if the industry code is equal to "HH", then (C2*0)+(D2*.9)+(E2*.1)+(E2*0)
if the industry code is equal to "HHW", then (C2*.7)+(D2*.3)+(E2*0)+(E2*0)
View 9 Replies
ADVERTISEMENT
Apr 14, 2007
how to make the word pink in the following
enter the GL account in question in the pink box
But the same is not possible for the word green in the following.
=IF(D3="Check to see if this is a vendor restricted from VULCAN", "Enter vendor number in the green box WITHOUT leading zero", "")
Is there a way to make green green?
View 3 Replies
View Related
Apr 8, 2009
I've attached the workbook to make this easier. If you look at the file master.xls you will see 2 tabs. On the Tool tab I've highlighted some blue cells and some rose cells. If you look at the blue cell G68 there is a long formula that reads up to 3 cells from the Specs tab and then plugs in the correct data from the blue cells on the right side of the Tools tab page. I need the rose cell F69 to do the same thing only with the data from the rose cells to the right. When I try and duplicate the fromula from G68 into F69 I get errors. I hope I made this clear enough. The formula in G68 is long, is there a way to shorten it? Also sometimes in this workbook when I try to type a formula in a cell I get the text I typed instead of the formula. For example I might enter in A1, =B2. Instead of getting the data from B2, it reads =B2. I've looked at how the cell is formatted but can't get it to work.
View 3 Replies
View Related
Jul 3, 2013
I am using the sum function to sum the following cells and the formula is too long. How can I make this work?
'Raw Data'!B3,'Raw Data'!D3,'Raw Data'!F3,'Raw Data'!H3,'Raw Data'!J3,'Raw Data'!L3,'Raw Data'!N3,'Raw Data'!P3,'Raw Data'!R3,'Raw Data'!T3,'Raw Data'!V3,'Raw Data'!X3,'Raw Data'!Z3,'Raw Data'!AB3,'Raw Data'!AD3,'Raw Data'!AF3,'Raw Data'!AH3, 'Raw Data'!AJ3, 'Raw Data'!AL3, 'Raw Data'!AN3, 'Raw Data'!AP3, 'Raw Data'!AR3, 'Raw Data'!AT3, 'Raw Data'!AV3, 'Raw Data'!AX3, 'Raw Data'!AZ3, 'Raw Data'!BB3, 'Raw Data'!BD3, 'Raw Data'!BF3, 'Raw Data'!BH3, 'Raw Data'!BJ3, 'Raw Data'!BL3, 'Raw Data'!BN3, 'Raw Data'!BP3, 'Raw Data'!BR3, 'Raw Data'!BT3, 'Raw Data'!BV3, 'Raw Data'!BX3, 'Raw Data'!BZ3, 'Raw Data'!CB3, 'Raw Data'!CD3, 'Raw Data'!CF3, 'Raw Data'!CH3, 'Raw Data'!CJ3, 'Raw Data'!CL3, 'Raw Data'!CN3, 'Raw Data'!CP3, 'Raw Data'!CR3, 'Raw Data'!CT3, 'Raw Data'!CV3, 'Raw Data'!CX3, 'Raw Data'!CZ3,
View 7 Replies
View Related
Jun 17, 2014
In my Personal Macro book, I have 2 modules, one for Functions and one for "macros".
Is there a point where it's more efficient to make a new module (or is the opposite true and it's more efficient if they are all in one)?
Does having a plethora of sub routines in a module slow down excel even though the routines are short? or would having them in different modules slow it down more?
View 4 Replies
View Related
Jun 2, 2009
I know that a variable of string type is equal to "" before it's assigned a value. So I reset the value of strings to empty (i.e. "") when I want them clear of a value. I also know that variables of Variant Type are equal to NULL when they are unassigned so I reset them to NULL when I want to clear them of a value.
But what about a Long? When you declare a variable of Long Type or Int Type or Double Type, what is it's inital value when empty and unassigned. I assume it's not 0 since 0 is an actual number.
I want to reset my lng variable and clear it of a value. So I'm wondering what should I set to... How can i do this?
View 3 Replies
View Related
Dec 20, 2006
1. if I have a custom command Bar that is very long and I want it displayed in two rows how do I code it to start a new row at a certain point (button)?
- can I control the length of it?
2. How do I make the Formatting toolbar inactive on opening a particular workbook?
View 9 Replies
View Related
Feb 8, 2010
im not really sure what div/0's are na dhow to counter them but i understand from the error that im dividing by something that is not valid yet.
below is my formula, among others like this. i need some assistance in getting the ISERROR code in as i am struggling; ....
View 9 Replies
View Related
Mar 4, 2014
I'm working on a spreadsheet that i have a long if statement and it keeps till me i have a error. I stated reading and come to find out you can only have 7 statement.
=IF($R$6="X",VLOOKUP($A25,VISION,2,FALSE),IF($R$7="X",VLOOKUP($A25,VISION,3,FALSE),IF($R$9="X",[code]....
View 14 Replies
View Related
Nov 13, 2008
how can in compress the following formula
=IF(B3="Monthly",IF(B25="Windows 2003 Std",15,IF(B25="Windows 2003 Ent",30,IF(B25="Windows 2008 Data Center ",40,IF(B25="RHEL 5","30",IF(B25="Windows 2008 Web","15",0))))))+(IF(B3="Quarterly",IF(B25="Windows 2003 Std",40,IF(B25="Windows 2003 Ent",75,IF(B25="RHEL 5","89",IF(B25="Windows 2008 Data Center ",120,IF(B25="Windows 2008 Web","45",0))))))+(IF(B3="Semi-Annually",IF(B25="Windows 2003 Std",75,IF(B25="Windows 2003 Ent",140,IF(B25="Windows 2008 Data Center ",200,IF(B25="RHEL 5","149",IF(B25="Windows 2008 Web","90",0)))))))+(IF(B3="Annually",IF(B25="Windows 2003 Std",150,IF(B25="Windows 2003 Ent",300,IF(B25="RHEL 5","299",IF(B25="Windows 2008 Data Center ",400,IF(B25="Windows 2008 Web","180",0))))))))
View 5 Replies
View Related
Oct 31, 2009
I need to add more arguments to a countif formula, but I have maxed out the 1024 character limit.
Is there another way of writing this formula or is there a way I can add more arguments to this formula.
Formula is:
=countif(A2:A9999,"AH")+countif(A2:A9999,"ARHC")+countif...
I have a list of the arguments, can I do something like:
=countif(A2:A9999,B2:B50)
(with the list of arguments in columns B2 to B50)
View 13 Replies
View Related
Jan 5, 2010
I've attached an example of what I am trying to achieve and an explanation as well. I'm trying to avoid the use of a macro as I don't understand them and I will need to modify the sheet later on which could cause problems. However, if a macro is the only solution, then I will have to make due with one.
View 5 Replies
View Related
Jul 22, 2013
I am trying to sort a fairly long list of tasks into a custom calendar order. I have created the custom list as Sept, Oct, Nov, Dec, Jan etc through to Aug, with one additional heading of "Recurring". The list won't seem to sort by the column where these headings occur. See attachment.
View 7 Replies
View Related
May 9, 2014
I have a UserForm where the user can enter figures for 143 different items which they select via a combobox drop down.
When I tested it, I hit the submit button but it says the procedure is too large!
Here is the code which is repeated 143 times but with a different row number and range each time (in red):
[Code] .......
So the next bit of code would be exactly the same except the range would be C5 and the row would be 18.
The range is always going to increase nice and easily: C5 up to C146.
The row number is a bit hit and miss.
Is there a way of significantly decreasing the size of this or will I have the break the code down into different UserForms?
View 14 Replies
View Related
Jan 27, 2009
I noticed that sometimes if I stop a macro midway (completely stop, not just pause), and then re-run it from the beginning, my variables will still hold the value they were last set to right before i stopped the macro. But sometimes, this doesn't happen - the variables reset.
I'm wondering what defines how long the variable's value are held for? I'm considering writing a macro that can hold variables so that even after the macro is finished, the user can re-run and the variables will still be available. This is useful mostly for text boxes in userforms that will probably have the same values the next time the user runs, but the user can still change them if he wants.
View 3 Replies
View Related
Feb 23, 2009
Take a look at the sample sheet. I get the information as an import that looks like the info in column A.
I want it to look like the data in Column B. If I try to format as a number it always turns the last number to a 0. I also dont want the 0's on front of the digits either.
I dont really care how its done. But I would like a routine to turn column A into and like the example in Column B. No zeros on the front and maintaining all numbers.
View 9 Replies
View Related
Jul 22, 2009
what the long expression for the range being summed below.
Reason: I need to exclude some cell values.
View 11 Replies
View Related
Sep 29, 2009
If there is a different way, like to select the value in the selected range and cross reference it against a list. i'm not sure... trying to learn and be efficient.
View 3 Replies
View Related
Aug 26, 2008
I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty.
{=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A)))}
But it takes sometimes up to 4-5 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?
View 3 Replies
View Related
Jun 30, 2013
The function "VAL" will return the value of a numerical string as a double. Is it possible to get a similar function that will return the value as a long.
View 5 Replies
View Related
Jul 4, 2007
with printing a long userform? Unfortunately, the me.printform does not print out the whole form. It gets cut off because my form is too long and slightly too wide. I also tried to do a print screen, but unfortunately, because my form is too long, it will only do a snapshot of a portion of the form. In my case, on the bottom as that is where I have placed my command button.
Does anyone know how to bring the whole form out to excel or word? I don't mind doing that as long as I can print out the whole form?
View 9 Replies
View Related
Feb 26, 2009
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.
The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:
Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row
For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else
Cells(Rownum, 11).EntireRow.Delete shift:=xlUp
Rows = Rows - 1
NxtRownum:
Next Rownum
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
May 23, 2006
Got a formula that goes like this: =IF((L1=1),"Product 1",IF((L2=2),"Product 2",IF((L2=3),"Product 3",IF((L2=4),"Product 4".... and so on for a further 100 or so products. Obviously, this formula is too long for one cell.
View 2 Replies
View Related
Aug 21, 2006
I have a large formula written in my VBA code (a sumproduct with numerous variables) which exceeds the line length in VBA and therefore a proportion of the code is shown on the next line.
However, the VBA treats this second line as an error (since it sees it as a new line of code which on its own doesnt work) What do I need to do so that it treats the two lines as a single line of code?
View 7 Replies
View Related
Oct 17, 2006
I have a cell with a number which is a result of several previous sums. It's a number with 8 to 11 digits. For example: 878567663.
I need to add each digit of the number, and then add again the result until I end up with a single digit. In the above example it'd be (8+7+8+5+6+7+6+6+3=56), then (5+6=11), then (1+1=2) and put the end result (2) in a cell
My main doubt is that I don't know how to use a simple "do while" type structure in Excel to achieve the above.
Questions:
1.) How do I create a formula to do that?. I have seen solutions in this board that let me add each digit but that's it... I need to "reduce" it until only one digit is left...
2.) What about if the lenght of the number is not always the same (fe. in one cell is 8, in another is 11, the next one is again 8...)?
View 6 Replies
View Related
Mar 30, 2013
I have a spreadsheet of barcodes 114,618 rows long. I have a program that requires me to enter in each and every barcode in a certain format. This is the format that it requires me to place the barcodes into.
23423432, 23432342, 234324323, 234322344, 432432432, .............
so and and so forth.
Only problem is my POS system exports the barcodes in a format that looks like this:
545554545435
345435435435
354543534534
354534545453
Now I have a Macro to add a comma to the end of these barcodes. That part was a breeze, however, I have hit a brick wall when it comes to placing them into an ongoing sentence type format, that my program needs. I have found several ways to combine and merge and "justify" the content, however these typically only cover small amounts of content, and doesn't even come close to hitting anything over 100,000. I don't know if maybe I need to be using another program in tandem with Excel.
View 3 Replies
View Related
Jun 25, 2014
I'm trying to run a countif formula in a separate worksheet searching for a long text string. The text string is long enough that I can't copy the entire string into the Ctrl F Find function.
=COUNTIF('Data Month 1'!C:C,F4)
F4 = text string put together using the values of multiple adjascent cells. Total characters are over 26.
View 12 Replies
View Related
Jan 4, 2014
My data is set up as
A
B
C
1
A
B
C
[Code]....
How Can I make that data appear as
A
1
A
2
B
[Code]...
Where is takes the row and keeps transposing it down 3 lines at a time?
View 10 Replies
View Related
Mar 13, 2014
In a long string of characters and values, for example in B2:
Cos PGA 3.00 8.98 0.75 Cis TAK 1.00 2.99 0.25 Gsr PAM 14.00 41.92 0.82 Art GUN 3.00 8.98 0.18 Tlu NRG 9.00 26.95 0.33
I want to extract the second value after the specific string which is in in column headers C1, D1,E1...:
Art GUNCis TAKAxa MLPGsr PAMPen NMECos PGATlu NRGRot KGR
so for the example the result should be 8.98 in cell C2; 2.99 in cell D2; no value in E2; 41.92 in F2...
I attached a sheet with the table.
View 14 Replies
View Related
Mar 6, 2009
I need to associate a fairly long (800 characters) string with a workbook as a template SQL query for an ODBC connection. I've thought about putting it into a cell, into a shape, into a standalone VBA module (with some 'magic' to stop it compiling). All I need is to be able to access it fairly easily via VBA (which I'm comfortable with).
View 3 Replies
View Related