Input data into excel to open word document with corresponding data in specified bookmarks

[excelnoobz]

Hi all, I could definitely use your help in addressing my code. This is the first time I've ever coded in VBA so appreciate any beginner's advice and tips. As a brief background, I have an excel sheet of my county's zip codes (col 2) and their corresponding towns (col 3). I want to have a person enter their zip code into "A2". Excel searches thru the zip code column and finds the corresponding town. Afterwards, I would like it to open up a word document template, which has two bookmarks: "zip" and "town", and pull the corresponding zip code and town to the appropriate bookmark.

Unfortunately, I keep getting an error of "Sub or Function not defined". Additionally, it highlights "Sub Worksheet_Change (ByVal Target As Range) and "cell" once I receive the error. Not sure what that means.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub Worksheet_Change(ByVal Target As Range)
Dim myVar As String
Dim numofzips As Integer
Dim targetrow As Integer
Dim ZipCode As String, municipal As String
numofzips = Range(Range("B2"), Range("B2").End(xlDown)).Count
If Target.Address = "$A$2" Then
Target.Font.ColorIndex = 5
myVar = Target.Value
For i = 2 To numofzips
If myVar = cell(i, 2).Value Then
targetrow = i
End If
Next
End If
ZipCode = cell(targetrow, 2)
municipal = cell(targetrow, 3)
ImportToDoc ZipCode, municipal



End Sub

Sub ImportToDoc(zip As String, town As String)



Dim wdApp As Word.Application, wdDoc As Word.Document
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open("C:\Users\kimd\Documents\VBA Test.docx", ReadOnly:=True)
wdApp.Visible = True
wdDoc.Bookmarks("zip").Range.Text = zip
wdDoc.Bookmarks("town").Range.Text = town
wdDoc.SaveAs Filename:=town & " Test"
wdDoc.Close
wdApp.Application.Quit


End Sub</code>

 

[Kenneth Hobson]

Welcome to the forum! TIP: Click the # icon in a reply toolbar to insert code tags and paste code between them.

I guess you set the Word Object in Tools > References?

Tip: Use Option Explicit as first line of code at top of Module, Worksheet, Userform, etc. Click Debug menus Compile before a Run.

Change True to False or skip passing it.