CODEWITHSUNDEEP
vbaexcelms-word
Jack Henderson
Jack Henderson

Reputation: 93

Finding Matches between an Excel Spreadsheet and VBA Array

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

Upvotes: 1

Views: 105

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Logic

  1. Use .Find to check if the keywords are present in the range or not.
  2. Store the relevant keywords in a comma delimited string which will later be converted into an array
  3. Open word doc
  4. 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

Upvotes: 3

Onmlock
Onmlock

Reputation: 63

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.

Upvotes: 0

Related Questions