Multiple ElseIf Statements With Areas
Dec 6, 2006
My 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 .....................
View 3 Replies
ADVERTISEMENT
Jun 6, 2009
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 Replies
View Related
Nov 22, 2008
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 Related
Dec 23, 2006
I have a userform that has two comboboxs on it. When the user clicks and selects an item in combobox 1 the code will highlight an area of cells that I set before. When the user clicks and selects an item in the combobox2 I need a piece of code that will highlight a second area of cells while still keeping the first areas of cells highlighted.
How I have it set up now if the combobox1.text = "Monday" then the cells B6:H20 are selected. But when the combox2 click event fires and selects another range of cells the first range selected by the combobox1 event is unselected. Is there a way to keep both areas selected?
View 3 Replies
View Related
Jun 23, 2014
In making my label printer, I will need to set the print area for multiple sheets based on a range generated in a cell. E.g. Sheet1 might need cells A1:P1200 set as print area, Sheet2 might need cells A1:G694 set as the print area, Sheet3 might not need to be printed, etc..
I have no problem generating a formula to state what the print area for each sheet should be, respectively, but passing that info into VBA has me really stumped. (Using indirect in the Page Setup menu doesn't work after the value has changed, it changes the value to a static reference, which is bizarre). I suspect I'd want to use the Sub Workbook_BeforePrint, but I'm not 100% on that. I will generally have 3 or more specific sheets selected using a button-triggered macro that gets a cell value of sheet names and selects those sheets.
View 4 Replies
View Related
Mar 20, 2008
I am trying to iterate through ranges (F2:G4) , (F5:G7) , ( F8:G11) , ... , lastrange
With each range I perform a simple median calculation.
The output of each calculation I would like to place in H2 , H3 , H4 , ... , lastcalculation
View 8 Replies
View Related
Oct 19, 2009
I have a situation which I hope is fixable. I have frozen panes above and to the left of cell F5, therefore all columns to the right of column E scroll left and right and all rows below row 4 scroll up and down as required. My problem is that I also need the whole top row to remain static all the time.
View 3 Replies
View Related
Oct 13, 2009
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")
View 9 Replies
View Related
Jun 6, 2008
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.
View 9 Replies
View Related
Apr 13, 2007
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.
View 7 Replies
View Related
Aug 6, 2014
I am trying to get a function in a sheet where it has two possible truth outcomes and one false outcome. Both statements false statement goes back to
'Production Metrics 2'!E11/12
Here are the two If statements as they appear in the formula bar now. I need to combine them so they both work and don't cancel each out out.
=IF('Production Metrics 2'!E11/12>2500,2500,'Production Metrics 2'!E11/12) If c10+b10>=4500,0,'Production Metrics 2'!E11/12
View 10 Replies
View Related
Jun 27, 2013
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] .........
View 5 Replies
View Related
Jun 19, 2006
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 = ""..........
View 9 Replies
View Related
Nov 23, 2008
I'm having trouble joining the followinf IF Statements. They work independently, however, I cannot get them to work in the same Cell.
Any help would be greatly appreciated.
=IF(A9="YES", SUM(A15+A21),"0")
=IF(B9="YES", SUM(B15+B21), "0")
View 14 Replies
View Related
Aug 31, 2009
Trying the following multiple if formula, however it only works for my first if statement and not the rest.
=(IF(G21="onl",WORKDAY(E21,L27,K26:K43),(IF(G21="telb",WORKDAY(G21,L27,K26:K43),(IF(G21="mrt",WORKDA Y(G21,L27,K26:K43),(IF(G21="tlr",WORKDAY(G21,L26,K26:K43),(IF(G21="atm",WORKDAY(G21,L26,K26:K43),(IF (G21="chq",WORKDAY(G21,L26,K26:K43),(IF(G21="crcdwu",WORKDAY(G21,L28,K26:K43)))))))))))))))
View 4 Replies
View Related
Apr 28, 2007
Is there a way to write a forumula that read, if it's January, add column A1:A10, if it's February, add column A1:B10, if it's March, add column A1:C10 and it's April, add column A1:D10
View 9 Replies
View Related
Nov 11, 2009
I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:
There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))
How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))
View 2 Replies
View Related
May 17, 2008
I have attached a copy of my monthly work schedule. Sorry if it looks too noobish but I am.
The question I have is on the schedule right now, it uses to of the same schedule, one is called request off and the other the final. The request off right now is where all changes are made, and they reflect on the final. I did it this way because I could not figure out how to make the cells autofill according to the day of the week, like if it was friday i needed it to fill in the shifts and normal days off automatically.
What I am trying to do now is eliminate the need for the second schedule, the request off one. I think I have figured out how to have the cells autofilled according to the day of the week, and also let the user enter in other stuff such as sick, vacation or whatever, and have it autofill if the cell is left blank, using vba. this is what i have so far, and it is working I was just wondering if it was possible to set it up for a range of cells instead of having to enter each one in manually.
View 14 Replies
View Related
Oct 4, 2008
if it is possible to use a batch query to execute more than one SQL statement at once from Excel. Something like this:
View 9 Replies
View Related
Aug 28, 2009
Can anyone tell me what I'm trying to accomplish with the following formula:
=(IF(E3="CORP",F14*0.9,IF(E3="GOLD/SLVR",F14*0.9,IF(IF(E3="ON",IF(Q33>=192,F14*0.9,IF(Q33>=96,F14*0.95, F14))), IF(E3="OFF",IF(Q33>=288,F14*0.9,IF(Q33>=192,F14*0.95,F14)))))))
It doesn't work as written but I don't what edits I have to make to make it work.
View 5 Replies
View Related
Oct 16, 2009
If my input page B13 is either New Lease In-House or New Lease w/Co-Borker andmy input page B43 is - then take cell D57 in my Analysis report, mulitply it by cell B32 from Input page and divide it by 2, otherwise, take cell D57 on the analysis report, mulitply it by B42 on Input page and divide it by 4. I think I have a mess up with my brackets. This is what I have: =IF((OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker"))AND('Input Page'!B43="-",'Analysis Report'!D57*'Input Page'!B32/2,'Analysis Report'!D57*'Input Page'!B32/"4")
View 4 Replies
View Related
Oct 14, 2013
I want to create a formula that will turn B2 in floors if A2 has /1 with its formula, Ceilings if it has /2, walls if it has /3, and M&E if it has /4. For example 456-401/2-1569 in cell A2 will populate ceilings in B2. I know I can create If statements but I can only figure out how to do this to search for one text and populate one word. I know Vlookup is possible but this spread sheet will be blank and as people populate there information I need the cells to automatically populate the data into the B column. how I can create the right formula?
View 4 Replies
View Related
Feb 21, 2014
I'm trying to create a statement that will add certain fields based on the month all in one formula. IE
=if(A1="January",sum(a2),if(A1="February",sum(a2:A3)......if(a1="December",sum(a2:a13).
I've tried a normal "IF" statement, but that does not seem to work after 4 months.
View 2 Replies
View Related
Feb 28, 2014
I am trying to tell the spreadsheet to look at each separate month and return different criteria. Current formula for single month search is:
=IF(VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F","")))))
I would like to add an AND function so that the IF statement also looks at the month which is represented in this case as $E$7, the formula I have been trying is:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F")
Then carry this forward for other months, for example, a later look at this formula would be:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO
[Coe] ...........
View 2 Replies
View Related
Jun 15, 2009
=IF(VLOOKUP(B3,'Rep List'!$E$1:$I$10000,5,FALSE)=OR("BH","AC","#N/A",0),VLOOKUP(C3,'Rep List'!$A$1:$C$1000,3,FALSE),VLOOKUP(B3,'Rep List'!$E$1:$I$10000,5,FALSE))
I'm getting the "#value" error - the vlookups are working independently
View 9 Replies
View Related
Jan 18, 2010
I have a userform with 14 independant checkboxes on and would like it when the operator presses the 'go' button for the code to look at each box and if ticked put a "Y" in the column on a spreadsheet associated with that checkbox.
I have this portion of code set for each checkbox within the sub function, but it's a lot of code for something relatively simple:
If GroupFinder.firstbox.Value = True Then
Worksheets("Number one").Range("C" & grouprow).Value = "Y"
Worksheets("Number one").Range("C" & grouprow).ClearComments
Worksheets("Number one").Range("C" & grouprow).AddComment
Worksheets("Number one").Range("C" & grouprow).Comment.Text Text:="Added by:" & Operator & Chr(10) & "On:" & Date
Else
Is there an easy and quicker way of doing this without all this code for each of the 14 checkboxes? I'm sure there is, but I can't figure it out.
I'm trying to keep the code simplified for others who follow after and might not follow VBA so well.
View 9 Replies
View Related
Feb 16, 2010
Can you have multiple IF statements in one cell. I know nested IF's are out there, but they will not work for what I need.
I just need to have two (or more) complete IF statements.
View 9 Replies
View Related
Aug 23, 2006
I have are two listboxes set up, one with all the states, the other with 7 possible variable selections. So in my case I have 371 (DC, Virgin Islands and Puerto Rico are included as states in this) possible combinations here. With each combo the user needs to be able to enter up to 2 years of quarterly data (8 quarters, so 2968 pieces of data possible) and then press a button to save that data to a specified location which I will need to access at run of the overall macro.
Is there an easier way to do this then to simply assign each combination a cell? Maybe an If State = AK and Variable = TUR then find column = AK & row = TUR (per quarter) type solution?
View 6 Replies
View Related
Aug 24, 2007
I'm using one spreadsheet "Take Off", to pull data from a cell which dictates an objects shape, based on this shape I have a very large IF statement that pulls data from an adjacent cell where an objects dimensions are entered, the statement than proceeds to use these dimensions to calculate the weight of the object.
I've run into the problem with only 7 IF statements, I have gone online and tried to figure out how to avoid this since it is a common problem, but I can't figure out how to do it, most of the examples show very simple and basic formulas. I am unable to figure out how to translate this into my IF statement where I make reference to many cells as well as us differant formulas within the IF statement.
My latest solution was to breakdown the IF statements and try to my my own functions but I think I am too green to have done this properly. Any guidance would be greatly appreciated.
The following is the code I'm working with followed by how I tried to remedy the situation:
=If(Or(C21="BPLT",C21="L"),(((Get_Word(D21,"First")+Get_Word(D21,3))/1000)*(Get_Word(D21,"Last")/1000)*(E21/1000)*7850),If(And(C21="PL",L21<4),(Get_Word(D21,"First")/1000*Get_Word(D21,"Last")/1000*E21/1000*7850), If(Or(C21="BP",C21="PL",C21="BAR"),(Get_Word(D21,"First")/1000)*(Get_Word(D21,3)/1000)*(Get_Word(D21,"Last")/1000)*7850, If(And(C21="HSS",L21>4),(Get_Word(D21,"First
I tried to make my own functions:
aCalc: =If(Or( 'Take-Off'!C13="BPLT",'Take-Off'!C13="L"),(((Get_Word('Take-Off'!D13,"First")+Get_Word('Take-Off'!D13,3))/1000)*(Get_Word('Take-Off'!D13,"Last")/1000)*('Take-Off'!E13/1000)*7850),"")
bCalc:
=If(And( 'Take-Off'!C13="HSS",'Take-Off'!L13>4),(Get_Word('Take-Off'!D13,"First")*2+Get_Word('Take-Off'!D13,3)*2)/1000*Get_Word('Take-Off'!D13,"Last")/1000*'Take-Off'!E13/1000*7850,"")
and so on until hCalc, but when I put them together in a cell like this:
=aCalc()+bCalc()+cCalc()+dCalc()+eCalc()....
I get an error #REF.
I realize a problem might be that I'm referencing a specific cell within my formulas, and many examples online show cells referenced in the form =A$1$, is there a differant way for referencing a cell, because currently I rely on Excel to automatically do this by way of relative reference.
ADDED: Ok I scrapped making the functions because I'm not sure and I did some research on VBA so I tried this one:
Function Weight(Shape As String, Amount As Double, Dimension As String, Optional Length As Variant)
If (Shape = "BPLT" Or Shape = "L") Then
If (Amount > 5) Then
Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 + Get_Word(Dimension, 5) / 1000 * Get_Word(Dimension, "Last") * 7850
Else
Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 * Get_Word(Dimension, "Last") * 7850
End If
But when I try to put Weight(C6,D6,F6,E6) I run into the error "Sub or Function Not Defined"
View 7 Replies
View Related
Jun 20, 2014
I have a worksheet and in one of my columns I can have multiple values, this could either be a valid numeric value, .e.g "Trace" or a value such as >1.5 or it could be blank.
Depending on the value I then want to return a different result in another cell.
In the example I have tried my data is in cell E8
I have tried the following formula and failed miserably, I'm not sure whether IF or IF OR can cope with this.
=IF(E8>=0,"1",IF(E8<0,"Invalid",IF(E8="Trace","2",IF(ISBLANK(E8),"4",IF(LEFT(E8,1=">"),"5")))))
If E8 contains a numeric value equal or greater than 0 then I want it to return a value of 1, this works
If E8 contains a value that is negative then I want my formula to then display "Invalid"
If E8 contains the word "Trace" to return a value of 2
If E8 is blank then to return a value of 4
If the first character in E8 is ">" then return a value of 5
View 4 Replies
View Related
