Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Option Explicit
Sub Daily_Output()
Dim xInput As Worksheet
Dim xOutput As Worksheet
Dim xLast_Row As Long
Dim xTime As Variant, xDate As Variant
Dim xCell As Range
Dim xRep As String
Dim i As Long
Set xInput = Sheets("Received")
xInput.Activate
xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
xDate = Mid(Cells(1, 1), 6, 10)
xTime = Mid(Cells(1, 1), 25, 8)
Set xOutput = Sheets.Add
Range("A1:E1") = Array("Date", "Time", "Rep", "Invoice#", "Amt")
i = 1
For Each xCell In xInput.Range("A3:A" & xLast_Row)
If xCell <> "" Then xRep = Mid(xCell, 5, 9999)
With xCell
If .Offset(0, 1) <> "" Then
If .Offset(0, 1) <> "Invoice#" Then
i = i + 1
Cells(i, 1) = xDate
Cells(i, 2) = xTime
Cells(i, 3) = xRep
Cells(i, 4) = .Offset(0, 1)
Cells(i, 5) = .Offset(0, 2)
End If
End If
End With
Next
End Sub
Regards,Sub Normalizer()
Dim rg As Range
Dim fl As Filter
Application.ScreenUpdating = False
Columns("A:B").Insert Shift:=xlToRight
Set rg = Range("D1").End(xlDown)
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
Set rg = rg.Offset(0, -3).Resize(, 5)
rg.Columns(1).Value = DateValue(Mid(Range("C1").Value, 6, 10))
rg.Columns(2).Value = TimeValue(Right(Range("C1").Value, 8))
Columns(2).ColumnWidth = 12
rg.Columns(3).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]=""Invoice#"",MID(R[-1]C,5,99),R[-1]C)"
rg.Columns(3).Formula = rg.Columns(3).Value
rg.AutoFilter Field:=4, Criteria1:="=Invoice#", Operator:=xlOr, Criteria2:="="
rg.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
rg.AutoFilter
rg.Cells(1, 1).Resize(1, 3).Value = Array("Date", "Time", "Rep")
Range(Range("A1"), rg.Cells(1, 1).Offset(-1)).EntireRow.Delete
End Sub
A2=INT(VALUE(SUBSTITUTE(SUBSTITUTE(Received!$A$1,"Date:",""),"RunTime:","")))
B2=VALUE(SUBSTITUTE(SUBSTITUTE(Received!$A$1,"Date:",""),"RunTime:",""))-INT(VALUE(SUBSTITUTE(SUBSTITUTE(Received!$A$1,"Date:",""),"RunTime:","")))
C2=SUBSTITUTE(INDEX(Received!A:A,MAX(IF(OFFSET(Received!$A$1,0,0,MAX(SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1)))<>"",ROW(OFFSET(Received!$A$1,0,0,MAX(SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1))))))),"Rep:","",1)
D2=INDEX(Received!B:B,SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1))
E2=INDEX(Received!C:C,SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1))
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.