Using pattern searches with a variable to find the correct values

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
918
Office Version
  1. 365
Platform
  1. Windows
As stated. I tried to make this as short as possible, but the code block I included makes it a little long. At the
end of the day, the code should be able to find all values of a phrase similar, but this code doesn't:

4 translations of Gen. 1:29:

Behold, I have given you every herb bearing seed (KJV)
Then God said, "I give you every seed-bearing plant (NIV)
I have given you every plant yielding seed (NASB)
Behold, I have given you every plant yielding seed (RSV)

typed in a textbox: "seed bearing plant"

I want the code to be able to pull up all occurrences verses if I type in what I remember the verse says without being forced to type in exactly the phrase with spaces, commas, dashes, etc., and still have the code pick them up.

When I type in anything like "seed bearing plant" the code yields no results. How can that be? I don't know how to construct a pattern search with variable operators, i.e., using "like" or similar to ensure any similar phrase will be found every time:

This code does not work every time:
Code:
Sheets("VALSFOUND").UsedRange.ClearContents
Dim lastrow As Long
Dim X As String, c As Range, rw As Long, firstAddress As String
Dim Y As String
X = cbav1.Value 'this is a combobox value
Dim rngSrc As Range
With Worksheets("SOURCE") 'the sheet the code searches
Set rngSrc = .Range("C1", .Cells(Rows.count, "C").End(xlUp)) 'col C is the NASB column.  Sht is divided into 4 cols: KJV = A, NIV = B, NASB = C, RSV = D
End With
With Worksheets("SOURCE") 'default NASB
Set c = rngSrc.FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
rw = 1
firstAddress = c.Address
Do
If InStr(1, c.Value, Y, vbTextCompare) > 0 Then
.Range(.Cells(c.Row, 2), .Cells(c.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw) '
rw = rw + 1
End If
Set c = rngSrc.FindNext(c)
Loop While c.Address <> firstAddress
lastrow = Sheets("VALSFOUND").Range("A" & Rows.count).End(xlUp).Row
Else
MsgBox "value not found"
End If
Sheets("VALSFOUND").Range("H1").Value = Me.cbav1.Value
Sheets("VALSFOUND").Range("I1").Value = Me.TextBox4.Value
End With
rowno = Sheets("VALSFOUND").Range("A1").End(xlDown).Row
Sheets("VALSFOUND").Range("H1").Value = rowno 'total rows found in search
Sheets("VALSFOUND").Range("I1").Value = X 'value to find, i.e.,, "last days"
ListBox1.ListIndex = 0
totrows = lastrow
Dim firstrow As Integer, myrange As Range
Set myrange = Sheets("VALSFOUND").Range("A1")
If myrange <> " " Then
firstrow = myrange.Row
Else
MsgBox "First row is blank"
End If
Sheets("VALSFOUND").Range("H1").Value = lastrow
Me.totrows.Value = lastrow
Me.rowno.Value = ListBox1.ListIndex + 1
Me.TextBox1.SetFocus
Me.TextBox1.CurLine = 0
Me.TextBox1.SelStart = 0
Sheets("VALSFOUND").Range("i1").Value = Me.cbav1.Value
Sheets("VALSFOUND").Range("J1").Value = Me.TextBox4.Value
End Sub

Thanks to anyone for help.

cr
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I'm having trouble understanding how your data is on the sheet, how you have the textbox control, etc.

In your code you have a variable 'Y' that you declare at the beginning, but you do not assign any value to it.
So you are comparing c.Value against a void.
1730827281058.png

Better help me figure out what you have on the sheet, put about 5 or 6 records.

I assume you have a userform with a textbox.
So you capture something in the textbox and want to search in column C.

Continuing with the 5 or 6 records, which of them should match, and most importantly, where do you want the results, on the sheet in a listbox?
It would also be ideal if you put a screenshot of how you want the final result.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --
 
Upvote 0
I'm having trouble understanding how your data is on the sheet, how you have the textbox control, etc.

In your code you have a variable 'Y' that you declare at the beginning, but you do not assign any value to it.
So you are comparing c.Value against a void.

Better help me figure out what you have on the sheet, put about 5 or 6 records.

I assume you have a userform with a textbox.
So you capture something in the textbox and want to search in column C.

Continuing with the 5 or 6 records, which of them should match, and most importantly, where do you want the results, on the sheet in a listbox?
It would also be ideal if you put a screenshot of how you want the final result.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --
You are right - this code is all messed up. I failed to notice I referred to two variables to try to get one result. The correct way
this code should work for any word or phrase is like this:

Code:
"appointed" AND "time"
occurs 63 times in 29 verses in the NASB20, including 26 exact phrases shown first.
Below are the exact phrase matches. ( I only included this first occurrence)
“Is anything too ]difficult for the LORD? At the appointed time I will return to you, [fn]at this time next year, and Sarah will have a son.”
(Gen 18:14)

