VBA to check string then apply iterations

[ItalianPlatinum]

My problem statement: I have code that extracts from a core database. That system has a limit for its filter of 750 characters. So looking for a way to 1) check if the limit is breached and then 2) if breached two split it into multiple iterations so its below the limit. For example:

s would look like this 123456789:987654321:987654111 and etc. unique identifier (9 characters) each separated by a " : "

'get the string for filter
With Sheets("Data")
  s = Join(Application.Transpose(.Range("B11", .Range("B" & rows.count).End(xlUp)).Value), ":")
End With

'apply the strings to prep for extraction
With Sheets("Extract System")
    .Activate
    .AutoFilterMode = False
    .Range("DATE") = RUNDATE4
    .Range("VW") = "1"
    .Range("Filter") = "C"
    .Range("Operation") = "="
    .Range("VALUE") = s 'ensure the 750 character isn't breached
End With

'extract from the system and copy data over to the main sheet
Application.Run "'Unpriced.xlsm'!Distribution2" '
With Sheets("Extract System")
    rws = .Range("A11:B11").End(xlDown).row - 10
  Sheets("MAIN").Range("I8").Resize(rws, 2).Value = .Range("A11:B11").Resize(rws).Value
End With

 

[PeteWright]

Hi @ItalianPlatinum
From what you posted it is not clear how the data should be processed. Could you post an example of the desired output?
Besides, your VBA code is just a fragment and you didn't post the referenced datasets.

 

[Peter_SSs]

Try something like this. I may possibly have the bottom 2 red lines in the wrong place but they should go at the point where you have finished doing what you want with the first section of the long string and return to do it all again with the next section. Added or changed lines are shown in red.
You may also need to do something different in that blue section but as @PeteWright has mentioned we don't know exactly what you are trying to do and we don't have any data or expected result to work from/towards.

With Sheets("Data")
  s = Join(Application.Transpose(.Range("B11", .Range("B" & Rows.Count).End(xlUp)).Value), ":")
End With

Do Until Len(s) = 0
  'apply the strings to prep for extraction
  With Sheets("Extract System")
      .Activate
      .AutoFilterMode = False
      .Range("DATE") = RUNDATE4
      .Range("VW") = "1"
      .Range("Filter") = "C"
      .Range("Operation") = "="
      .Range("VALUE") = Left(s, 749) 'ensure the 750 character isn't breached
  End With
 
  'extract from the system and copy data over to the main sheet
  Application.Run "'Unpriced.xlsm'!Distribution2" '
  With Sheets("Extract System")
      rws = .Range("A11:B11").End(xlDown).Row - 10
    Sheets("MAIN").Range("I8").Resize(rws, 2).Value = .Range("A11:B11").Resize(rws).Value
  End With
  s = Mid(s, 751)
Loop