hello everyone,
I am trying to link a word doc template with a complex excel financial model. I named all the cells in excel that i would like transferred and i build out tables in word and bookmarked all the "cells" for the corresponding names in excel. The thing is, not all the columns and rows in word always apply. so i want the users in word to be able to cut out columns and rows that are N/A. however, it appears when they do this, that bookmarks are deleted, which I think is resulting in this runtime error...
is there a way around this? is there a way to alter the code so that if the cell that contain the bookmark is no longer there, it just proceeds anyway? i have enclosed an excerpt from the code below. any help is greatly appreciated. the line that is getting debugged is " Set cl = item.Range.Cells(1)"
Code:
Dim item As Bookmark
Dim itemName As String
Dim a As Long
For a = wDoc.Bookmarks.Count To 1 Step -1
Set item = wDoc.Bookmarks(a)
itemName = item.Name
Set cl = item.Range.Cells(1)
fValue = RangeValue(itemName)
If fValue <> "" Then
If IsNumeric(fValue) Then
Select Case formatCell
Case "Percentage"
item.Range.Text = Format(fValue, formatCell)
Case "0%"
item.Range.Text = Format(fValue, formatCell)
Case "0.0%"
item.Range.Text = Format(fValue, formatCell)
Case "0.00%"
item.Range.Text = Format(fValue, formatCell)
Case "0.00""x"""
item.Range.Text = Format(fValue, "0.00""x""")
Case Else
item.Range.Text = Format(fValue, "#,##0;(#,##0);"" - """)
End Select
Else
item.Range.Text = fValue
End If
End If
wDoc.Bookmarks.Add itemName, cl.Range
Next