If 1st Condition Met Don't Execute 2nd IF Statement
Nov 20, 2006
I'm having a problem with negative values. Somehow if the pitchX is -11.1877, let's say, and it is the right value, the code will still perform the 2nd if statement, when it should not. Am I using the wrong data type? Or is there anything I'm missing out?
Dim pitchX As Double
If pitchX > 10000 Then
pitchX = pitchX / 1000
End If
If pitchX <> Range("C8").Value Then
With Range("C8")
.Font.Bold = True
End With
End If
View 6 Replies
ADVERTISEMENT
Aug 27, 2012
1) I have a VBA macro MyMacro() which works fine.
The macro is assigned to a button on the w/s, and also runs from w/b Open() event and other locations within the w/b.
2) The macro displays a message just before exiting whenever it is called.
3) I’d like the MsgBox statement in the macro to be executed ONLY when the macro is run from the button.
In other words, skip the MsgBox statement when the macro is run from anywhere else in the VBAProject.
Can this be done ? possibly by inserting a statement or two before MsgBox in the MyMacro() code ?
View 4 Replies
View Related
Oct 28, 2009
Rows:
Each row contains a new client (a new entry)
Columns:Clients first and last name.
Case number
Case type - Case type can be AAA, BBB, CCC, DDD, EEE, (and many more).
Case outcome - Case outcome can be Granted (G) or Denied (D)
A case can be of type AAA, and be Granted.
A case can be of type MMM, and be Denied.
A case can be of type ZZZ, and be Denied.
Etc...
I am trying to keep track of how many cases of each type have been granted and denied. If you look at the file i attached, you will see that the table is a list of clients, with different case types and different outcomes to their cases.
The problem i am having is that i need a way to have a two-condition statement as such:
if case type is "AAA" and case outcome is "G", then add 1 to a "AAA GRANTED" column on another sheet.,....................
View 3 Replies
View Related
Jun 27, 2006
I have a problem with a SUMIF statement. I think it is =SUMIF(AND(...
I have attached I want to sum in column E lines 255-316 if in cell D 34 and if cell B25 equals GL and cell C255 equals 0001.
Maybe this would be handled better with a sumproduct formula...
View 3 Replies
View Related
Feb 28, 2014
I have the following code:
[Code] .......
What do I need to change in order to make it execute the Call statement on EVERY item in the ListBox2, not the Selections.
View 3 Replies
View Related
Nov 17, 2009
=IF(SUM(Q4)=0,"",MAX(0,G4-MAX(F4,Q4))/7) - to calculate number of days for year
=IF(SUM(TODAY()>G4),(G4-MAX(F4,Q4))/7,SUM(TODAY()-MAX(F4,Q4))/7) - to calculate number of days year to date
I am using this condition to calculate the length of a service which falls with in a specific review period, eg
Service starts 1 Feb 09 and finishes 30 April 09
The review period is 1 Mar 09 and finishes 31 May 09.
I would need to measure the number of days that fall within the review period, which would be 1 Mar 09 - 30 April 09
At the moment I am missing a condition which identifies whether to subtract from 'service end date' or 'review end date'.
View 6 Replies
View Related
Mar 26, 2009
I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.
What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".
Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".
If it is any day in between the two, it should display the term "Active"
I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.
=IF(D7=0,"Active", IF((TODAY()-40)-D7>0,"Verify","Active"))
=IF(D8=0,"Active", IF((TODAY()+365)-D8<0,"Future","Active"))
(D# corresponds to the respective cell that contains the scheduled ISD)
View 13 Replies
View Related
Sep 25, 2013
I have simplified my problem to its basic parts:
Column A Contains Customer Numbers,
Column B Contains a Balance.
Customers can appear more than once, but the data is sorted by customer number.
In column C, I need to put a 1 when the balance reaches 0 or less, but only for the first instance. Everything else should be 0.
here is how i'd want it to look:
customer
Balance
1st
View 9 Replies
View Related
May 6, 2014
I am running a macro which filters a column to show only records with "#N/A" (ie an error), and copies these to another worksheet. As I am aiming for no errors, there will be occasions when there is no filtered data.
My problem is that, unless I use something like xlCellTypeVisible, when there is no data shown it still copies and pastes all the hidden records. And if I do use it when no filtered data, I currently get an error "No cells were found".
I have tried many iterations of code correction and i think i'm currently blinded by staring at it so long!
My current if statement reads as follows:
[Code] .....
I think I might need to use some sort of On Error Resume Next statement, but I have never used these before. When I tried adding that before the If statement, it just ignored the criteria and tried copying hidden rows - the exact opposite of what I want.
I'd like something that said, if this condition = error, skip over entire if statement and carry on with rest of the sub.
View 5 Replies
View Related
Oct 6, 2011
I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!$F$2:$F$34" contains my list of names and the formula returns how many occurences there are in each cell.
What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.
So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!$F$2:$F$34", it then compares the associated date in "References!$G$2:$G$34" with the date associated with N3 which happens to be O3.
SUMPRODUCT(COUNTIF(N3,"*"&References!$F$2:$F$34&"*"))
View 5 Replies
View Related
Apr 10, 2012
I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.
For Example, if "T" is what I am looking for then :
EDT4 will be true and give me the value accordingly
D10T1 Will also be true and return me the value
D10B2 Will not be true
I know how to use the Mid() and right() function, but I need to check the 2nd from the right.
View 2 Replies
View Related
Feb 22, 2009
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
View 10 Replies
View Related
Mar 24, 2014
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
View 2 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Apr 17, 2009
I have below as part of my
Dim ToPath As String
ToPath = "C:Documents and Settings" & Environ("UserName") & "Desktop"
With Application.FileSearch
.LookIn = ToPath
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Sheets("Sheet2").Range("A1:A65536").ClearContents
For i = 1 To .FoundFiles.Count
tempbuf = .FoundFiles(i)
tempbuf = Mid(tempbuf, InStrRev(tempbuf, "") + 1, 255)
Sheets("Sheet2").Range("A1").Offset(i, 0).Value = tempbuf
Next i
Else
Exit Sub
it exits the sub which apparently means .Execute is not > to 0.
So my question is, what does the line If .Execute() > 0 checks?
I am 101% sure that there are pdf files under the ToPath folder.
View 9 Replies
View Related
Feb 13, 2007
I'm using code from the Ozgrid page: http://www.ozgrid.com/VBA/loop-through.htm
However, once I get to the if statement If .Execute > 0... it does not contain a value. The folder contains 16 files in it, but .Execute does not recognize any of them. Is there a specific library or call that I'm missing??
I've been battling this for sooo long, I just dont know what to do with it.
View 9 Replies
View Related
Dec 27, 2008
Using Excel 2003 with WinXP. I'm trying to run macro code automatically whenever time = 9:30:01 (or whatever time I pick) for a stock market trading program, which is why the exact time matters so much. I've been able to get the time to update fine, but unless I click on the worksheet while the time condition is TRUE then my code doesn't run. The time actually is sent to me from the stock data provider and it shows up in a cell as a constantly updating value.
I've tried using the Workbook_SheetChange function and OnTime method, but without luck.
In both cases, unless I activate the sheet the code doesn't run. By activate, I mean that I have to click on the sheet when the values that trigger the code to run would be true. If I do that it works just fine, but sitting around and clicking on a worksheet defeats the purpose of automation. Since I'm trading stocks this has to be very exact, so I can't trust a macro scheduler to do this.
The code further below is what I'm trying to get to run. The time value is in cell L1. In cell L2 I have the following
View 9 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Feb 24, 2014
Attachment 299651
I have turned a Workbook with Macros into an add-in. The macros work perfectly for the intended purpose: copy selected data in a special way and put it into an e-Mail message.
I used the CustomUI Editor to assign a macro to a Ribbon button.
However, when I turn it into an add-in and click the Ribbon button, it doesn't work on my machine or any other machine, with an error that says "Cannot run the macro 'CreateListofInventory'. The macro may not be available in this workbork or all macros may be disabled.
My macros are not disabled, and the add-in doesn't even work in the book where the macros reside.
View 2 Replies
View Related
Sep 25, 2009
I have two userform one if useing the getopenfilename to popluate a listbox. and then i use these listbox item withen this code to copy the files into a new folder. This works ok.
Then i copy a program into the same folder jpegresize.exe this program makes resized images of all files in its directory. by defult this program should only deal with these files. though what is happening is instead of using the files in the new folder it running on the files where the originals where copied from. (hope that makes sence). Though if i run jpegresize.exe from the new folder or command prompt it. It runs like it should. So i cant figure it out is there somethink the the shell and wait is passing that it shouldnt be.
View 3 Replies
View Related
Oct 15, 2009
I would like to execute a sub when the workbook is saved, what is the procedure for this.
View 3 Replies
View Related
Nov 6, 2009
How do I have a workbook execute VBA code when I hit 'Enter' anywhere on a specific sheet? I don't need the code to execute when I hit 'Enter' on any of the other sheets in the workbook, just a specific sheet.
View 11 Replies
View Related
Jan 13, 2010
I coudn't find anything on this forum on this subject.
Is there a way to execute [trigger] a macro from within a formula?
e.g. Based on an IF statement result, execute macro1 if true or macro 2 if false?
modytrane
View 7 Replies
View Related
Feb 3, 2003
I need a macro that will execute the F2 function key so that the cell can be edited.
View 7 Replies
View Related
Nov 19, 2011
I have this very simple code below that I use to delete a row if its marked as 'false' in column M. This works quite well, but I want to expand it. I use this in a workbook that can have name different sheet names in a month, and I want it to automatically go through all the sheets and do this...except for 2 sheets named addressess and sheet1. Is there something I can add to this macro that will loop through all the other sheet names (regardless of name) and execute this?
Sub DelRow()
With ActiveSheet
.AutoFilterMode = False
With Range("m1", Range("m" & Rows.Count).End(xlUp))
.AutoFilter 1, "false"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
View 2 Replies
View Related
Jan 4, 2008
Assume a cell -- say A1 -- has a long formula in it. If I select A1, press F2 to "Enter" the cell, then I have a blinking 'l' indicating the cursor/pointer position WITHIN the cell.
Is there any way to control the position of the blinking 'l' (or whatever it is called)? For instance, in a long formula, I would like to write a macro which could transport the blinking 'l' to midway into the formula string.
Alternatively, can I make a partial selection WITHIN a cell and run a macro on it. For instance, if cell B1 has a heading "Dec 2007" and I highlight just the "Dec" portion of the string, I would like to execute a macro to color it red. I have a simple macro that can do it to the entire cell, but not to only part of the cell contents. Is there any way for VBA to be active when I am WITHIN a cell.
Sub Font_Red()
'will add red color to ActiveCell font
Selection.Font.ColorIndex = 3
End Sub
View 9 Replies
View Related
Jun 17, 2008
I tried the following code but it shows an error:
Sub test2col()
Sheets("Sheet6").Cells.ClearContents
Sheets("Sheet6").Range("A:B").Value = Sheets("Sheet1").Range("A:B").Value
Sheets("Sheet6").Range("D").Value = Sheets("Sheet1").Range("C").Value
End Sub
It said
Sheets("Sheet6").Range("D").Value = Sheets("Sheet1").Range("C").Value
End Sub
has error.
Does a Sub not support to use Range more than one time? If so, do I need to write many Sub to do it?
View 11 Replies
View Related
May 18, 2009
How do I let a macro execute a certain key strok combination, such as Alt, D, G, H?
(Didn't really know what to search for...)
View 9 Replies
View Related
Jun 12, 2006
When the next empty cell in column A is selected, add a formula in the same row, column H. I'm not sure on how to hard code this and used the offset instead. So maybe instead of offsetting, i should/could change the references? However, as it is, it also adds the formla in column H when column G is selected and data is entered. I cant recreate this with any other column though.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A5:A65000")) Is Nothing Then
If ActiveCell.Offset(-1, 0) = " r " Then
If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Formula = "=if(RC[-1]="""","""",RC[-1]+120)"
Exit Sub
End If
End If
If ActiveCell.Offset(-1, 0) = "" Then
MsgBox "Please select the next empty cell/row!"
Exit Sub
End If
If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Formula = "=if(RC[-1]="""","""",RC[-1]+120)"
End If
End If
End Sub
View 2 Replies
View Related
Sep 26, 2006
I need to create a batchfile from data retrieved from an excel spreadsheet.
1. I have 30 pre-select users on a list with their phone numbers and want to create checkboxes on each user to be selected.
2. Selected users will then be included in a batchfile to execute but only the corresponding phone numbers will be used.
3. Probably need to use a form for it to be user-friendly...i guess.
e.g. batchfile.bat
@ECHO OFF
::Location of the executable file:example.exe
d:
::CLI of utility
example 32165487 -tsve -g<ip> <msg>
example 45679821 -tsve -g<ip> <msg>
example 16543248 -tsve -g<ip> <msg>
View 9 Replies
View Related
