VBA add column for date conversion and copy paste

I have to change a date value by using int().
So I need to add steps to the code before it pivots.

With Filtered_Data sheet
1. always will be column L
2. Don't know the number of rows
3. Don't know where column Date Shipped will be in.
4. need to copy formula =INT("Date Shipped" row 2)
5. Copy down all the rows.
6. Paste to "Date Shipped" column over the existing data

Can anyone help??

Sub GroundRating()
    Application.ScreenUpdating = False
    Dim NextCol As Long, lastRow As Long
    Dim lngLastCol As Long
 
    Dim HRow As Range
    Dim cl As Object
    Dim RWt, Zn As String
    Set HRow = Range("A1", Range("A1").End(xlToRight))
    Dim lngRow As Long

    

    For Each cl In HRow
        Select Case cl.Value
            Case "Rated Weight"
                RWt = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
            Case "Zone"
                Zn = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
        End Select
    Next cl
    
    With Sheets("Filtered_Data")
        lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

        'For lngRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 2 Step -1
            'If .Cells(lngRow, lngLastCol) < Sheets("Details").Range("A3") Then
              '  .Cells(lngRow, lngLastCol).EntireRow.Delete
           ' End If
        'Next
        
        NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(1, NextCol) = "Ground Rates"
        .Range(.Cells(2, NextCol), .Cells(lastRow, NextCol)).Formula = _
            "=IF(" & RWt & "2>=150,(VLOOKUP(" & RWt & "2,GR," & Zn & "2,TRUE)/150)*" & RWt & _
            "2,VLOOKUP(" & RWt & "2,GR," & Zn & "2,FALSE))"
    End With
    
    
    With Sheets("Filtered_Data")
    NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    .Cells(1, NextCol) = "Average Package Weight"
    .Range(.Cells(2, NextCol), .Cells(lastRow, NextCol)).Formula = _
"=Roundup(AVERAGEIFS(INDEX($1:$1000,,MATCH(""Rated Weight"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Pay Type"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Pay Type"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Date Shipped"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Date Shipped"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Origin Zip"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Origin Zip"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Recipient Zip"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Recipient Zip"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Street Address"",$1:$1,0)),INDEX($1:$1000,,MATCH(""Street Address"",$1:$1,0)),INDEX($1:$1000,,MATCH(""State"",$1:$1,0)),INDEX($1:$1000,,MATCH(""State"",$1:$1,0)),INDEX($1:$1000,,MATCH(""City"",$1:$1,0)),INDEX($1:$1000,,MATCH(""City"",$1:$1,0))),0)"
End With



''''''Enter date conversion here

  Set rCrit1 = Sheets("Details").Range("A3")
     With Filtered_Data

            .AutoFilterMode = False

            .Range("A1:K1").AutoFilter

            .Range("A1:K1").AutoFilter Field:=11, Criteria1:="<" & rCrit1

    End With






End Sub

Open in new window

MWT-TOOL-v10.4.xlsm
Euro5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Euro5Author Commented:
I have the column added, but when I try to use the variable for the formula, it doesn't work.
The following should give me the formula =int(C2) -----  just an example, whatever column "Ship Date" is in.  It results in a NAME? error

        .Range(.Cells(2, NextCol), .Cells(lastRow, NextCol)).Formula = "=int(Dsh)"

Sub DateConverstion()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim rng As Range

With Sheets("Filtered_Data")

    Dim NextCol As Long, lastRow As Long
    Dim HRow As Range
    Dim cl As Object
    Dim DSh, Zn As String
    
    Set HRow = Range("A1", Range("A1").End(xlToRight))

    For Each cl In HRow
        Select Case cl.Value
            Case "Date Shipped"
                DSh = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
            Case "Zone"
                Zn = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
        End Select
    Next cl
    
    With Sheets("Filtered_Data")
        NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(1, NextCol) = "Dataconversion"
        .Range(.Cells(2, NextCol), .Cells(lastRow, NextCol)).Formula = "=int(Dsh)"


    End With
End With

End Sub

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this....

Sub DateConverstion()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim rng As Range

With Sheets("Filtered_Data")

    Dim NextCol As Long, lastRow As Long
    Dim HRow As Range
    Dim cl As Range
    Dim DSh As Long, Zn As String
    
    Set HRow = Range("A1", Range("A1").End(xlToRight))

    For Each cl In HRow
        Select Case cl.Value
            Case "Date Shipped"
                DSh = cl.Column + 1
            Case "Zone"
                Zn = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
        End Select
    Next cl
    Columns(DSh).Insert
    .Cells(1, DSh) = .Cells(1, DSh - 1)
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    With .Range(.Cells(2, DSh), .Cells(lastRow, DSh))
        .FormulaR1C1 = "=int(RC[-1])"
        .Value = .Value
        .NumberFormat = "mm/dd/yyyy"
    End With
    .Columns(DSh - 1).Delete
End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
WOW!!!  Thank you so much!! that was perfect.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad it worked for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.