Copy Down xlup

[Anne Troy]

I need to copy formulas in 3 cells:

J2, K2, L2

down to the last-filled row in column I.

I found this code, but have no clue how to alter it.

Sub Test()
Dim LastRow As Long
LastRow = Range("K" & Rows.Count).End(xlUp).Row
Range("V4").FormulaR1C1 = "=RC[-18]"
Range("V5:V" & LastRow).FormulaR1C1 = "=IF(RC[-18]="""",R[-1]C,RC[-18])"
End Sub

I thought I would change "K" to "J:L", but couldn't figure out what the rest of the code does. Ugh.

Thanks so much!!

 

[Anne Troy]

For anyone feeling industrious, it would be FANTASTIC if it also then placed, in the first blank cells below those 3 columns, sums of those 3 columns. The formulas are basically stripping text and pulling numbers out of it for m.

J2 =LEFT(TRIM(I2),1)

K2 =MID(I2,FIND("+",I2)+2,1)

L2 =MID(I2,FIND("er +",I2)+5,1)

Thanks again!

 

[VoG]

Try this

Sub Test()
Dim LastRow As Long
LastRow = Range("I" & Rows.Count).End(xlUp).Row
Range("J2:L2").AutoFill Destination:=Range("J2:L" & LastRow)
End Sub

 

[VoG]

Try changing those formulas to coerce the results to numbers like this

J2 =LEFT(TRIM(I2),1)+0

Then

Dim LastRow As Long
LastRow = Range("I" & Rows.Count).End(xlUp).Row
Range("J2:L2").AutoFill Destination:=Range("J2:L" & LastRow)
Range("J" & LastRow + 1).Formula = "=SUM(J2:J" & LastRow & ")"
Range("K" & LastRow + 1).Formula = "=SUM(K2:J" & LastRow & ")"
Range("L" & LastRow + 1).Formula = "=SUM(L2:J" & LastRow & ")"
End Sub

 

[Anne Troy]

VoG, you're a monster, you know that? It was your code I pasted in the first place, and I so much appreciate your work!!

Can you possibly get me a sum on the first blank row below?

I'm going to try out the code you left.

Thanks!!

 

[Anne Troy]

OMG! You already did it!!! Thanks so much!! IT'S BEAUTIFUL!!

 

[VoG]

Just spotted an error due to copy and paste

Sub Test()
Dim LastRow As Long
LastRow = Range("I" & Rows.Count).End(xlUp).Row
Range("J2:L2").AutoFill Destination:=Range("J2:L" & LastRow)
Range("J" & LastRow + 1).Formula = "=SUM(J2:J" & LastRow & ")"
Range("K" & LastRow + 1).Formula = "=SUM(K2:K" & LastRow & ")"
Range("L" & LastRow + 1).Formula = "=SUM(L2:L" & LastRow & ")"
End Sub

 

[Anne Troy]

Right. Amazingly enough, I figured that one out myself, but wanted to make sure I mention it for the sake of others.

But I need one more piece, if you don't mind.

I've got Ron's code to attach emails.

And I'm trying to say:

If Range("'Dashboard'!G10").Text = "Type 1" Then

Then "say this" and attach these two files...

But

If Range("'Dashboard'!G10").Text = "Type 2" Then

Then "say this" and attach this one file.

The names/locations of the files can be static. Here's a piece of the code I have now, though I'm not sure I should be doing two separate IFs, you know?

    If Range("'Dashboard'!G10").Text = "Hospitality" Then
     
    With OutMail

                .To = Range("'Dashboard'!D3").Text
                .CC = Range("'Dashboard'!D4").Text & ";" & Range("'Dashboard'!D5").Text & Range("progemail").Text
                .BCC = Range("'Dashboard'!D6").Text

                .Subject = "Harbortouch POS Order Update: " & Range("merchantname").Text & " " & Range("merchantmid").Text
                .Body = Range("'Email Text'!$C$16").Text & Chr(10) & Chr(10) & _
                Range("'Email Text'!$C$18").Text & Chr(10) & Chr(10)
                

        '.Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        .Attachments.Add ("C:\SendingFiles\MerchantHelperApplication.xlsx")
        .Attachments.Add ("C:\SendingFiles\MenuOrganization.xlsx")
   
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Display
        '.Send
    End With
    
    If Range("'Dashboard'!G10").Text = "Retail" Then
    
        With OutMail

                .To = Range("'Dashboard'!D3").Text
                .CC = Range("'Dashboard'!D4").Text & ";" & Range("'Dashboard'!D5").Text & Range("progemail").Text
                .BCC = Range("'Dashboard'!D6").Text

                .Subject = "Harbortouch POS Order Update: " & Range("merchantname").Text & " " & Range("merchantmid").Text
                .Body = Range("'Email Text'!$C$16").Text & Chr(10) & Chr(10) & _
                Range("'Email Text'!$C$18").Text & Chr(10) & Chr(10)
                

        '.Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        .Attachments.Add ("C:\.txt")
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Display
        '.Send
    End With

Thanks again for any help you give!

 

[VoG]

Maybe like this

If Range("'Dashboard'!G10").Text = "Hospitality" Then
     
    With OutMail

                .To = Range("'Dashboard'!D3").Text
                .CC = Range("'Dashboard'!D4").Text & ";" & Range("'Dashboard'!D5").Text & Range("progemail").Text
                .BCC = Range("'Dashboard'!D6").Text

                .Subject = "Harbortouch POS Order Update: " & Range("merchantname").Text & " " & Range("merchantmid").Text
                .Body = Range("'Email Text'!$C$16").Text & Chr(10) & Chr(10) & _
                Range("'Email Text'!$C$18").Text & Chr(10) & Chr(10)
                

        '.Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        .Attachments.Add ("C:\SendingFiles\MerchantHelperApplication.xlsx")
        .Attachments.Add ("C:\SendingFiles\MenuOrganization.xlsx")
   
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Display
        '.Send
    End With
    
    ElseIf Range("'Dashboard'!G10").Text = "Retail" Then
    
        With OutMail

                .To = Range("'Dashboard'!D3").Text
                .CC = Range("'Dashboard'!D4").Text & ";" & Range("'Dashboard'!D5").Text & Range("progemail").Text
                .BCC = Range("'Dashboard'!D6").Text

                .Subject = "Harbortouch POS Order Update: " & Range("merchantname").Text & " " & Range("merchantmid").Text
                .Body = Range("'Email Text'!$C$16").Text & Chr(10) & Chr(10) & _
                Range("'Email Text'!$C$18").Text & Chr(10) & Chr(10)
                

        '.Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        .Attachments.Add ("C:\.txt") ' you will need to add the correct file name here
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Display
        '.Send
    End With
    End If