VBA Code Terminating 1 Row Too Early

[censo]

Hello,

The formulas in the VBA code below work perfectly for what I'm wanting to achieve; however, I notice that it's terminating 1 row before the last line of data. I thought the "offset" might have something to do with it so when I deleted it, it wreaked havoc, haha. I think the "offset" is to not include the header but as you can see in the results, I just need Columns E, F, and H to be shifted down just one row.

The puzzling part (for me anyway), is that Column D is perfect the way it is and uses the same syntax. Why is that?

Sub A_Today()


Application.ScreenUpdating = False
    
    With Worksheets("ShippingConfirmation").Range("C2").CurrentRegion.Columns("C")
        .Offset(1, 1).Resize(.Rows.Count - 1, 1).FormulaR1C1 = "=TODAY()"
    .Value = .Value
    End With


    With Worksheets("ShippingConfirmation").Range("D2").CurrentRegion.Columns("D")
        .Offset(1, 1).Resize(.Rows.Count - 1, 1).FormulaR1C1 = _
        "=IF(R[1]C7="""","""",IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(""|""&INDEX(CARRIER_TBL,0,1),""|""&R[1]C7),INDEX(CARRIER_TBL,0,2)),""FedEx""))"
    .Value = .Value
    End With


    With Worksheets("ShippingConfirmation").Range("E2").CurrentRegion.Columns("E")
        .Offset(1, 1).Resize(.Rows.Count - 1, 1).FormulaR1C1 = _
        "=IF(RC[-1] = ""Other"",""United Delivery Service"","""")"
    .Value = .Value
    End With


    With Worksheets("ShippingConfirmation").Range("G2").CurrentRegion.Columns("G")
        .Offset(1, 1).Resize(.Rows.Count - 1, 1).FormulaR1C1 = _
        "=IF(RC[-2] = ""United Delivery Service"",""Standard"","""")"
    .Value = .Value
    End With


Application.ScreenUpdating = True
End Sub

Book1
	A	D	E	F	G	H
1	order-id	ship-date	carrier-code	carrier-name	tracking-number	ship-method
2	0000000001	2018-06-26	Lasership		?1Z9728W90301392248?
3	0000000002	2018-06-26	Lasership		1LS720804299375
4	0000000003	2018-06-26	Other	United Delivery Service	C11659806347612	Standard
5	0000000004	2018-06-26			WM6280000000924898
ShippingConfirmation

 

[RoryA]

I presume the issue is the formula, not the code per se. The formula in the second With block refers to R[1]C7 which is looking at the row below the formula cell. I think you need RC7 instead.

 

[censo]

I presume the issue is the formula, not the code per se. The formula in the second With block refers to R[1]C7 which is looking at the row below the formula cell. I think you need RC7 instead.

I agree it looks a little off but this is how I derived that formula in VBA form.

I copied all the formulas I needed to Wordpad
I opened up a blank Excel file
I began recording macros
I went to the respective cell for the formula pasted it in and stopped the macro
I repeated these steps for each of my formulas
I then went into the module (ALT+F11) and extracted the VBA version of those formulas

So, following your logic, only one of the columns would show the formulas falling short. If you look at the screenshot I provided all 3 formulas on separate columns fall short (blank on the last row) and those don't exhibit the proposed anomaly you point out.

 

[RoryA]

I agree it looks a little off

It would be pretty easy to verify, by just amending that line and testing.

So, following your logic, only one of the columns would show the formulas falling short

Nope. Column E is the one that refers to the row below the formula row. Columns F and G both refer to column E, so if that's off, both of them will be too.

 

[censo]

It would be pretty easy to verify, by just amending that line and testing.

Nope. Column E is the one that refers to the row below the formula row. Columns F and G both refer to column E, so if that's off, both of them will be too.

I am eating crow for dinner with a slice of humble pie for desert. You were 100% correct and it works perfectly now.

In thinking back, what would have caused the error was me pasting in that code in row 1 instead of the correct row 2. The mistake could have easily been made because in the blank Excel worksheet I used to record the macro I did not have the 1st row header and mistakenly recorded the formula in row 1 vs. row 2, thus causing the 1 row deviation that you pointed out in the formula.

That's why you're the MVP and I'm just a squirrel trying to get a nut! haha.

While I have your attention, I just noticed something new that is a concern. There's one tracking ID in particular that contains non-printable characters. The presence of this information can skew the validity of the tracking information report on the server I upload it to. I know that the CLEAN function can help with this.

Using the code structure and setup above, how can I apply the CLEAN function to column G, where data already exists? I don't want the data to go into a new column, rather, simply replace what's already there. Possible?