Results are copied to a sheet and displayed in a userform. To work correctly the word, words or phrase should appear in the
cell texts being searched. The code above uses the AND operator and seems to work correctly. Copied from Blue Letter Bible search.
Thanks for helping. I totally missed what you pointed out.

cr
 
Upvote 0
Hi Charles,

The following four steps are a manual way to do what you're after that could be converted to code I'm sure:
  1. Go to a blank (unused) column and put in this formula in Row 2 (note the verses are in Col. C for me - you may need to change to suit your tab layout):
    =SUM(LEN(C2)-LEN(SUBSTITUTE(C2,"appointed","")))/LEN("appointed")
  2. Go to the next blank (unused) column and put this formula in Row 2:
    =SUM(LEN(C2)-LEN(SUBSTITUTE(C2,"time","")))/LEN("time")
  3. Copy these formulas down to the last row
  4. Filter both formula columns by unselecting the zeros
You should be left with the required number of verses and their count by summing the two formula columns entered above.

The concept is the same for extra words - you just add another column for each word i.e. for "seed bearing plant" you would have three formula (helper) columns.

Hope that helps.

Regards,

Robert
 
Upvote 0
Here's a macro that seems to work ok for me (you will need to align your data with what I've written on my test data of course):

VBA Code:
Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim i As Long, j As Long, k As Long
    Dim strText As String
    Dim lngNumOfVerses As Long, lngWordCount As Long
    Dim varItem As Variant, varArr() As Variant
    Dim rngCell As Range
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Set ws = ThisWorkbook.Sheets("SOURCE")
    ws.Calculate
    On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0
    With ws
        .Columns.EntireColumn.Hidden = False
        .Rows.EntireRow.Hidden = False
    End With
    strText = "appointed time"
    'strText = "seed bearing plant" '**Note for 'KJV' this needs to be 'herb bearing seed' to return Genesis 1:29
    'strText = "herb bearing seed"
    
    For Each rngCell In ws.Range("C2:C" & ws.Range("C" & Rows.Count).End(xlUp).Row) 'Col. C has the verse text. Change to suit.
        For Each varItem In Split(strText, " ")
            If Len(rngCell) - Len(Replace(rngCell, CStr(varItem), "")) > 0 Then
                i = i + 1
                j = j + UBound(Split(rngCell, CStr(varItem)))
            End If
        Next varItem
        If i = UBound(Split(strText, " ")) + 1 Then
            lngNumOfVerses = lngNumOfVerses + 1
            lngWordCount = lngWordCount + j
            ReDim Preserve varArr(1 To 2, k)
            varArr(1, k) = rngCell.Offset(0, -1).Value 'Book and chapter reference is in Col. B. Change to suit.
            varArr(2, k) = rngCell.Value 'Verse
            k = k + 1
        End If
        i = 0: j = 0
    Next rngCell
    
    'Output
    If k = 0 Then
        MsgBox "No verses where found containing the text """ & strText & """.", vbExclamation
    Else
        Set ws = ThisWorkbook.Sheets("VALSFOUND") 'Refer screen shot I've included for the layout I used in the 'VALSFOUND' tab in my workbook. Change to suit your needs.
        With ws
            On Error Resume Next
                ws.ShowAllData
                .Columns.EntireColumn.Hidden = False
                .Rows.EntireRow.Hidden = False
                j = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            On Error GoTo 0
            If j >= 6 Then
                .Range("A6:A" & j).EntireRow.ClearContents
            End If
            .Range("B1").Value = strText
            .Range("B2").Value = lngNumOfVerses
            .Range("B3").Value = lngWordCount
            .Range("A6").Resize(k, 2).Value = WorksheetFunction.Transpose(varArr)
        End With
        MsgBox "Results have now been outputted.", vbInformation
    End If
    
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Thanks,

Robert
 

Attachments

  • Trebor76 layout of VALSFOUND tab.jpg
    Trebor76 layout of VALSFOUND tab.jpg
    30.2 KB · Views: 4
Upvote 0
Here's a macro that seems to work ok for me (you will need to align your data with what I've written on my test data of course):

VBA Code:
Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim i As Long, j As Long, k As Long
    Dim strText As String
    Dim lngNumOfVerses As Long, lngWordCount As Long
    Dim varItem As Variant, varArr() As Variant
    Dim rngCell As Range
   
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
   
    Set ws = ThisWorkbook.Sheets("SOURCE")
    ws.Calculate
    On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0
    With ws
        .Columns.EntireColumn.Hidden = False
        .Rows.EntireRow.Hidden = False
    End With
    strText = "appointed time"
    'strText = "seed bearing plant" '**Note for 'KJV' this needs to be 'herb bearing seed' to return Genesis 1:29
    'strText = "herb bearing seed"
   
    For Each rngCell In ws.Range("C2:C" & ws.Range("C" & Rows.Count).End(xlUp).Row) 'Col. C has the verse text. Change to suit.
        For Each varItem In Split(strText, " ")
            If Len(rngCell) - Len(Replace(rngCell, CStr(varItem), "")) > 0 Then
                i = i + 1
                j = j + UBound(Split(rngCell, CStr(varItem)))
            End If
        Next varItem
        If i = UBound(Split(strText, " ")) + 1 Then
            lngNumOfVerses = lngNumOfVerses + 1
            lngWordCount = lngWordCount + j
            ReDim Preserve varArr(1 To 2, k)
            varArr(1, k) = rngCell.Offset(0, -1).Value 'Book and chapter reference is in Col. B. Change to suit.
            varArr(2, k) = rngCell.Value 'Verse
            k = k + 1
        End If
        i = 0: j = 0
    Next rngCell
   
    'Output
    If k = 0 Then
        MsgBox "No verses where found containing the text """ & strText & """.", vbExclamation
    Else
        Set ws = ThisWorkbook.Sheets("VALSFOUND") 'Refer screen shot I've included for the layout I used in the 'VALSFOUND' tab in my workbook. Change to suit your needs.
        With ws
            On Error Resume Next
                ws.ShowAllData
                .Columns.EntireColumn.Hidden = False
                .Rows.EntireRow.Hidden = False
                j = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            On Error GoTo 0
            If j >= 6 Then
                .Range("A6:A" & j).EntireRow.ClearContents
            End If
            .Range("B1").Value = strText
            .Range("B2").Value = lngNumOfVerses
            .Range("B3").Value = lngWordCount
            .Range("A6").Resize(k, 2).Value = WorksheetFunction.Transpose(varArr)
        End With
        MsgBox "Results have now been outputted.", vbInformation
    End If
   
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Thanks,

Robert
Hi Robert - thanks for jumping in on this. Just getting to study this now. Want to fully understand, and not just copy/paste.
Will get to you soon. My thinking is, if online Bible apps like Blue Letter Bible or traditional Bible apps like Logos can write correct code to do this
correctly written Excel VBA code should be able to also, with the tools VBA has, i.e. operators integrated with variable pattern searches, etc.
Again, thanks, cr Kingwood, Tx.
 
Upvote 0
Results are copied to a sheet and displayed in a userform.
Well it's a long road, but I'm going to explain it step by step.

Check out my next design:

1. In the "SOURCE" sheet, your data as follows:
varios 08nov2024.xlsm
C
1VERSES
2Behold, I have given you every herb bearing seed (KJV)
3Then God said, "I give you every seed-bearing plant (NIV)
4I have given you every plant yielding seed (NASB)
5Behold, I have given you every plant yielding seed (RSV)
6Is anything too hard for the Lord? I will return to you at the appointed time next year, and Sarah will have a son (NIV)
7Is anything too difficult for the Lord? At the appointed time I will return to you, at this time next year, and Sarah will have a son (NASB)
SOURCE



2. In the "VALSFOUND" sheet, put the following structure:
varios 08nov2024.xlsm
AB
1Version
2Phrase
3Occurs
4Verses
5Exact
6
7Below phrase matches
8
9
VALSFOUND


3. In the userform, a combobox1 for the versions, a textbox1 to write the phrases, a listbox1 for the values found, a listbox2 for the statistics:
1731080585489.png

4. Operation:
- In the code I am loading 4 versions in combobox1: "KJV", "NIV", "NASB" and "RSV":
1731080848801.png

- Then you can choose a version and the code will automatically show all the verses that belong to that version, since according to your initial example, the verses have the version in parentheses, example: (NASB)
1731080948533.png
- The counters will be shown in the statistics box. You didn't explain how to count, so I did my own calculations hoping to guess.

- Then you can capture the phrase in the textbox, the code will automatically search for the phrase in the version selected in the combo:
1731081211102.png

- Or you can search for the phrase in all versions, leaving the combo empty:
1731081354685.png

- At the same time the code will put the results in the VALSFOUND sheet:
1731081484618.png
😇

Put all of the following code in your userform:
VBA Code:
Dim a As Variant

Private Sub ComboBox1_Change()
  Call Filter_Data
End Sub

Private Sub TextBox1_Change()
  Call Filter_Data
End Sub

Sub Filter_Data()
  Dim arr As Variant, b As Variant, itm As Variant
  Dim i As Long, k As Long, n As Long, m As Long
  Dim nOccurs As Long, nVerses As Long, nExact As Long
  
  ReDim b(1 To UBound(a, 1), 1 To 1)
  If ComboBox1.ListIndex > -1 Then
    arr = Split(TextBox1.Text & " (" & ComboBox1.Value & ")", " ")
  Else
    arr = Split(TextBox1.Text, " ")
  End If
  n = UBound(arr) + 1
  For i = 1 To UBound(a)
    m = 0
    For Each itm In arr
      If a(i, 1) Like "*" & itm & "*" Then
        nOccurs = nOccurs + 1
        m = m + 1
      End If
    Next
    
    If m = n Then
      nVerses = nVerses + 1
      b(nVerses, 1) = a(i, 1)
    End If
    
    If ComboBox1.ListIndex > -1 Then
      If a(i, 1) Like "*" & TextBox1.Value & "*" & "(" & ComboBox1.Value & ")" & "*" Then
        nExact = nExact + 1
      End If
    Else
      If a(i, 1) Like "*" & TextBox1.Value & "*" Then
        nExact = nExact + 1
      End If
    End If
  Next
  ListBox1.List = b
  With Sheets("VALSFOUND")
    .Range("B1").Value = ComboBox1.Value
    .Range("B2").Value = TextBox1.Value
    .Range("B3").Value = nOccurs
    .Range("B4").Value = nVerses
    .Range("B5").Value = nExact
    .Range("A8:A" & Rows.Count).ClearContents
    If nVerses > 0 Then .Range("A8").Resize(nVerses).Value = b
    ListBox2.List = .Range("A1:B5").Value
  End With
End Sub

Private Sub UserForm_Activate()
  a = Sheets("SOURCE").Range("C2", Sheets("SOURCE").Range("C" & Rows.Count).End(3)).Value
  
  With ComboBox1
    .AddItem "KJV"
    .AddItem "NIV"
    .AddItem "NASB"
    .AddItem "RSV"
  End With
  
  With ListBox1
    .ColumnCount = 2
    .ColumnWidths = "100;1"
  End With

  With ListBox2
    .ColumnCount = 5
  End With
End Sub


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 
Upvote 0
Well it's a long road, but I'm going to explain it step by step.

Check out my next design:

1. In the "SOURCE" sheet, your data as follows:
varios 08nov2024.xlsm
C
1VERSES
2Behold, I have given you every herb bearing seed (KJV)
3Then God said, "I give you every seed-bearing plant (NIV)
4I have given you every plant yielding seed (NASB)
5Behold, I have given you every plant yielding seed (RSV)
6Is anything too hard for the Lord? I will return to you at the appointed time next year, and Sarah will have a son (NIV)
7Is anything too difficult for the Lord? At the appointed time I will return to you, at this time next year, and Sarah will have a son (NASB)
SOURCE



2. In the "VALSFOUND" sheet, put the following structure:
varios 08nov2024.xlsm
AB
1Version
2Phrase
3Occurs
4Verses
5Exact
6
7Below phrase matches
8
9
VALSFOUND


3. In the userform, a combobox1 for the versions, a textbox1 to write the phrases, a listbox1 for the values found, a listbox2 for the statistics:

4. Operation:
- In the code I am loading 4 versions in combobox1: "KJV", "NIV", "NASB" and "RSV":

- Then you can choose a version and the code will automatically show all the verses that belong to that version, since according to your initial example, the verses have the version in parentheses, example: (NASB)
- The counters will be shown in the statistics box. You didn't explain how to count, so I did my own calculations hoping to guess.

- Then you can capture the phrase in the textbox, the code will automatically search for the phrase in the version selected in the combo:

- Or you can search for the phrase in all versions, leaving the combo empty:

- At the same time the code will put the results in the VALSFOUND sheet:
😇

Put all of the following code in your userform:
VBA Code:
Dim a As Variant

Private Sub ComboBox1_Change()
  Call Filter_Data
End Sub

Private Sub TextBox1_Change()
  Call Filter_Data
End Sub

Sub Filter_Data()
  Dim arr As Variant, b As Variant, itm As Variant
  Dim i As Long, k As Long, n As Long, m As Long
  Dim nOccurs As Long, nVerses As Long, nExact As Long
 
  ReDim b(1 To UBound(a, 1), 1 To 1)
  If ComboBox1.ListIndex > -1 Then
    arr = Split(TextBox1.Text & " (" & ComboBox1.Value & ")", " ")
  Else
    arr = Split(TextBox1.Text, " ")
  End If
  n = UBound(arr) + 1
  For i = 1 To UBound(a)
    m = 0
    For Each itm In arr
      If a(i, 1) Like "*" & itm & "*" Then
        nOccurs = nOccurs + 1
        m = m + 1
      End If
    Next
  
    If m = n Then
      nVerses = nVerses + 1
      b(nVerses, 1) = a(i, 1)
    End If
  
    If ComboBox1.ListIndex > -1 Then
      If a(i, 1) Like "*" & TextBox1.Value & "*" & "(" & ComboBox1.Value & ")" & "*" Then
        nExact = nExact + 1
      End If
    Else
      If a(i, 1) Like "*" & TextBox1.Value & "*" Then
        nExact = nExact + 1
      End If
    End If
  Next
  ListBox1.List = b
  With Sheets("VALSFOUND")
    .Range("B1").Value = ComboBox1.Value
    .Range("B2").Value = TextBox1.Value
    .Range("B3").Value = nOccurs
    .Range("B4").Value = nVerses
    .Range("B5").Value = nExact
    .Range("A8:A" & Rows.Count).ClearContents
    If nVerses > 0 Then .Range("A8").Resize(nVerses).Value = b
    ListBox2.List = .Range("A1:B5").Value
  End With
End Sub

Private Sub UserForm_Activate()
  a = Sheets("SOURCE").Range("C2", Sheets("SOURCE").Range("C" & Rows.Count).End(3)).Value
 
  With ComboBox1
    .AddItem "KJV"
    .AddItem "NIV"
    .AddItem "NASB"
    .AddItem "RSV"
  End With
 
  With ListBox1
    .ColumnCount = 2
    .ColumnWidths = "100;1"
  End With

  With ListBox2
    .ColumnCount = 5
  End With
End Sub


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Hi Robert - thanks for jumping in on this. Just getting to study this now. Want to fully understand, and not just copy/paste.
Will get to you soon. My thinking is, if online Bible apps like Blue Letter Bible or traditional Bible apps like Logos can write correct code to do this
correctly written Excel VBA code should be able to also, with the tools VBA has, i.e. operators integrated with variable pattern searches, etc.
Again, thanks, cr Kingwood, Tx.
The code works great for each column searched. Translation differences give different results in the count, which is to be expected.
The For loop picks up all instances of any word or phrase.
Thanks a mil.
cr
 
Upvote 0
Well it's a long road, but I'm going to explain it step by step.

Check out my next design:

1. In the "SOURCE" sheet, your data as follows:
varios 08nov2024.xlsm
C
1VERSES
2Behold, I have given you every herb bearing seed (KJV)
3Then God said, "I give you every seed-bearing plant (NIV)
4I have given you every plant yielding seed (NASB)
5Behold, I have given you every plant yielding seed (RSV)
6Is anything too hard for the Lord? I will return to you at the appointed time next year, and Sarah will have a son (NIV)
7Is anything too difficult for the Lord? At the appointed time I will return to you, at this time next year, and Sarah will have a son (NASB)
SOURCE



2. In the "VALSFOUND" sheet, put the following structure:
varios 08nov2024.xlsm
AB
1Version
2Phrase
3Occurs
4Verses
5Exact
6
7Below phrase matches
8
9
VALSFOUND


3. In the userform, a combobox1 for the versions, a textbox1 to write the phrases, a listbox1 for the values found, a listbox2 for the statistics:

4. Operation:
- In the code I am loading 4 versions in combobox1: "KJV", "NIV", "NASB" and "RSV":

- Then you can choose a version and the code will automatically show all the verses that belong to that version, since according to your initial example, the verses have the version in parentheses, example: (NASB)
- The counters will be shown in the statistics box. You didn't explain how to count, so I did my own calculations hoping to guess.

- Then you can capture the phrase in the textbox, the code will automatically search for the phrase in the version selected in the combo:

- Or you can search for the phrase in all versions, leaving the combo empty:

- At the same time the code will put the results in the VALSFOUND sheet:
😇

Put all of the following code in your userform:
VBA Code:
Dim a As Variant

Private Sub ComboBox1_Change()
  Call Filter_Data
End Sub

Private Sub TextBox1_Change()
  Call Filter_Data
End Sub

Sub Filter_Data()
  Dim arr As Variant, b As Variant, itm As Variant
  Dim i As Long, k As Long, n As Long, m As Long
  Dim nOccurs As Long, nVerses As Long, nExact As Long
 
  ReDim b(1 To UBound(a, 1), 1 To 1)
  If ComboBox1.ListIndex > -1 Then
    arr = Split(TextBox1.Text & " (" & ComboBox1.Value & ")", " ")
  Else
    arr = Split(TextBox1.Text, " ")
  End If
  n = UBound(arr) + 1
  For i = 1 To UBound(a)
    m = 0
    For Each itm In arr
      If a(i, 1) Like "*" & itm & "*" Then
        nOccurs = nOccurs + 1
        m = m + 1
      End If
    Next
   
    If m = n Then
      nVerses = nVerses + 1
      b(nVerses, 1) = a(i, 1)
    End If
   
    If ComboBox1.ListIndex > -1 Then
      If a(i, 1) Like "*" & TextBox1.Value & "*" & "(" & ComboBox1.Value & ")" & "*" Then
        nExact = nExact + 1
      End If
    Else
      If a(i, 1) Like "*" & TextBox1.Value & "*" Then
        nExact = nExact + 1
      End If
    End If
  Next
  ListBox1.List = b
  With Sheets("VALSFOUND")
    .Range("B1").Value = ComboBox1.Value
    .Range("B2").Value = TextBox1.Value
    .Range("B3").Value = nOccurs
    .Range("B4").Value = nVerses
    .Range("B5").Value = nExact
    .Range("A8:A" & Rows.Count).ClearContents
    If nVerses > 0 Then .Range("A8").Resize(nVerses).Value = b
    ListBox2.List = .Range("A1:B5").Value
  End With
End Sub

Private Sub UserForm_Activate()
  a = Sheets("SOURCE").Range("C2", Sheets("SOURCE").Range("C" & Rows.Count).End(3)).Value
 
  With ComboBox1
    .AddItem "KJV"
    .AddItem "NIV"
    .AddItem "NASB"
    .AddItem "RSV"
  End With
 
  With ListBox1
    .ColumnCount = 2
    .ColumnWidths = "100;1"
  End With

  With ListBox2
    .ColumnCount = 5
  End With
End Sub


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
You spent a lot of time on this - thank you!. I really like this userform design. About the best I've seen for reporting and viewing results. It gives a complete view for any translation selected. I need to study this a while to understand all you've done, and test it with different
words and phrases so I can have this thing nailed down. A complete understanding of your and Trebor76's code is invaluable to me for this application. I knew this already, but translation differences will give different counts because of the way the original translators wrote it. A tremendous effort. Will get back to you as soon as I can.
Thanks for all your help.
cr, Kingwood, Tx
 
Upvote 0
Hi Charles,

The code works great for each column searched. Translation differences give different results in the count, which is to be expected.
The For loop picks up all instances of any word or phrase.
Thanks a mil.
cr

I'm glad it worked 👍 and you're welcome 😎

Hi Dante,

Check out my next design

Very nice 😉
 
Upvote 0

Forum statistics

Threads
1,224,462
Messages
6,178,814
Members
452,880
Latest member
kares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top