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.
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.
HOw can i find those rows which contain the letters "DR" in each of the VCH sub journals?
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
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.
HOw can i find those rows which contain the letters "DR" in each of the VCH sub journals?
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
Code:
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
/code]
