If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
cell number format
I have one table in a word document. In column 3 and 7 starting from row 2 I
want to change the input 10000 to 10,000. I found the following: Sub format() ' Dim i As Long, mynum As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set mynum = .Cell(i, 3).Range mynum.End = mynum.End - 1 .Cell(i, 3).Range = format(mynum, "#,##0") Next i End With End Sub -------------------------- It works fine for column 3, but when I change the 3 to 7 in a second sub, it changes the values, but stops at: Set mynum = .Cell(i, 7).Range =========== Is there anyway I can do both columns in one macro? what's the code? thanks |
#2
|
|||
|
|||
cell number format
Dim i As Long, mynum As Range
With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set mynum = .Cell(i, 3).Range mynum.End = mynum.End - 1 .Cell(i, 3).Range = format(mynum, "#,##0") Set mynum = .Cell(i, 7).Range mynum.End = mynum.End - 1 .Cell(i, 7).Range = format(mynum, "#,##0") Next i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Helmut" wrote in message ... I have one table in a word document. In column 3 and 7 starting from row 2 I want to change the input 10000 to 10,000. I found the following: Sub format() ' Dim i As Long, mynum As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set mynum = .Cell(i, 3).Range mynum.End = mynum.End - 1 .Cell(i, 3).Range = format(mynum, "#,##0") Next i End With End Sub -------------------------- It works fine for column 3, but when I change the 3 to 7 in a second sub, it changes the values, but stops at: Set mynum = .Cell(i, 7).Range =========== Is there anyway I can do both columns in one macro? what's the code? thanks |
#3
|
|||
|
|||
cell number format
Hi Doug,
I get this error: Runtime error 5941 - the requested number of the collection des not exist. It actually executes all two columns, changing the format but then the Erro pops up and when pressing Debug the following is highlighted: Set mynum = .Cell(i, 7).Range Doug, in addition, I have a SUM of the two columns and would like to 'update' the field after formatting the cells in the two columns. what additional command do I need to 'update the field'? thanks Helmut "Doug Robbins - Word MVP" wrote: Dim i As Long, mynum As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set mynum = .Cell(i, 3).Range mynum.End = mynum.End - 1 .Cell(i, 3).Range = format(mynum, "#,##0") Set mynum = .Cell(i, 7).Range mynum.End = mynum.End - 1 .Cell(i, 7).Range = format(mynum, "#,##0") Next i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Helmut" wrote in message ... I have one table in a word document. In column 3 and 7 starting from row 2 I want to change the input 10000 to 10,000. I found the following: Sub format() ' Dim i As Long, mynum As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set mynum = .Cell(i, 3).Range mynum.End = mynum.End - 1 .Cell(i, 3).Range = format(mynum, "#,##0") Next i End With End Sub -------------------------- It works fine for column 3, but when I change the 3 to 7 in a second sub, it changes the values, but stops at: Set mynum = .Cell(i, 7).Range =========== Is there anyway I can do both columns in one macro? what's the code? thanks |
#4
|
|||
|
|||
cell number format
It works fine here. To get that format for the formula, set it in the
Insert Formula dialog. Note that if the formula is in the last row of the able, the macro as it is written will convert the result of the formula to text. If you don't want that to happen, change For i = 2 To .Rows.Count to For i = 2 To .Rows.Count - 1 -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Helmut" wrote in message ... Hi Doug, I get this error: Runtime error 5941 - the requested number of the collection des not exist. It actually executes all two columns, changing the format but then the Erro pops up and when pressing Debug the following is highlighted: Set mynum = .Cell(i, 7).Range Doug, in addition, I have a SUM of the two columns and would like to 'update' the field after formatting the cells in the two columns. what additional command do I need to 'update the field'? thanks Helmut "Doug Robbins - Word MVP" wrote: Dim i As Long, mynum As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set mynum = .Cell(i, 3).Range mynum.End = mynum.End - 1 .Cell(i, 3).Range = format(mynum, "#,##0") Set mynum = .Cell(i, 7).Range mynum.End = mynum.End - 1 .Cell(i, 7).Range = format(mynum, "#,##0") Next i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Helmut" wrote in message ... I have one table in a word document. In column 3 and 7 starting from row 2 I want to change the input 10000 to 10,000. I found the following: Sub format() ' Dim i As Long, mynum As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set mynum = .Cell(i, 3).Range mynum.End = mynum.End - 1 .Cell(i, 3).Range = format(mynum, "#,##0") Next i End With End Sub -------------------------- It works fine for column 3, but when I change the 3 to 7 in a second sub, it changes the values, but stops at: Set mynum = .Cell(i, 7).Range =========== Is there anyway I can do both columns in one macro? what's the code? thanks |
Thread Tools | |
Display Modes | |
|
|