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

 

[ItalianPlatinum]

For the most part it worked I just had to rearrange some things. But overall seems to be yielding the results I expect. Apologies if I couldn't share all information. the extraction from the system is code I don't have access to unfortunately. I am only given the means of extracting i.e Application.Run "'Unpriced.xlsm'!Distribution2", then filters to limit back what the system gives me back.

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
  Application.Run "'Unpriced.xlsm'!Distribution2" ' 'extract from the database
  s = Mid(s, 751) 'run the next iterations
Loop 'stacks the results and end after empty
  
'transfer all data over
  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

 

[Peter_SSs]

.... I just had to rearrange some things.

Seems like this was basically the issue ..

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.

But overall seems to be yielding the results I expect.

Good news! Thanks for confirming.