Copy cells between two found instances of a different strings

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.

yep, that was exactly the problem haha

thanks so much for your help millz!!

Hi alansidman,

the line I get the error with is the

Set int1 = rng2.Row - rng1.Row

the first line of the loop. Which leads me to think that it doesn't like the rng2.Row variables.