Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Formula
when using double quotes inside a string, you need to double the double
quotes Dim sForm as String sForm = "=IF(O2=""H"",""On Time"", IF(AND(O2=""M"",N2" & _ "<0),""Late"",IF(AND(O2=""M"",N20),""Early"") ))" ActiveCell.Formula = sForm demo'd from the immediate window: sform = "=IF(O2=""H"",""On Time"", IF(AND(O2=""M"",N2" & _ "<0),""Late"",IF(AND(O2=""M"",N20),""Early"") ))" ? sform =IF(O2="H","On Time", IF(AND(O2="M",N2<0),"Late",IF(AND(O2="M",N20),"Ea rly"))) -- Regards, Tom Ogilvy "jmdaniel" wrote in message ... I am trying to insert a formula in a macro, then auto fill this formula to the last row with data in it. So far, (with the first cell the formula goes in being P2): Range("P2").Select ActiveCell.Formula = "=IF(O2="H","On Time", IF(AND(O2="M",N2<0),"Late",IF(AND(O2="M",N20),"Ea rly"))) Sub autofill() LastRow = .Cells(Rows.Count, "A").End(xlUp).Row .Range("D2").autofill Destination:=.Range("D2:D" & LastRow) _ , Type:=xlFillDefault End With Column O has H or M as text, signifying Hit or Miss, and column N has the number of days a shipment was late, on time, or early. The error I got, I believe was a compile error, "expecting an end" (slight paraphrase), and the "H" was highlighted. I hope I have given enough info, and that somebody can give me a hand! Thanks, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Formula
Hello Jeff,
I took a peek at Nate's response, but can't really tell what it is supposed to do. Thanks! Step by step. It stores the last used row based on column A's data. It then looks at which row that is. If the row is greater then 2, it takes the range p2:px where x is your last row and populates it with the following function: =IF(O2="H","On Time",IF(O2="M",CHOOSE((N2=0)+1,"Late","Early")," ")) Which is a slightly modified version of yours, where N2 = 0 is Early as well, the adjustment is straightforward. Because it uses relative addresses (r1c1), you can populate the entire range with the function in one pass and it will update accordingly. If lstRow < 3, simply populate P2. Hope this helps. Regards, Nate Oliver |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Formula
The exact error message is:
Compile Error Expected End Sub "jmdaniel" wrote: Okay, that makes sense. I inserted your stuff in there, but now am getting an error message, "expecting a sub" with the 5th line below highlighted: Selection.Sort Key1:=Range("O2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("O:O").Select Selection.TextToColumns Destination:=Range("O1"), DataType:=xlFixedWidth, _ OtherChar:="|", FieldInfo:=Array(Array(0, 2), Array(7, 1)), _ TrailingMinusNumbers:=True Sub tester() Dim lstRow As Long Let lstRow = Range("a65536").End(xlUp).Row If lstRow 2 Then Range(Cells(2, 16), Cells(lstRow, 16)).FormulaR1C1 = _ "=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))" Else: Cells(2, 16).FormulaR1C1 = _ "=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))" End If End Sub End Sub This section of the macro simply removes the leading blank spaces before the letter "M", (the report being worked on is pulled from Oracle originally). If I don't do that, the macro returns incorrect values in the P column. With this error in place, I don't get down to your code to see if it works. Thanks! "Nate Oliver" wrote: Hello Jeff, I took a peek at Nate's response, but can't really tell what it is supposed to do. Thanks! Step by step. It stores the last used row based on column A's data. It then looks at which row that is. If the row is greater then 2, it takes the range p2:px where x is your last row and populates it with the following function: =IF(O2="H","On Time",IF(O2="M",CHOOSE((N2=0)+1,"Late","Early")," ")) Which is a slightly modified version of yours, where N2 = 0 is Early as well, the adjustment is straightforward. Because it uses relative addresses (r1c1), you can populate the entire range with the function in one pass and it will update accordingly. If lstRow < 3, simply populate P2. Hope this helps. Regards, Nate Oliver |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Formula
Jeff, try the following:
Sub tester() Dim lstRow As Long Let lstRow = Range("a65536").End(xlUp).Row Application.ScreenUpdating = False Range(Cells(2, 1), Cells(lstRow, 15)).Sort _ Key1:=Range("O2"), Order1:=xlDescending, Header:=xlGuess Columns("O:O").TextToColumns Destination:=Range("O1"), _ DataType:=xlFixedWidth, OtherChar:="|", _ FieldInfo:=Array(Array(0, 2), Array(7, 1)) If lstRow 2 Then Range(Cells(2, 16), Cells(lstRow, 16)).FormulaR1C1 = _ "=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))" Else: Cells(2, 16).FormulaR1C1 = _ "=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))" End If Application.ScreenUpdating = True End Sub You only want one subroutine. So, one Sub ___() and one End Sub. Also, there's no need to select ranges and declare your variables at the top of the procedure, it makes your code easier to follow/maintain. Regards, Nate Oliver |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Formula
I'll take a peek at it in the morning, Nate. Appreciate very much the help. Think I might need to do a little reading on macros....
"Nate Oliver" wrote: Jeff, try the following: Sub tester() Dim lstRow As Long Let lstRow = Range("a65536").End(xlUp).Row Application.ScreenUpdating = False Range(Cells(2, 1), Cells(lstRow, 15)).Sort _ Key1:=Range("O2"), Order1:=xlDescending, Header:=xlGuess Columns("O:O").TextToColumns Destination:=Range("O1"), _ DataType:=xlFixedWidth, OtherChar:="|", _ FieldInfo:=Array(Array(0, 2), Array(7, 1)) If lstRow 2 Then Range(Cells(2, 16), Cells(lstRow, 16)).FormulaR1C1 = _ "=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))" Else: Cells(2, 16).FormulaR1C1 = _ "=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))" End If Application.ScreenUpdating = True End Sub You only want one subroutine. So, one Sub ___() and one End Sub. Also, there's no need to select ranges and declare your variables at the top of the procedure, it makes your code easier to follow/maintain. Regards, Nate Oliver |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for inserting a letter | Excel Discussion (Misc queries) | |||
Inserting formula in the cell below | Excel Worksheet Functions | |||
Inserting Formula | Excel Discussion (Misc queries) | |||
inserting a formula | Excel Discussion (Misc queries) | |||
Formula changes while inserting a row !!!! | Excel Worksheet Functions |