Finding Matches between an Excel Spreadsheet and VBA Array

Question

I'm new to Excel VBA and was looking for some help in fixing my code. So basically to provide colour on what I have, I have an excel database, and a word document. In the word document I have bookmarked section headers (reffered to as "cat", "dog", and "bird") and in a row on the excel database I have "dog" and "bird".

What I am trying to do is write a code that compares the elements of the array (which are strings) to the cell values within a range declared in an excel database. For the values that exist in the array but not in the declared excel range, I want to delete those values (i.e. the bookmark) from the word document.

If anyone could provide me with feedback, ideas, or example codes it would be greatly appreciated.

Thanks.

Sub ArrayToDatabase()

Dim myRange As Variant
Set myRange = Range("C7:AP7")

Dim myArray As Variant
myArray = Array("cat", "dog", "bird")

Dim i As Integer
Dim reqName As Object
For i = LBound(myArray) To UBound(myArray)
    Set reqName = myArray(i).Value
    If myRange.Validation(reqName) = False Then
        wdApp.ActiveDocument.Bookmarks(reqName).Range._
        Paragraphs(1).Range.Delete
    End If
Next i

End Sub

Siddharth Rout · Accepted Answer · 2017-06-14 20:22:07Z

Logic

Use .Find to check if the keywords are present in the range or not.
Store the relevant keywords in a comma delimited string which will later be converted into an array
Open word doc
Loop through the array and delete the bookmarks

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim myArray As Variant, BookMarksToDelete As Variant
    Dim oWordApp As Object, oWordDoc As Object
    Dim sTemp As String, FlName As String
    Dim aCell As Range, myRange As Range
    Dim i As Long

    '~~> Change this to the relevant sheet
    Set myRange = ThisWorkbook.Sheets("Sheet1").Range("C7:AP7")

    myArray = Array("cat", "dog", "bird")

    '~~> Change this to the relevant word document
    FlName = "C:\Users\Siddharth\Desktop\DeleteMeLater.docx"

    For i = LBound(myArray) To UBound(myArray)
        '~~> Check if the word exists in the range or not
        Set aCell = myRange.Find(What:=myArray(i), LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If it doesn't then store it in a comma delimited string
        If aCell Is Nothing Then
            sTemp = sTemp & "," & myArray(i)
        Else
            Set aCell = Nothing
        End If
    Next i

    sTemp = Mid(sTemp, 2)

    If Not Len(Trim(sTemp)) = 0 Then
        '~~> Convert comma delimited string to array
        BookMarksToDelete = Split(sTemp, ",")

        '~~> Open word document
        Set oWordApp = CreateObject("Word.Application")
        oWordApp.Visible = True
        Set oWordDoc = oWordApp.Documents.Open(FlName)

        '~~> Delete the bookmarks
        For i = LBound(BookMarksToDelete) To UBound(BookMarksToDelete)
            oWordDoc.Bookmarks(BookMarksToDelete(i)).Delete
        Next i
    End If

    MsgBox "Done"
End Sub

Hey Siddharth, thanks for the response. So I went ahead and ran your code, and the following line came up with a 'Run-time Error 462". oWordDoc.Bookmarks(BookMarksToDelete(i)).Delete
My Code is tested and tried :) BTW I am on my way to the gym. Will have to look at this when i come back. Double check if bookmarks exists and they do not have any extra spaces...
I got it figured out thank you once again. Also, how would I later that line to not just delete the bookmark, but also delete the text at it and the paragraph underneath it? (i.e. I have a header and a paragraph).

Onmlock · Accepted Answer · 2017-06-14 20:08:01Z

Does your code work? It's a little unclear what you're asking, unless that's just for feedback. All I personally have to say is the way you declare variables.

So if you know what the variable will hold, it's best to declare it as such. For example,

Dim myRange as Range

Dim myArray(2) as String
myArray = {"cat", "dog", "bird"}

Dim reqName as String

I'm no expert either, just trying to help! Feel free to ask any questions, but I can't guarantee I have an answer.

Collectives™ on Stack Overflow

Finding Matches between an Excel Spreadsheet and VBA Array

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related