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?
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies View RelatedI am playing around with the Forfiles command (being called from Excel via Shell)..
I can't for the life of me get it apply a second command (such as getting the file size of each file)..
For example:
VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d
[Code]....
The end result being the filename and the filesize shown..
Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?
On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand
Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...
AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.
Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.
I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack
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 RelatedI 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,
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?
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?
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)
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; ....
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]....
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))))))))
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)
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 RelatedI 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 RelatedI 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?
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.
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.
what the long expression for the range being summed below.
Reason: I need to exclude some cell values.
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 RelatedI 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?
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 Relatedwith 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?
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
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 RelatedI 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?
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...)?
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.
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.