Hi everyone,
I'm trying to write a code that searches for instances of a string within a cell within a range, and then copies everything between that cell and the next instance of a different string.
Sounds confusing, but essentially I have multiple ranges between a cell containing 'Posts:' and a cell containing 'Logged', and these ranges differ in size, but are always contained between these two cells.
This is what I have so far:
Sub Consolidate()
Dim StrSearch As String
Dim StrSearch2 As String
Dim rng1 As Range
Dim rng2 As Range
Dim int1 As Integer
StrSearch = "Posts:"
StrSearch2 = "Logged"
With Worksheets(1).UsedRange
Set rng1 = .Find(StrSearch, LookIn:=xlValues, LookAt:=xlPart)
Set rng2 = .Find(StrSearch2, LookIn:=xlValues, LookAt:=xlWhole)
SampleCnt = Application.WorksheetFunction.CountIf(Sheets("Raw").Range("A:A"), "Logged")
Do While i < SampleCnt
Set int1 = rng2.Row - rng1.Row
rng1.Activate
Range(ActiveCell, rng1.Offset(int1)).Select
Selection.Copy
Sheets("Output1").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Raw").Select
Set rng1 = .FindNext(rng1)
Set rng2 = .FindNext(rng2)
i = i + 1
Loop
End With
End Sub
But I keep getting a 'Compile Error: invalid qualifier', likely because the 'rng1.Row' output is not being treated as an integer.
Is there a way around this?
Thanks so much in advance for all your help.
Bookmarks