2 If Statements In Macro?
Sep 28, 2009
I've done a few searches and found the 2 macros I need. They are:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Count = 1 Then
If InStr(1, Target.Value, "REL", vbTextCompare) Then
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1).Value = Date
Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy"
Target.EntireColumn.AutoFit
End If
End If
End If
End Sub
and
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub...........................
I know very little about VBA. The first program I use to enter a static date to the cell (Column C) to the left when I type "REL" into a cell (Column D) and the second will be used to enter a static date into the cell next to the one above (Column B) when I type anything into a cell (Column E).
They both work individually but I am now trying to compile them together and get the compile error (Because they have the same worksheet_change name). I do not know how to rename or make them in the same.
View 2 Replies
ADVERTISEMENT
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
May 26, 2008
Sheet 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
View 4 Replies
View Related
Nov 28, 2009
I created the driver macro Mike2 (At first cosisting only of the first 2 lines) with keyboard shortkey +m, to run macro Mike. (In future additionals macros). Every time I run this macro it adds 2 lines to the Macro ,regardless of the End and Exit sub statements. Why?? Do not want this to happen.
View 4 Replies
View Related
Sep 18, 2012
I have 2 option buttons linked to cell A1 and I want to run a different macro based on what A1 shows. The code I've been trying is:
Code:
If A1 = 2 Then
Application.run "ship1"
ElseIf A1 = 1 Then
Application.run "ship2"
End If
Using this runs neither of the macros though. I've tried a few different things and still no luck.
View 5 Replies
View Related
Feb 9, 2008
I'm needing a macro that will allow me to get around the limits of no more than 7 IF statements and using a SUMPRODUCT formula as well. I need the total or sum of the macro/formula to be in cell "DB8".
Here's my formula: =SUMPRODUCT(IF(CP8=M11,EXACT(K7,"DI"),0)+0+SUMPRODUCT(IF(CP8=W11,EXACT(U7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AG11,EXACT(AE7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AQ11,EXACT(AO7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BA11,EXACT(AY7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BK11,EXACT(BI7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BU11,EXACT(BS7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CE11,EXACT(CC7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CO11,EXACT(CM7,"DI")+0,0))))))))))
View 9 Replies
View Related
Dec 21, 2009
how this code can be condensed getting rid of some of the duplicate coding?
Everything is duplicate except for the For i = 1 To x part as the formula that is added is changed from sheet to sheet.
View 10 Replies
View Related
Nov 1, 2012
I tried sending this message once before but the system crapped out. At least on my end. If it was sent and this is a partial duplicate. At any rate, as most of you know when an Excel workbook has a large number of merged fields Excel sometimes changes all the cell (not just the merged cells) formats to a Date type, with the exception of those cells that are the Text data type. I found the following code on the net someplace and I can't properly cite the author, that when run resets the cell formats to the default format which is General for all those that had been changed.
Sub CleanStyles()
Dim sty As Style, wbTemp As Workbook
' First, remove all styles other than Excel's own.
' they may have arrived from pasting from other workbooks
[Code]....
This code worked for months. However, it no longer works. I opened the code in the VBA editor and stepped through it using F8. The first time I pressed F8 the first line in the code is highlighted, just like always. The next time I pressed F8 the third line of code (not counting the remarks or blank lines) is highlighted. It did not highlight the Dim statement.
I'm using Excel 2007 in XP.
View 1 Replies
View Related
Jan 8, 2014
I'm completely new with macros. I was wondering what you would need so that the information will automatically update itself onto a summary page. For example for entries on one worksheet to create a statement on another worksheet?
Example:
Asset Type
Description
Date
Amount
OA
[Code]....
The first entry doesn't have a date, because the asset type is classified as OA. (therefore an exception) Each of this information will be linked to it's own worksheet. For example, the OA entries would create their own sheet which will summed.
View 1 Replies
View Related
Oct 14, 2009
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)
View 2 Replies
View Related
Jun 11, 2014
I need a VBA code to create a Text file which should be in the form of DML (my scenario is to get Insert Statements for the Data available in the Excel) from Excel Sheet. And this has to be done by assigning a macro to command button where by clicking on it the DML text file should be generated. Even the syntax for the scenario will do.
View 1 Replies
View Related
Aug 9, 2007
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)))))))))
View 9 Replies
View Related
Aug 22, 2008
a = Range("d1").CurrentRegion.Resize(,2).Value
and
Range("G1").Resize(n,2).Value = b
what do the above two lines of code do?
View 9 Replies
View Related
Oct 1, 2009
I have the following:
=IF(I8>=G8,IF(I8
View 9 Replies
View Related
Dec 23, 2009
if-and-else statement in vb.
I made a combo box where the user selects a part in a combo box and I want the part selected to be stored in a certain cell. The first selection would be stored in C15 and if thats not empty then in D15 and if thats not empty then in E15 and if thats not empty then in F15.
Dim m As Integer
m = cboPart.ListIndex
If m = 0 Then
If IsEmpty(ActiveCell.Range("C15")) = True Then
ActiveCell.Range("C15") = cboPart.Value & vbLf & "Name: " + txtName
Else
ActiveCell.Range("D15") = cboPart.Value & vbLf & "Name: " + txtName
End If
Else
If IsEmpty(ActiveCell.Range("D15")) = False Then
ActiveCell.Range("E15") = cboPart.Value & vbLf & "Name: " + txtName
Else
ActiveCell.Range("F15") = cboPart.Value & vbLf & "Name: " + txtName
End If
End If
When I run this if-and-else statement it stops storing the selected data after the following:
If IsEmpty(ActiveCell.Range("C15")) = True Then
ActiveCell.Range("C15") = cboPart.Value & vbLf & "Name: " + txtName
Else
ActiveCell.Range("D15") = cboPart.Value & vbLf & "Name: " + txtName
End If
View 9 Replies
View Related
Nov 19, 2013
I got a 2 columns (A:B) with values in workbook1 (survey2.xlsm)
I want to open a sheet named according to values from column A wb1 in workbook2 (du_database2)
And I want to fill in the offset values (column B) in the aproppriate sheet
I wrote this sub for it. I am having troubles with the for each statements (how do I set them up properly?). The script itself hasn't been checked might also been wrong there.
VB:
Sub copytest()
'by J
Dim ws As Worksheet
Workbooks.Open "survey2.xlsm"
Application.Workbooks("du_database2.xlsm").Activate
[Code] .....
View 2 Replies
View Related
May 12, 2014
I'm using vlookups within IF statements to look in a number of various speadsheets depending on what the lookup value is.
So for example,
Formula:
This works well enough however I need some look up values to look in multiple spreadsheets as the data is split out between several spreadsheets. So for example, if B1=Hello I want to vlookup to 2 different spreadsheets.
I know I could do two instances of a vlookup within the formula, both for "hello" but looking to different spreadsheets. But doesn't this mean that if the first vlookup finds the value, the second one will overright it with an "#N/A" if it doesn't find it?
View 2 Replies
View Related
Dec 6, 2013
I have 2 conditions that I need to apply.
View 3 Replies
View Related
Jan 29, 2014
I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
View 6 Replies
View Related
Feb 7, 2014
I have various 6 x 6 blocks which contain just numbers, in another part of the spread sheet I have a 1 x 6 block of numbers. What I need to do is to check whether any of the numbers in the 1 x 6 block appear in any of the 6 x 6 blocks.If that does occur then I want to make a specific cell increase by the times that the match happens.using HLookup but just got errors and the If Statement, although did work, went on and on and on.
View 6 Replies
View Related
Nov 17, 2008
I have a list of dates and I need to calculate how many of the dates are before or between certain dates. I can't quite get my parameters correct.
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
Dec 8, 2008
I am new to this thread and hope you can find the time to help me. I am using excel to look up data manually entered in Widgets, Gidgets, Lidgets fields to find an exact match in an array and return the value in "field 1" with no luck. My lookup formula should return "9" from the Field 1 column.
Data Entry:
Widgets3
Gidgets6
lidgets5 ........
View 14 Replies
View Related
Mar 4, 2009
I need to write an IF statement that exceeds the 8 statement maximum. Basically, what I want to say is: IF(U2>K2,1,IF(U2+V2>K2,2.........all the way up to U2...AM2>K2,19)
View 13 Replies
View Related
Apr 8, 2009
I've attached the workbook to make this easier. If you look at the file master.xls you will see 2 tabs. On the Tool tab I've highlighted some blue cells and some rose cells. If you look at the blue cell G68 there is a long formula that reads up to 3 cells from the Specs tab and then plugs in the correct data from the blue cells on the right side of the Tools tab page. I need the rose cell F69 to do the same thing only with the data from the rose cells to the right. When I try and duplicate the fromula from G68 into F69 I get errors. I hope I made this clear enough. The formula in G68 is long, is there a way to shorten it? Also sometimes in this workbook when I try to type a formula in a cell I get the text I typed instead of the formula. For example I might enter in A1, =B2. Instead of getting the data from B2, it reads =B2. I've looked at how the cell is formatted but can't get it to work.
View 3 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
Oct 30, 2009
I'm hoping someone can help me sort this out. I have a formula I need te determine if a TAT was 'met' or 'missed' and it has multiple criteria. Here is the formula I am trying to get to work:
=IF(AND(I2="Amendments",F2<=3,IF(AND(I2="Renewals",F2<=1,OR(D2<=G2)))),"Met",IF(F2="n/a","n/a","Missed"))
I've been able to get separate pieces of the formula to work correctly, example:
=IF(AND(F2<>"n/a",I2="Amendments",F2<=3,OR(D2<=G2)),"Met",IF(F2="n/a","n/a","Missed"))
but when I try to combine everything into one string I can not get it to work.
View 10 Replies
View Related
Jul 3, 2008
I know the concept of Case statements sounds simple, but trying to write it in my scenario seems a lot more difficult than I thought it would be. I have three columns with an unspecified number of rows (which are generated from another macro). Lets say those colums are C, D, E, and they should be strings because my criteria are based on the number of digits contained in each cell. I want to add a certain number of periods to a given cell, then add that string onto two other cell strings, and form one combined string in a separate cell.
Basically I want to sort of Concatenate the cells into a given cell in column F for each row, but there are many conditions. If the cells in column C contain 3 digits, I want to add one period "." onto the original string. If it has 4 digits, then I add nothing, and then, add that cell to the string in cell D, and then Cell E. [It is like the function Concatenate(C1&".",D1&"..",E1)]. Cells in Column D have 6 criteria, and cells in Column E have zero criteria.
And I need this to loop down until it reaches an empty row or cells.
View 12 Replies
View Related
Dec 4, 2008
I have a spreadsheet which I need to write a formula which looks at a field and if the number in the field starts with either 55, 50 or 78 it populates my selected field. This is an example of what I am trying to do :
View 2 Replies
View Related
Aug 14, 2009
Trying to use several ifs together. Not sure, where my end if's need to be. I keep get Next w/o For error.
View 4 Replies
View Related