satputenandk
05-12-2014, 06:10 AM
Dear Members,
I generate dispatch report daily or alternate day. I have raw data with branch code and document numbers. I am looking for vba code which output From and To document number for each branch & missing document number/numbers. (each document set have 50 leafs)
Please find attached file for more clarification. (I have showed output in separate sheet.)
Any help may appreciated.
Bob Phillips
05-12-2014, 08:07 AM
public Sub CreateOuput()
Const FORMULA_BATCH_50 As String = "=SUMPRODUCT(--(RIGHT('Raw Data'!B<start>:B<end>,2)=""50""))"
Const FORMULA_BATCH_100 As String = "=SUMPRODUCT(--(RIGHT('Raw Data'!B<start>:B<end>,2)=""00""))"
Const FORMULA_MIDDLE_50 As String = "=MATCH(--(LEFT(B<start>,LEN(B<start>)-2)&""50""),B1:B<end>,0)"
Const FORMULA_MIDDLE_100 As String = "=MATCH(--(LEFT(B<start>,LEN(B<start>)-2)&""00""),B1:B<end>,0)"
Const FORMULA_MISSING As String = "=MIN(IF(NOT(ISNUMBER(MATCH(ROW(INDIRECT(RIGHT(B<start>,4)&"":""&RIGHT(B<end>,4))),--(RIGHT(B<start>:B<end>,4)),0)))," & _
"--(LEFT(B<start>,LEN(B<start>)-4)&TEXT(ROW(INDIRECT(RIGHT(B<start>,4)&"":""&RIGHT(B<end>,4))),""0000""))))"
Dim ws As Worksheet
Dim branch As String
Dim firstTHC As Long
Dim middleTHC As Long
Dim lastTHC As Long
Dim missingTHC As Long
Dim nextrow As Long
Dim lastrow As Long
Dim i As Long
Application.DisplayAlerts = False
Worksheets("Output").Delete
Application.DisplayAlerts = True
With ActiveSheet
Set ws = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbo ok.Worksheets.Count))
ws.Name = "Output"
ws.Range("A1:D1").Value = Array("Bkg Branch", "From", "To", "Missing")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextrow = 2
branch = .Range("A2").Value
For i = 2 To lastrow + 1
If .Cells(i, "A").Value <> "branch" Then
End If
firstTHC = i
Do
i = i + 1
Loop Until .Cells(i, "A").Value <> branch Or i > lastrow + 1
lastTHC = i - 1
If .Evaluate(Replace(Replace(FORMULA_BATCH_50, "<start>", firstTHC), "<end>", lastTHC)) Then
middleTHC = .Evaluate(Replace(Replace(FORMULA_MIDDLE_50, "<start>", firstTHC), "<end>", lastTHC))
ElseIf .Evaluate(Replace(Replace(FORMULA_BATCH_100, "<start>", firstTHC), "<end>", lastTHC)) Then
middleTHC = .Evaluate(Replace(Replace(FORMULA_MIDDLE_100, "<start>", firstTHC), "<end>", lastTHC))
Else
middleTHC = 0
End If
If middleTHC > 0 Then
.Cells(firstTHC, "A").Resize(, 2).Copy ws.Cells(nextrow, "A")
ws.Cells(nextrow, "C").Value = .Cells(middleTHC, "B").Value
.Range("F1").FormulaArray = Replace(Replace(FORMULA_MISSING, "<start>", firstTHC), "<end>", middleTHC)
If .Range("F1").Value > 0 Then ws.Cells(nextrow, "D").Value = .Range("F1").Value
nextrow = nextrow + 1
.Cells(middleTHC + 1, "A").Resize(, 2).Copy ws.Cells(nextrow, "A")
ws.Cells(nextrow, "C").Value = .Cells(lastTHC, "B").Value
.Range("F1").FormulaArray = Replace(Replace(FORMULA_MISSING, "<start>", middleTHC), "<end>", lastTHC)
If .Range("F1").Value > 0 Then ws.Cells(nextrow, "D").Value = .Range("F1").Value
nextrow = nextrow + 1
branch = .Cells(i, "A").Value
Else
.Cells(firstTHC, "A").Resize(, 2).Copy ws.Cells(nextrow, "A")
ws.Cells(nextrow, "C").Value = .Cells(lastTHC, "B").Value
.Range("F1").FormulaArray = Replace(Replace(FORMULA_MISSING, "<start>", firstTHC), "<end>", lastTHC)
If .Range("F1").Value > 0 Then ws.Cells(nextrow, "D").Value = .Range("F1").Value
nextrow = nextrow + 1
branch = .Cells(i, "A").Value
End If
If i < lastrow Then i = i - 1
Next i
.Range("F1").ClearContents
End With
End Sub
satputenandk
05-13-2014, 11:16 PM
Thank You sir,
You are genius. you code working perfect. :clap2:
Thanks again for giving your valuable time.
satputenandk
05-13-2014, 11:57 PM
Dear sir,
Here I am reopening thread because, When I use large data the code gives Run-time error '13': Type mismatch error on below line
middleTHC = .Evaluate(Replace(Replace(FORMULA_MIDDLE_100, "<start>", firstTHC), "<end>", lastTHC))
I have attached file with large data
Bob Phillips
05-14-2014, 12:46 AM
I can correct that first issue, but there is a more serious problem later on. Line #53 THC is 1677049, line #54 THC is 9155499. I thought you had said that gaps would just be an increment of 1, and I coded such. I am not catering for a series 7,478,450 missing THC numbers.
satputenandk
05-15-2014, 12:58 AM
sorry sir,:( I forgot one point that any branch might be contain more that one series that means Line #53 and Line # 54 is different series. (If gap is more that 50 then consider it new series).
My English is not well.:sad2:
thanks
satputenandk
05-16-2014, 09:49 PM
Any help may appreciated.
satputenandk
05-20-2014, 01:37 AM
Any Master have solution for this ??
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.