I have an enquiry where I'm trying to order error messages which occurs in if, ElseIf and Else Statements. The code I have works. However, I would like the error messages to be checked in a particular order. I tried rearranging the conditional statements but had no success. Here is my
View 5 RepliesMy code is below. See how I have repeated the same code over and over for each row. Is there a shorter way to write this, perhaps a cleaner way. Im not an expert and I write what I know and most of that is long code that can be shortened.
If MyScreen.Area(7, 2, 7, 4) = "102" Then
Workbooks(2).Worksheets(1). Range("B" & row) = "Yes"
ElseIf MyScreen.Area(8, 2, 8, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(9, 2, 9, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(10, 2, 10, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(11, 2, 11, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(12, 2, 12, 4) = "102" Then .....................
develop a macro to send e-mails upon certain criteria, I then wanted to add a layer to the macro which I have, and it works... sort of. I'm wanting the Macro to send a specific e-mail if the dates fall between a certain range, a different e-mail if they fall between another range, etc... Here is the code I came up with:
Private Sub Workbook_Open()
Dim LR As Long, i As Long, s As String, sb As String, sc As String, eml As String
With Sheets("Vendor-Rltshp")
I started changing it and then realized that I can keep going by adding new elseif's but i think an array would be a better option(i think). But i am really a monkey see monkey do kind of vba user. i can backwards engineer but cant come up with it.
here is code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$z:$z")) Is Nothing Then
If Target.Value = "NO CONTACT" Then
Target.Offset(0, 1) = Date + 1
ElseIf Target.Value = "NO CNTC FCL" Then
Target.Offset(0, 1) = Date + 1
ElseIf Target.Value = "NO RES" Then
Target.Offset(0, 1) = Date + 1
you see i keep adding "results" and assigning lift dates. what i would idealy want to do is take my list (that i use in a drop down box in $z) and next to it assign the number of days i want to set the next call date(the offset 1 cell) to be. then have the code just read cells a1:a10(the results) and assign the corresponding b1:b2(the amount of days to wait to call). I can keep adding elseif's but then if i decide to add new results codes or change the days from 1 to 5 i have to go back to the code.
In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,
Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following
Cells(J6)*1.04)*2080/12
If False then
ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following
Cells(J6)*43.33)+((J6*1.04)*129.99
If False then
ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following
Cells(J6)*86.66)+((J6*1.04)*86.66
If False then
ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following
Cells(J6)*129.99)+((J6*1.04)*43.33
If False then
ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following
Cells(J6)*1.04)*2080/12, but put this into column P
If row 6 Column O if the month is greater then 1 then I need the formula to do the following,
=J6*2080/12
What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.
In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.
I have tried to shorten my code by combining common tasks, however it runs but selects the wrong data. In the example below,
The code returns the expected if I select "M135X" and "300 hrs", but unexpected results if "M135X" and "1500 hrs" is selected
My variables are models and hours - the code below is for one model.
Code:
'Next Model
ElseIf Range("D3") = "M135X" And Range("E3") = "1st Service" Then
ThisWorkbook.Worksheets("M100X-135X").Range("B37:D44").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
[Code] .........
I have created a user form with a combo box and three text boxes. The drop down box is populated via the initialization of the form; code is posted below.
the "add item" names are names that are already on the worksheet in column 1. What i have done is used the command button "submit" to populate the information from the textboxes to the worksheet.
what i would ike to do is populate the empty cells below this area with the information that is in all three text boxes and the combo box. for the IF / elseif statments i have used the combo box as the condition. So the quandry is I cant input the information into the empty cells, i have tried a few things, my vba is very basic, as you can tell ihavent even DIM'd anything (i think that is another thread though :D )
here is the initialize code and below is the "submit button" code.
Private Sub UserForm_Initialize()
'sets values for text boxes
cbomaterialdiscription.Value = ""..........
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
I have about 45 worksheets in one file. Then all have been named. Is there any quick way to order them? I would prefer not to move each one.
View 2 Replies View Relatedwhat i want to do is have a formula set up that works out who has the best position and then re order the number rows to put the player in position 1 at the top. so just moving cells c-n
View 6 Replies View RelatedI need to ensure that a worksheet is ordered after another in a workbook. The macro that this code is in opens a selected file and carries out various operatins on it, so although I can do this fine when the worksheet needs to be created, there are scenarios where it already exists and I simply need to change the order. The sheet that must come in position 1 does not have a common name and so I cannot reference it via it's name in the code. The other sheet is named when it is created so it doesn't necesarilly have to be in position 2, it's just that the other one needs to be in position 1.
View 7 Replies View RelatedA number of columns (B to U) contain names, details and scores of players. The A column contains the ranking, e.g. 1 to 100.
If a player (row) is deleted, that ranking number is also gone. Is there a way to make the A column always maintain the integrity and completeness of the numbering, e.g. if rank (row) 16 is deleted, the range of B17:U100 moves one row up and the former 17 is now 16? And if a new row is inserted, the ranking numbers after the insertion move up one row and a new number is added at the end?
2. There are 6 columns of scores, P to U. But out of these 6 scores, only the best 4 count for the ranking. The two worst (highest) scores are dropped. Is there a way to automate the process by selecting only the 4 best (lowest) numbers to be summed into the Total column (C), which in turn determines the rankings in the A column?*
And then can the whole range B2:U100 automatically be sorted according to the ranking in column (A)?
*It is possible that there are 3 or 4 bad scores, all with the same value. In that case only 2 could be discarded, and 1 or 2 would be summed into the ranking cell.
How do you do a lookup without having to have a list in ascending order?
i have a 5 by 10 table (5 columns 10 rows)
values only equal 1 or blank
i want to re-order this table in another identical table so that no row will have more than one value (each row will have a maximum of one "1")
currently, the table looks something like this:
blank blank blank 1 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank
blank blank blank blank blank
what i would like the idential table to show is:
blank blank blank 1 blank
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank
I have 320 rows of data, of which the date is in the first column. I need the data sorted into date order, ie. 04.05.12-->30.04.13 and all the dates in-between (note these are British laid out dates). I am aware, and have already done Format Cells --> Date --> 14.03.01, as this is how my dates are laid out.
I've tried sorting this column, ascending, descending etc etc etc, obviously it needs to be 'expanded' to ensure that the rest of the data in the row follows it to its new position. No joy. It only ever sorts the dates by the day ie. 02.04.13, 04.12.12, 06.06.12. Doing it by the day and not the month and the year also.
I don't want to order 320 data rows myself!
I want to have them in order of shortest price (favourite) first with the relevant horse's name in the adjacent column. I have just discovered vlookup which seemed perfect - until I discovered that when there are two or more identical prices it only returns the 1st horses name.
Is there any way I can use vlookup to do this (with more than one identical value)
I am trying to come up with a formula which would combine two groups of numbers, put them in numberial order and remove duplicate numbers:
3 6 10 22
1 3 5 9 10 13 19
The above two groups of numbers are in separate cells in two different rows.
the result would also be in a different row: 1 3 5 6 9 10 13 19 22
I need to make a spreadsheet on excel which shows the production schedule of a product over 4 days.
Then I need to make a supply ordering sheet which shows how much supplies are needed to do the above task and how much each item in the supply list will cost and when the items would need to be ordered.
setting up a stock ordering system in Excel. I have been sent a spreadsheet with about 400 items I've ordered before and about 15 columns of descriptions which include the name, category of item, manufacturer, cost, number etc.
I would like to set up a simple Excel spreadsheet which staff can use when they want to order some stock, which will automatically fill in the adjacent cells with the product category and unit cost, once the name has been typed into the first cell. Once I add the SUM formula I can therefore have an immediate total of how much each staff member is spending on stock that week, without having to look at the invoice I get back from the supplier.
I know Excel has a predictive text style feature (autocomplete?) but I don't know how to get it to automatically fill several cells in a row with data that is linked to the first cell.
I would like to reorder a list whilst ignoring any 0 values. I've attached (I hope correctly!) a spreadsheet of what I mean. It's pretty simple, but I've been banging my head over it for a while now!
Sample.xlsx
I have the following macro:-
ActiveSheet.Select
ActiveSheet.Copy
Columns("A:D").EntireColumn.AutoFit
Range("A2:B500").Select
Selection.Sort Key1:=Range("B2:B500"), order1:=xlAscending, Key2:=Range("A6:A250"), order2:=xlAscending
Within this I would like to add a couple of features which check:-
1) If there is a number in column A - there should be one in column B
2) The numbers in column B are sequential i.e. 1, 2, 3 etc without any gaps in the sequence
1 - We receive an excel file from our customer (approx 20 different customers in total)
2 - I have an excel template we have to use, which will import into our MIS software. This excel template must remain the same or it wont import into our system. ie (Columns must be in this particular order)
3 - Our customer sends their own spreadsheets in their own formats ie, column in a different order to our excel template.
4 - I need to know if there is a quick way to remap customer columns into my excel template
5 - Im looking for a little application that will look at the customer spreadsheet and our template in the same window and let me link which fields need to map where in my template. When I have completed I can save what ive done and run when needed for this customer.
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
I have a list on flower names in column A , and the colors in Column B. For sorting purposes I have duplicates in Column A. I am creating an easier way for coworkers to organize the ordering process from wholesalers. On the order sheet It will have the dropdown that lets them choose the flower (ultimately I hope to make this searchable,) Once the flower is selected the next column will allow them to choose from the color available. Attached Is the sheet with my work so far.
View 8 Replies View Relatedin 1 workbook i have 2 sheets (sheet1 and sheet2) with same format of data that I need to compare and compile in sheet3 by a primary key column.
example:
sheet 1 & 2 data format:
|Atr1|Key|Atr2|SubAtr1_1|SubKey1|SubAtr1_2|SubAtr2_1|SubKey2|SubAtr2_2|Atr3|
..................... ......................................../......................................../
......................................Set1.....................................Set2
To be more specific I can compare this with an electrical wiring table, where:
Key is the wire number
Atr1, Atr2, Atr3 are general atributes of the wire number
Set1 are the atributes of the first end of the wire, called "from":
SubKey1 is the "from" device and its subatributes SubAtr1_1 & SubAtr1_2
Similar for the other end of the wire is Set2.
This means that Set1 and Set 2 have similar format & type of data.
I have found on your site an example of macro that is doing the comparision between the 2 sheets of data but I encountered a problem...sometimes on sheet2 Set1 and Set2 are reversed and are highlighted as a difference but I don't want this.
Please help me with macro that would create a rearranged copy of sheet 2 respecting the following conditions:
1. if Key from sheet1 cannot be found on sheet2 then copy entire row on sheet3.
2. if Key from sheet2 cannot be found on sheet1 then copy entire row on sheet3.
3. If Key from sheet1 = Key from sheet2
If SubKey1 from sheet1 = SubKey2 from sheet2
& SubKey2 from sheet1 = SubKey1 from sheet2
Then reverse Set1 with Set2 by swapping cells
and copy entire row (with Set1 and Set2 reversed)to sheet3.
All data on sheet 3 should be sorted by Key.
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................
I must put in descendent order some columns, but the thing is a bit more complicated...
As my english is not very good i am gonna explain it with a simple example:
----------------------------------
1 4 2 5 rabit fox dog cat
2 6 1 3 bird fis wolf mouse
----------------------------------------
Ok, i must order the numbers in a ascendent way, but reordering one number means also reordering its correspondient name (the name of the animals at its right).
So, again, with an example you will see it better:
Ordering the first row it must appear like this:
----------------------------------------------
1 2 4 5 rabit dog fox cat
----------------------------------------------
I didnt know if i had to post it here or in other subforum and either if it can be done with excel.
My formula is not working correctly; not sure what to do. I use this same formula with numbers and it seem to work, but only have 8 IF statements as well. This example below doesn't even work with only 8 IF statements.
--------------------------------------------------
What I am saying below is = If E3 = a swat team (i.e. EO-Deal Processing-Prescreen) then please enter "Chumbley" and so on. For anything else put an na or n/a for blanks or swat teams that I haven't lised in the criteria.
---------------------------------------------------
=IF(E3="EO-Deal Processing-Prescreen",Chumbley,IF(E3="EO-Deal Processing-DocGen",Chumbley,IF(E3="EO-Deal Processing-Events",Chumbley,IF(E3="EO-Deal Processing-Triage",Junk,IF(E3="EO-Deal Processing-Processing",Queck,IF(E3="EO-Deal Processing-Calcs",Doyle,IF(E3="EO-Deal Processing-Closing",Terry,IF(E3="EO-Deal Processing-D11B",Blazier,IF(E3="EO-Deal Processing-Isolated UI",James,na)))))))))
a = Range("d1").CurrentRegion.Resize(,2).Value
and
Range("G1").Resize(n,2).Value = b
what do the above two lines of code do?