VBA Text Criteria Builder-CodePudding

Essentially I am trying to build a text criteria builder to use on other systems. Simply put I have an excel dashboard where you select another excel worksheet with bulk data in it containing first names, last names, DOB, gender and other material.

I want to be able to create a VBA button to take the important details like first and last name and DOB per person on each row and create a system where it builds a criteria. For example, once the data is in the worksheet, sorted and formatted, the system prints out a "copy and paste" system with criteria like "JOHN" AND "DOE" AND "04JUL1993". Then adds in a OR and starts the next persons details in the same format. So hypothetically it would repeat like this: "JOHN" AND "DUNNY" AND "03AUG1993" OR "JOHN" AND "DOE" AND "04JUL1993" OR "ANDREW" AND "JENKINS" AND "17FEB1973" OR "JANE" AND "DOE" AND "30MAR2000".

The aim is to take the details of around 10 people at a time, build the "text criteria" in the above format ready to copy and paste all 10 people in the AND OR format and paste it into another system I use for work that searches records based on those people. the system that takes that information has a limited amount of characters per search, so doing 10 people per search is the safe option.

Ideally I'd like to have a system that builds the criteria regardless of how many people are in the dataset (there is never anymore than 500 people in the data list) and create criteria ready to copy and paste in batches of around 10, search in the above format, yield results in the other system, paste the next 10 people and so on so fourth. It's difficult to explain and I am unsure if I'm just confusing people or making sense.

I haven't really got much so far, other than the dashboard with the ability to open another worksheet and import it's data into the active dashboard.

CodePudding user response：

With the help of https://stackoverflow.com/a/60896244/692098 copying things to clipboard of strings you build in vba, since it seems like you don't want to adjust worksheets and copy from there, it should be fairly easy.

Sub criteriaBuilder()
    Dim Lastrow As Long, cRow As Long
    Dim wbD As Workbook
    Dim ws As Worksheet
    Dim sCopy As String
    
    Set wbD = Workbooks("YourData.xlsx") 'adjust to your Data file
    Set ws = wbD.Worksheets("Data") 'and corresponding sheet
    
    Lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To Lastrow 'assuming you have a header
        sCopy = sCopy & ws.Range("A" & i).Value & " AND "
        sCopy = sCopy & ws.Range("B" & i).Value & " AND "
        If i Mod 10 = 1 Or i = Lastrow Then 'Copy every ten lines or when lastrow is reached
            sCopy = sCopy & ws.Range("C" & i).Value
            Clipboard sCopy
            MsgBox ("The 10 are copied, paste them in your other program and then hit OK to continue")
            sCopy = ""
        Else
            sCopy = sCopy & ws.Range("C" & i).Value & " OR "
        End If
    Next i
    MsgBox ("No more people left")
End Sub

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .GetData("text")
        End Select
    End With
    End With
End Function

Is this what you had in mind?

Ps: Try showing us what you have next time and how the sheet that you're working with looks like.