OR Function / Command
Nov 8, 2009
I am trying to get the OR command or something that may work in a similiar fashion to work on this particular formula.
=IF(ROWS($G$2:G2)<=$Z$32,SMALL(IF(DEALLIST[Sales1]=$E$9,ROW(DEALLIST[Deal'#])-ROW('DEALS 1.0'!$B$1)),ROWS($G$2:G2)),"")
In this formula I want to be able to see if DEALLIST[Sales1], OR EALLIST[Sales2] is equal to $E$9, if they are then I want the formula to continue. I have tried this but it returns every row in my table starting from row 1 through 1000.
=IF(ROWS($G$2:G2)<=$Z$32,SMALL(IF(OR(DEALLIST[Sales1]=$E$9,DEALLIST[Sales2]=$E$9),ROW(DEALLIST[Deal'#])-ROW('DEALS 1.0'!$B$1)),ROWS($G$2:G2)),"")
View 4 Replies
ADVERTISEMENT
Apr 3, 2012
I can accomplish what I want with the "Spin Button", but I'd really like to use the command button instead...
Can the command button be used for 2 functions?
Right now, I have a Spin Button, that performs this function:
Private Sub SpinButton1_spinup()
Range("E11").Formula = "=c11"
[Code]...
Is there a way to have the Command Button perform the same function? Not very familiar with VBA...I'd even prefer to go with the "Check Box"...but frankly, I simply don't know enough about the command functions to make it work.
View 3 Replies
View Related
Jul 9, 2008
I've a command button on one worksheet (named "Front") that when clicked it "resets" a large number of cells back to "."
I've ALSO a command button on another worksheet (named "Back") that when clicked it also "resets" a large number of cells back to "."
Both are embedded on their own VBA tabs.
Both work exactly as needed.
No problem.
Is there a way I can move the coding from both to the "This Workbook" VBA tab, leave the one command button on the "Front" worksheet, and eliminate the one on the back?
I'm kinda guessing that this will require my rewording the
"(Range("C5").Formula = ".")"
(example line of code for the first cell it resets on the "front" tab) to something specifically referencing the "Front" worksheet, and the same for the ones on the "Back", but I could be wrong.
View 9 Replies
View Related
Oct 7, 2008
I am working on a simple employee list with names, phone numbers, employee ID numbers, etc. I have created a VBA form to make new entries or change existing entries. The form has 'next', 'previous', and 'close' command buttons that work well, but I would like to have my data sort by employee name after any of the buttons are clicked.
My data is in worksheet "Employees" with employee names in column A beginning on row 2. Here is what I have entered for the 'Next' command button:
View 5 Replies
View Related
Mar 17, 2014
Userform1 on the attached file has a "Calendar" button to display a calendar that allows the user to select a date and have it placed in the Date Text Box. It simply doesn't work unless you click on the userform area where the calendar should appear - but how would the user know that?
View 2 Replies
View Related
Apr 14, 2009
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 Related
Apr 19, 2014
I 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..
View 4 Replies
View Related
Jun 20, 2013
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.
View 2 Replies
View Related
Feb 17, 2010
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
View 9 Replies
View Related
Feb 3, 2010
How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons
e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.
View 2 Replies
View Related
Aug 31, 2007
I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?
Dim strButname As String
strButname ="But1"
Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?
This doesn't work, but gives you an idea of what I am trying to do.
Controls(strButname)_click
View 9 Replies
View Related
Jan 30, 2007
I have 2 worksheets. On the first worksheet, i enter a line of different information.
What i want the spreadsheet to do is copy and paste that info onto the 2nd worksheet - wil this require VB atall?
View 9 Replies
View Related
Apr 24, 2007
I have a large amount of data, of which i need to perfrom a command to show either true or false result.
H Column has a range of number from 1 through 260
L Column has different text entrys 'pen' pencil' 'tape' etc..
The aim of the formula is to show a true result
if the number in the H Column is larger than 30
if the text is equal to either pen or pencil
View 6 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
May 14, 2013
Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
View 9 Replies
View Related
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
Jul 29, 2008
I have searched this topic but I came up with nothing regarding my exact situation.
I have 5 buttons on sheet 1. I want them to all direct me to another sheet in the same workbook. However, some buttons will contain more than one sheet.
Example: Button 1 will direct me to 4 different sheets. Is this possible?
Also, When I open the workbook, I see many sheets that I am working from but I do not want them to show up when I open the workbook. The only way to see these sheets is to click on the command buttons at the top of sheet 1.
View 10 Replies
View Related
Oct 15, 2008
I'm looking to have a doc where people time stamp their start and stop times. I have found that Control(apple) - Shift -semi colon works. The problem i'm having is that I want people to be able to use a command icon in their toolbars for this task as opposed to people have to use the keyboard. I can use a button with a macro but this is not possible as our excel corrupts when there are too many macro's
View 4 Replies
View Related
Jan 30, 2009
I have just started playing around with events etc and i was trying to reomve the command bar using the code below in module 1.
View 4 Replies
View Related
Sep 22, 2009
The following code is not working like I thought it would. I've got 4 named cells on a worksheet, and if any of them are blank, I don't want the user to be able to print it. What am I missing? Is it something with my Cancel = True statement?
View 2 Replies
View Related
May 21, 2009
I have a sheet in which I have a sum then depending on this value I want the next cell, F4, to have one of four outputs
If the value in F3 is;
Less than 1 F4 should show 9
between 1 and 2 F3 should show 16
Between 2 and 3 F3 should show 25
Greater than 3 F3 should show 36.
I have used IF to get the correct result when F3 is less than one but cannot work out how to add the other three results.
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
Apr 24, 2007
Simple VBA What is the command to scroll to over "x" number of columns? and/or rows?
I just the active cell to move over 5 cells to the right for example?
View 9 Replies
View Related
Oct 9, 2007
whats the vba command to do nothing in an if statement
View 9 Replies
View Related
Jun 5, 2008
with this
With Worksheets("Sheet2")
Application.WorksheetFunction.Max(.Range(.Range("A65536").end(xlup),.Range("A65536").End(xlUp).Offset(-20)))
End With
I'm able to calculate the max each 20 cells. Right?
If yes, how can I put that value into B1 ?
View 9 Replies
View Related
Jul 17, 2008
I recorded this SQL query and got this and a Connection string, the query kind of works but i want to be able to edit more. Can someone tell me what does what in this huge string, i want to be able to edit the MatsViewerView.SpecType and other things like i can edit the lot number=2007312RC (very end of string). P.S. sorry about the formatting, i guess this is what excel does when it records a query onto a macro.
.CommandText = Array( _
"SELECT MatsViewerView.SpecType, MatsViewerView.LotNumber, MatsViewerView.WaferNumber, MatsViewerView.DieNumber, MatsViewerView.TestDate, PostAnalysis.Description, PostAnalysis.Achieved, WPGain1.WPGain" _
View 9 Replies
View Related
Oct 17, 2008
I am trying to add aright click menu item a cell.
I am using the following code in a module.
Sub addmenu()
Set nbar = Application.CommandBars("Cell")
Set ntrnew = nbar.Controls.Add(Type:=msoControlButton)
ntrnew.Caption = "Paste In"
ntrnew.Visible = True
ntrnew.BeginGroup = True
ntrnew.FaceId = 110
ntrnew.OnAction = "Test"
To trigger the first code when needed.
I know this all works when I put it in brand new workbook it works my custom menu appears and I can run the the "Test sub"
However as soon as I put this in the workbook where I want this to work it does not show the menu.
It is there though as when I step through the remove Sub I can see it matched and then deleted.
I thought at first it was protection but that is now removed but still no joy.
I can not see any setting that controls these sub menus.
View 9 Replies
View Related
Feb 11, 2009
i need to merge several columns together with a comma seperator. but there are some columns with only set of data in and sometimes 6 columns with data. so i end of with something looking like WC,,,,, or GAU,MAJ,CHEM,,, etc.
i have over 6000 lines so don't feel like cleaning up manually.
View 9 Replies
View Related
Feb 27, 2009
I need to find the row number of the first instance of the string 'TIMEACT
in column A. I know that it must exist at some point in column A, but I do not know where. Column A might have 100,000 entries... ie: a very large number, so I'd appreciate it if the code could be very general.
View 9 Replies
View Related
