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

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

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

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

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