VBA Code. Check Multiple Conditions
Jul 22, 2007
I want to create a user form in excel that auto calcs the discount depending on customer type and no. of books purchased. There are two types of customer’s one individual and the other schools, library etc. The discounts are below
More than 50 books - 30%
25 - 49 - 20%
15 - 24 - 15%
5 - 14 - 10%
<5 - 5%
Individual
>25 - 25%
5 - 24 - 15%
<5 - 0%
I have created a form with option and text boxes to capture data but am really struggling with this as in my head I understand a set calculation will need to take place once relevant conditions are met but am really struggling to put it into code (yes I am new to this).
View 2 Replies
ADVERTISEMENT
Jul 14, 2009
I have the data in this form
YYY
NYY
NYY
NNY
NYY
NNY
NYY
NYY
NYY
NYY
YNN
NNY
NNN
NYY
The output should check two conditions. If all three columns are "Y" then it should return value "Y". If any two columns are "Y" then it should return value "Y". or else it should return "N". Then the output should look like this.... The output column is colored in Red..........
View 5 Replies
View Related
Oct 22, 2009
I want to vary a for next loop, depending on the value of a variable (y). Is there a way of doing this without repeating code as in the example?
If y = 1 Then
For x = 1 To 100
' lengthy code changes Cells(x,1) characteristics
Next x
ElseIf y = 2 Then
For x = 101 To 200
'lengthy code changes Cells(x,1) characteristics
Next x
ElseIf y = 3 Then
For x = 201 To 300
'lengthy code changes Cells(x,1) characteristics
Next x
End If
View 9 Replies
View Related
May 22, 2006
=AND($AE$2:$AE$10<=$F$2:$F$2000;$AE$2:$AE$10>=$E$2:$E$2000) With this I am checking for conditions to be true or false
Lets say I am checking AND(AE2 <= F2 ; AE2>=E2) this tells me if date in cell
AE2 is smaller or equal to Cell F2, but how can I set this fromula to check if all the cells from F2 to F1000 are equal to AE2 so If I write date into Cell AE2 it checks all dates from F2 to F1000 and check if condition is true or false
(and if I write date in cell AE3 it should check all the dates from F2:F1000, if date in AE3 is in this range)
View 9 Replies
View Related
Nov 8, 2006
i am trying to create what is probably a simple If statement for a worksheet formula i want to check to see if one of two cells have the text specified if so then the cell value is A4 from another sheet if not the cell containing the formula should be blank, something like below
=If(B1="on" Or D1="on",Sheet2!A4,"")
I also need this kind of formula with the AND operator, something like
=If(B1="off" And D1="off",Sheet2!A4,"")
View 5 Replies
View Related
Sep 30, 2007
I have a sheet here whereby if Bor No = 1, then total incurred should return as incurred. But if BOR No is more than 1, then the total incurred should sum up seq 1 and 2 of incurred. Bear in mind that seq no could be up to 4, 5, 6.. and it's not fix. But if BOR No = 0 or NULL, then total incurred should equal to "0"
View 5 Replies
View Related
Jun 22, 2008
=IF(H10=0,IF((R10-I10)>$AH$1,(R10-I10),0),IF((R10-N10)>$AH$1,(R10-N10),0))
I have a small issue with the formula above. Basically the sheet is used to input start and end time of shifts and create breaks and lunches accordingly. The formula above resides in column S. Basically it warns us if the end of the shift is over 4.00 hrs past the end of the last lunches. A shift can have no lunch, one lunch or two lunches. H10 would be the start of the first lunch, R10 is the end of shift, I10 is the end of the first lunch, AH1 = 4:00 or 4:00:00 AM, N10 is the end of the second lunch.
What happens is that if there is no lunch given in cells H, I, M, N or in this case H, I, N then I get a strange number. For instance, I put in 6:00 for the start time [E10] (actually we input military time and the result is displayed in AM or PM) and the end time as 8:00, then I get a result of 8:00 in the S column [titled +4hrs warning). 6:00 to 9:00 results in 9.00 hrs., etc, When we input a six hour shift, a lunch is automatically starts in H10 and ends in I10. Once this begins, the formula works correctly.
View 4 Replies
View Related
Nov 16, 2009
I have several rows with numbers that is either 0, a balance or a text like n/a. I need a formula that return true if all are 0 or contain n/a. If there is one balance shall it be false. Any suggestion? See example:
View 6 Replies
View Related
Oct 20, 2006
Excel file attached! I need a macro to do the following:
Start with cell F4 and read the increment value from cell C4. Then add this with F3 and display the result. Continue with the same increment until it reaches the value equal to cell B4. Then read the increment from cell C5 and do the same until value equals cell B5. Repeat the same step until it reach the value equals B7. I did manually in the column F4 to F28. Moreover, the cell increment will change according to the variable in A2. In this case it is 25 and got 25 values to fill the column R.
View 8 Replies
View Related
Aug 22, 2009
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.
Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)
What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.
Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D
Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
View 5 Replies
View Related
Oct 8, 2007
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View 11 Replies
View Related
Aug 21, 2009
1. I have 4 columns (let's say D,E,I,J);
D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J
Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00
534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)
for column E, and
=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
View 4 Replies
View Related
Oct 16, 2009
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
View 13 Replies
View Related
Nov 30, 2006
I have two sheets. Watchbill and Roster. The Roster contains a list of names that are randomly generated to the watchbill sheet into more than 60 seperate cells. My problem is the names are not being distributed fairly. If I had 20 names and 20 cells, it only uses say 12 of them and repeats 8 names, thus leaving 8 persons out of the generation completely. I did not know how to fix this so I used a system to identify when a name was repeating in the series of cells by displaying Repeat in the cell beneath it. I then had the names that were not placed on the watchbill (the other 8 not used) highlight themselves in blue on the roster sheet so I could manually insert them on a third sheet into a cell set to override the repeating name on the watchbill. This was my back door system to solve my repeating names problem. However this takes forever. I want some vba that will identify the cell with the "repeat" underneath it and automatically recalculate the cell until it doesn't repeat. I only want the cell recalculated, not the sheet. This code would have to do this for about 70 cells making sure no names repeat at all, unless there isn't enough personnel to fill the positions, then it would allow the names to repeat. Can that be done and what would the code be?
View 14 Replies
View Related
Feb 10, 2009
I've had a look through the forum and can't see an answer, so apologies if I'm duplicating something!
I have a spreadsheet where column O has five conditions:
1 PO in
2 High Probability
3 Medium Probability
4 Low Probability
Blank
I need to colour code the whole row based on the value in the cells; if there were four conditions, I could do this with conditional formatting, but with five (with white as the fifth condition), I need to look at VBA.
However, I'm having difficulty colouring the whole row; how do I do this?
e.g. what I need to do is:
If O2 = "1 PO in"; Select A2:T2; Colour green
In addition, I need to ensure that if there is no data in column D, but column O is "1 PO in", it is highlighted in a separate colour. I have a macro for that, but don't know whether in needs to be put into the code before or after the macro needed above...
View 9 Replies
View Related
Jun 12, 2014
I'm trying to create a condition that hides rows when certain cells are equal to zero (column D + E + F). Anything else, I would like those rows to remain visible. Ideally, I'd like for this to be applied with the use of a macro (button) and then also deactivated when not desired (possibly another button). I've seen some varieties of code for this but none have worked flawlessly yet. An example sheet I would like for this to work on is my "ADM" sheet", among others.
View 2 Replies
View Related
Jan 22, 2010
I have a userform with two check boxes and four textbox... and i have a command button... i want that when i check checkbox1, the value in textbox1 and textbox2 will appear on cell A1 and A2... and when i check checkbox2, the value in textbox3 and textbox4 will appear in cell A1 and cell A2.
View 9 Replies
View Related
Nov 29, 2008
My data looks somewhat like this :
X Y Z
DOU Quarter Sum
0 Q1 = sum of 1's in Q1
1 Q1 = sum of 1's in Q2
1 Q2 = sum of 1's in Q3
0 Q2 = sum of 1's in Q4
1 Q3
1 Q1
1 Q4
0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
View 9 Replies
View Related
Jun 10, 2014
Obviously there is the Remove Duplicates option but I want something a bit different plus this option won't be available as the workbook is on complete lockdown.
There will always only be 214 rows to check in column B and the data cannot be sorted or it will mess a lot of things up!
Just wondering if there's some code that can check for duplicate entries because for the workbook to work, each row must have a different entry.
When the user selects this sheet a message box pops up telling them they can't have two or more entries the same but I feel this isn't enough as there's nothing actually stopping them doing this.
View 2 Replies
View Related
Nov 2, 2011
I use a userform to make sales data entries into a database. The userform contains textboxes for:
Invoice No.Invoice DateGrossVatNet The Invoice No. is the first entry box on the userform and it is important that users enter the correct format (which is alphanumeric).
It is also important that a checking is carried out to ensure that the Invoice Number being entered has not already been used.
I use the following code to ensure that the numbers are entered in the desired format:
Code:
Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtInv.Value = vbNullString Then Exit Sub
If (Not UCase(Me.txtInv.Value) Like "ST###") And (Not UCase(Me.txtInv.Value) Like "ST####") Then
MsgBox "Non Valid Invoice Number.â€
Cancel = True
End If
End Sub
And this code is pasted lower down the VB editor window. And it seems to be working fine!
For the checking to ensure that the invoice number being typed into the textbox has not already been used, I use the following two codes combined together:
a) to check for invoice No.:
Code:
check for Invoice Number
If Trim(Me.txtInv.Value) = "" Then
Me.txtInv.SetFocus
MsgBox "Please Enter Invoice No."
Exit Sub
End If
b) And to it I add the following additional code for CHECKING that the no. does not already exist in the database (the invoice nos are located in Column A of the database):
Code:
Dim x
X= Application.Match(Me.txtInv.Value, Columns(1),0)
If Not IsError(x) Then
MsgBox Me.txtInv.Value & "Invoice Number Is Already Used"
Me.txtInv.SetFocus
Cancel = True
Exit Sub
End If
My problem with the second set of codes in (a) & (b) (for the Checking of already existing No.) is that the code allows the user to key in the Invoice No. and go through filling in the other textboxes. It is only after clicking the ‘Add’ button that a msgbox comes up to indicate that the ‘invoice no. is already used’.
I need a tweaking of the codes so that the moment an invoice no. that has already been used is typed into the textbox and the TAB button pressed, a msgbox will show up at that stage to indicate that the ‘Invoice no. is already used’. This will reduce time and avoid filling in the other textboxes and clicking the ‘Add’ button before the checking is done.
View 3 Replies
View Related
Apr 12, 2014
I need a vba code to run a macro when a specific word entered in a cell. i.e. when a word "Duplicate" entered in a cell O2, the macro run automatically.
View 1 Replies
View Related
Oct 27, 2009
I have some code that will Kill a DB if it already exists, but I want to check if it exists and warn the user before this happens.
I am not that familiar with ADO, so I was fumbling through the Help topics trying to learn about ADO type names, etc. b/c i thought I could use something like:
If TypeName(MyDB) = "ADODB" Then . . .
but even if that ran, the argument in parentheses would be a string and not the actual DB object, so I am at a loss.
View 9 Replies
View Related
Sep 9, 2006
I want Excel VBA to check for named worksheets. Based on the return, I will have VBA either (1) delete the named worksheets and replace them or (2) refresh the pivot tables on the named worksheet. how to make VBA check for the presence of the named worksheets. My efforts are below and comments at the end of the Sub detail what I want to have happen.
Sub EnterProgram()
Dim Current_P As String, New_P As String
Current_P = Range("data!C2")
If Current_P = "" Then
New_P = InputBox("Which program?")
Do While New_P = ""
Prog = InputBox("Try again... Which program?")
Loop
Range("data!C2") = New_P
ElseIf Current_P = New_P Then
If MsgBox("Use the current program (yes or no)?", vbYesNo) = vbYes Then
Range("data!C2") = New_P
End If........................................
View 2 Replies
View Related
Sep 13, 2006
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
View 3 Replies
View Related
Apr 2, 2007
i want to improve some of my application by testing if a folder is accessible to the user... i've got some code to check if a file is already is use which is useful... but before that we have folders in work that are only accessible to certain users for reporting and if the person doesn't have access to it within a macro it Errors out... i'd like some kind of macro to display a message box like windows does... Directory is not accessible, Access Violation... it has to check directly for the Accessible part and not just an error...
View 2 Replies
View Related
Aug 9, 2009
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
Range("C4").Select
End If
End Sub
I am looking to do something similar on excel 07 as follows:
Range("O16").Select
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
View 9 Replies
View Related
Jan 29, 2007
see attached workbook.
I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions.
1) The name is in the list
2) The code in the list is one from the codes tab.
I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
View 3 Replies
View Related
Apr 13, 2011
I have a file I created which works fine for me, on my machine at least but there are two issues I'm trying to sort out.
The first issue is I need to share the file with others who may not have the same access rights. I have something loading in the Workbook_Open event which I want to bypass for users who do not have access to a specific folder. So I was trying to check their access using Dir(path) = "" at the top level folder to see if any files are present (which there are). My assumption was that if it finds files it's safe to assume they have permission and it's ok to continue, otherwise they don't in which case I want abort the rest of the code. This seems to work fine for me but I tried testing with someone I know does not have access and it gave her a compile error.
Part of the code in macro utilizes FileSystemObject which seems to be the line where the code bombs on my coworkers machine. The FSO seems related to appears related to the Microsoft Scripting Runtime reference, but it's not enabled on my machine or any of the others. I vaguely remember having to do something on my machine awhile back for the life of me don't remember what it was.
The second issue is an MS Forms error: "Could not load some objects because they are not available on this machine." appears when opening the file on three of the machines I tested but it does not appear on my machine. I've tried looking at the references and could not find any differences between the machines. I pored through various postings online and it appears it might be worth re-registering the DLL/OCX files but we need admin rights to do that so I will need to open a request with our support team.
View 2 Replies
View Related
Mar 2, 2012
I'm trying to write some code that will check if specific files in a folder are present before continuing code.
I've looked on-line and found various code that checks if a single file is present whereas my requirement is slightly more demanding.
Some files might be present and others not. The code must check ALL necessary files are there then run main code. If even any single file from say 5 files is missing, code must exit.
I was thinking of using an array to store all files names and then use a "For Each XX In XX" to loop through the array and check each file is present?
This is what I have so far:
Sub XX()
Dim strPath As String, LinesDelete As String, Applepnfile As String, MpnStock As String
Dim myArray() As Variant, myElement As Variant
Dim myArray() As Variant, myElement As Variant
strPath = "Z:AppleApple Deliverys"
[Code] .........
View 5 Replies
View Related
Jan 14, 2008
I hv following data in the excel.
I hope some one can help me to create code to do the following :-
1) if have last purchase date , but no last issue date = New Product.
2) if no purchase date , also no last issue date = None stock issue.
3) if have last purchase date , also have last issue date = cal the date between and put the month value .
For example :-
if last purchase date = Oct 07
if last monment date = Nov 07
then the stock duration = 1
View 9 Replies
View Related
