VBA that will insert column D with a Yes when the customer places an order that is within 24 hours of the their previous

dtopinka

New Member
Joined
Nov 2, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
we charge customers for abusing our delivery service. what i need help with is creating the code that will insert a "Yes" in Column D when a customer (name column) requests an order within 24 hours of his last. Example would be rows 7 and 8 should both have a Yes in their D column cell. Thank you in advance!

1604352066247.png
 
And this code populates column E while excluding anything captured in column D:

VBA Code:
Sub timenamecompare4()
Dim lr, i As Long

lr = Cells(Rows.Count, "H").End(xlUp).Row

For i = lr To 2 Step -1
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i, 4).Value = "Yes"
    End If
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) > 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 2880 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i, 5).Value = "Yes"
    End If
    
Next
End Sub
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Alright, give this a try

VBA Code:
Option Explicit

Sub timenamecompare3()
Dim lr, i As Long

lr = Cells(Rows.Count, "H").End(xlUp).Row

For i = lr To 2 Step -1

If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 1440 And _
Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
    Cells(i, 4).Value = "Yes"
    End If
Next
End Sub
that is really close.. the yes value needs to be in the row that corresponds to the orders that were placed after the initial order. basically we charge them for every order that is requested within 24 hours of another request.

we also do the same thing for some but the period is 48 hours.. can you tell me what i switch in the code to reflect that change.

thank you!!
 
Upvote 0
And this code populates column E while excluding anything captured in column D:

VBA Code:
Sub timenamecompare4()
Dim lr, i As Long

lr = Cells(Rows.Count, "H").End(xlUp).Row

For i = lr To 2 Step -1
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i, 4).Value = "Yes"
    End If
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) > 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 2880 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i, 5).Value = "Yes"
    End If
   
Next
End Sub
lets stick with the 24 hour code for now. this one still does not place the value in the correct row. example Allgood, Margaret, the yesses should be in rows 10 and 11.
 
Upvote 0
VBA Code:
Sub timenamecompare5()
Dim lr, i As Long

lr = Cells(Rows.Count, "H").End(xlUp).Row

For i = lr To 2 Step -1
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i + 1, 4).Value = "Yes"
    End If
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) > 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 2880 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i + 1, 5).Value = "Yes"
    End If
    
Next
End Sub
 
Upvote 0
Solution
VBA Code:
Sub timenamecompare5()
Dim lr, i As Long

lr = Cells(Rows.Count, "H").End(xlUp).Row

For i = lr To 2 Step -1
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i + 1, 4).Value = "Yes"
    End If
    If Cells(i, 18).Value = Cells(i + 1, 18).Value And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) > 1440 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) < 2880 And _
        Abs(DateDiff("n", Cells(i, 8).Value, Cells(1 + i, 8).Value)) <> 0 Then
        Cells(i + 1, 5).Value = "Yes"
    End If
   
Next
End Sub
I believe that is it! Thank you thank you thank you!!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top