ajm
10-26-2011, 06:58 PM
I am trying to create a macro that will help our accounts team find erroneous postings to a particular journal. the data to search is all contained in Column A and appears in a fairly standardised format.
I have posted this same query at http://www.mrexcel.com/forum/showthread.php?t=585283 with no responses so am desperate for help.
first line is "VCH", for voucher
second is alpha and numeric string for description
third line is "JNL" or journal
then there are several lines of sub journal description which generally consist of a exact breakdown of the cost element at VCH.
then VCH again and away you go. VCH appears only at the start of each group of lines relating to that one transaction.
for example, the 14 lines below are all from column A in my worksheet. I want to capture the Voucher detail for lines 9-14 as this voucher contains postings to an unidentified journal starting with DR.
10/10/11 32698 /fms/interfaces/prod/unibis Account Payable Voucher
BCH
CHT054 4 2012 09/10/11 "134465.55" "QFC" no
VCH
26392 "0QF" "11684225" 02/10/11 12034.51 0 0 1203.45 ? 0 0 ? 0 ? "Accounts Payable Voucher 10/10/11" "AUD" 0 "Y"
JNL
"" "9699900792" 1203.45 "" "" "" "T"
"" "QF99900650" 12034.51 "11684225" "Accrued L/Haul Road" "GST" ""
VCH
35728 "0QF" "11666879" 04/10/11 604.2 0 0 60.42 ? 0 0 ? 0 ? "Accounts Payable Voucher 10/10/11" "AUD" 0 "Y"
JNL
"" "9699900792" 60.42 "" "" "" "T"
"" "DR99900650" 34.2 "11666879" "Accrued L/Haul Road" "GST" ""
"" "DR99900650" 570 "11666879" "35728-MEL34/WAG34" "GST" ""
VCH
How can i find those rows which contain the letters "DR" in each of the VCH records?
i found the following piece of code (and my apologies to its author as i can't find the page it came from now) which finds the first instance of a string and then the last instance and copies all the rows in between into an adjacent column. I have been trying to find a way to include the "if it contains DR bit, in between the first and last instance of the search terms, then copy. and as the Vouchers don't all have the same number of postings to them, its hard to say search every n number of lines etc..
Sub x()
Dim rngTemp As Range
Dim rngFind As Range
Dim rngFirst As Range
Dim rngLast As Range
Dim strFirstAddress As String
Dim lngRow As Long
lngRow = Range("A" & Rows.Count).End(xlUp).Row + 1
With Range("A:A")
Set rngFind = .Find("VCH*", .Cells(lngRow, 1), LookIn:=xlValues, lookat:=xlPart)
MsgBox rngFind
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngFirst = rngFind.Offset(1)
Set rngTemp = rngFirst
Set rngFind = .FindNext(rngFind)
Do While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
Set rngLast = rngFind
Set rngTemp = Union(rngTemp, Range(rngFirst, rngLast))
Set rngFirst = rngFind
Set rngFind = .FindNext(rngFind)
Loop
End If
End With
If Not rngTemp Is Nothing Then rngTemp.Copy Range("B1")
End Sub
I have posted this same query at http://www.mrexcel.com/forum/showthread.php?t=585283 with no responses so am desperate for help.
first line is "VCH", for voucher
second is alpha and numeric string for description
third line is "JNL" or journal
then there are several lines of sub journal description which generally consist of a exact breakdown of the cost element at VCH.
then VCH again and away you go. VCH appears only at the start of each group of lines relating to that one transaction.
for example, the 14 lines below are all from column A in my worksheet. I want to capture the Voucher detail for lines 9-14 as this voucher contains postings to an unidentified journal starting with DR.
10/10/11 32698 /fms/interfaces/prod/unibis Account Payable Voucher
BCH
CHT054 4 2012 09/10/11 "134465.55" "QFC" no
VCH
26392 "0QF" "11684225" 02/10/11 12034.51 0 0 1203.45 ? 0 0 ? 0 ? "Accounts Payable Voucher 10/10/11" "AUD" 0 "Y"
JNL
"" "9699900792" 1203.45 "" "" "" "T"
"" "QF99900650" 12034.51 "11684225" "Accrued L/Haul Road" "GST" ""
VCH
35728 "0QF" "11666879" 04/10/11 604.2 0 0 60.42 ? 0 0 ? 0 ? "Accounts Payable Voucher 10/10/11" "AUD" 0 "Y"
JNL
"" "9699900792" 60.42 "" "" "" "T"
"" "DR99900650" 34.2 "11666879" "Accrued L/Haul Road" "GST" ""
"" "DR99900650" 570 "11666879" "35728-MEL34/WAG34" "GST" ""
VCH
How can i find those rows which contain the letters "DR" in each of the VCH records?
i found the following piece of code (and my apologies to its author as i can't find the page it came from now) which finds the first instance of a string and then the last instance and copies all the rows in between into an adjacent column. I have been trying to find a way to include the "if it contains DR bit, in between the first and last instance of the search terms, then copy. and as the Vouchers don't all have the same number of postings to them, its hard to say search every n number of lines etc..
Sub x()
Dim rngTemp As Range
Dim rngFind As Range
Dim rngFirst As Range
Dim rngLast As Range
Dim strFirstAddress As String
Dim lngRow As Long
lngRow = Range("A" & Rows.Count).End(xlUp).Row + 1
With Range("A:A")
Set rngFind = .Find("VCH*", .Cells(lngRow, 1), LookIn:=xlValues, lookat:=xlPart)
MsgBox rngFind
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngFirst = rngFind.Offset(1)
Set rngTemp = rngFirst
Set rngFind = .FindNext(rngFind)
Do While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
Set rngLast = rngFind
Set rngTemp = Union(rngTemp, Range(rngFirst, rngLast))
Set rngFirst = rngFind
Set rngFind = .FindNext(rngFind)
Loop
End If
End With
If Not rngTemp Is Nothing Then rngTemp.Copy Range("B1")
End Sub