Now that I have your attention :). This a two part question. How do I insert a bookmark programmatically? <- most want to know
and is it possible to maintain dynamic formula ranges inside word tables? eg. I have the formula =Sum(Table1 B3:B9), and the user changes the table so the formula now needs to be =Sum(Table1 B3:B12). Is there a way to automate the change with out code? or user interaction? <-this is just curiosity
1. [VBA]Selection.Bookmarks.Add "MyBookmarkName"[/VBA] is somewhere to start.
2. Word fields and referencing are somewhat of a mystic art. It should be possible (but certainly not the easiest thing) to make dynamic ranges but I would have to play around a bit to get something working.
Thanks Tony. I thought it would be something like that (Q1). As for number two, I only ask because this billing template is something of a perpetual headache at the company I work for and knowing how to do dynamic ranges inside a table for formulas would help somewhat to simplyfying it, as well as making editting less of a chore both for user and programmer :).
I realise I'm being a little vague, sorry :(, I think its just that bookmarks are unfamiliar territory for me. Would it be possible to use two bookmarks to specify a range for a table formula? say instead of =Sum(Table1 B5:B10) have =Sum(Table1 StartSum:EndSum) And justhave bookmarks in the right cell... hmm, experiment time :)
okay, that didn't work but I think i'm onto something of an idea. If I could (in code which I think i'm always happier with to be honest) how would I move through the cells between the two bookmarks. They would be in the same column if that helps?
You could try this to start with - instead of selecting, do what you want:
[VBA]Set col = ActiveDocument.Bookmarks("Bookmark1").Range.Cells(1).Column
For i = ActiveDocument.Bookmarks("Bookmark1").Range.Cells(1).Row.Index _
To ActiveDocument.Bookmarks("Bookmark2").Range.Cells(1).Row.Index
col.Cells(i).Select
Next[/VBA]
just my luck, i get a runtime error :). error 5992 - cannot access individual columns because the table has mixed cell widths - erf :banghead: . any ideas to get around it, becasue from your code it look like that could be just the ticket i need.
this is what i had going till then
[vba]
Dim i As Integer
Dim myRange As Range
Set myRange = ActiveDocument.Range(Start:=ActiveDocument.Bookmarks _
("StartDisb").Start, End:=ActiveDocument.Bookmarks("EndDisb").Start)
For i = 2 To myRange.Cells.count
'the mod lets me access the right column but your code seems so
'much more efficient
If i Mod 4 = 1 Then
Debug.Print "I = " & i & " Cell = " & myRange.Cells(i).Range.Text
End If
Next i
[/vba]
Now is this not a curious thing. Ok Tony, help me out here.
ColA ColB ColC ColD
Row1 12 100
Row2 5
Row3
b1 is bookmarked as "start", d2 as "end". I have a formula in c3 "= start + end". No SUM, just straight +. It returns 117.
The problem lies in that Word sets the default for cells using formula as SUM(LEFT). Regardless of the fact that I did not put a formula in a1, or b2, b2 is listed as SUM(LEFT), and I can not edit it out.
Not only that, but if I remove the value in b1...but it is still bookmarked as "start", the formula in d2 returns 17. Even though a1 is NOT bookmarked, even though a1 is not mentioned at all - it nevertheless is used.
Regarding b2 - I have tried to SUM itself; I have tried to completely remove the "formula" (which I never entered in the first place); I have tried ABOVE - but because there IS no above this returns a syntax error.
Every time a formula runs that references a cell as bookmark, even though that cell has NO formula, a formula is inserted "=SUM(LEFT)" before the running formula actually executes.
Hmmmmm. Thoughts on what I may be doing wrong?
Hi all,
Are you able to use { =SUM(above) }? This sums all the values up to the first blank cell or illegal value. Then all you need to do if the table changes make sure you have a blank cell between ranges and update the fields.
Just a thought....
Cheers,
Andrew
Did you read my post? Yes, I did. In my example - and unfortunately I did not realize it would come out unformatted -
1. b1 never had a formula entered
2. b1 is referenced BY BOOKMARK
3. referencing b1 CREATES a formula for b1 =SUM(LEFT)
4. I tried changing to SUM(ABOVE), but, as I posted, that causes an error as there is no above...it is in the top row.
5. I tried to remove the formula, and can, but as soon as the other formula makes a reference to the bookmark, it creates the =SUM(LEFT) again.
I hope this clarifies.
You can reference bookmarks across merged cells in a table, but this automatically creation of formula for referenced cells is a problem, as what if there is a value you do NOT want to include?
Actually, if it has this kind of need for table cell calculation...may as well use Excel. However, this is annoying me, and I want to figure it out.
Ok,
Sorry fumei, I was refering to Sandam's problem, but I'll have crack at yours too. There are few things that don't make sense though, probably not helped by the formatting not coming out properly in the post. Are you able to post a document with the table, bookmark and field layout?
Cheers.
Man, another forgotten post. :doh:
I really need to keep up on these things. I've given up on the formula's - the calculations are for three seperate gruops of values in the same column which all need to be added to a total elsewhere (ughhhh!) At the moment the calcualtions are done programmatically and then inserted into the table. To avoid the need to recalulate on a modification - I pull all the fields off the page and get the user to edit them using the same form they used to enter them for the first time - and then re-inserting them. I would rather be using excel myself and my boss has even hinted at the possibility but I'm not even going to get into that for a while. I have this week (already only 4 days because of a public holiday) to rewrite and rework 6 months of coding :eek: :banghead: :bug: :bug: :bug: :bug: :banghead: :wot
Thanks fumei, thanks rabbit, i really appreciate the insights
Andrew;?
P.s marking this thread solved
Hi Gerry, :DQuote:
Originally Posted by fumei
Have you got a little sample file to demonstrate this problem.
Would love to play with it and think along! :whistle: