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
Title | # Comments | Views | Activity |
---|---|---|---|
Data in Rows to be converted into single row | 9 | 40 | |
Excel VLOOKUP. How to return "Not Found" if Item or Qty is missing in Lookup table? | 11 | 38 | |
how to add loop into this VBA | 3 | 29 | |
Excel - DATEDIF error #NUM | 6 | 26 |
Join the community of 500,000 technology professionals and ask your questions.