How to read the formatted percentage from a cell with VBA?
I am taking data from an Excel spreadsheet and inserting it into a Word document. Here is the code I am using which works as intended.
Private Sub insertData(wb As Excel.Workbook)
Dim numBM As Integer
Dim countBM As Integer
Dim currentBM As String
numBM = ActiveDocument.Bookmarks.Count
For countBM = 1 To numBM
currentBM = ActiveDocument.Bookmarks(countBM).Name
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Value2
Next
End Sub
In Excel, I have some cells that are percentage type formatting. So the value of the cell is 0.857394723 but the cell displays "86%". How can I change my code so that "86%" is inserted into Word instead of "0.857394723"
excel vba ms-word format percentage
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
add a comment |
I am taking data from an Excel spreadsheet and inserting it into a Word document. Here is the code I am using which works as intended.
Private Sub insertData(wb As Excel.Workbook)
Dim numBM As Integer
Dim countBM As Integer
Dim currentBM As String
numBM = ActiveDocument.Bookmarks.Count
For countBM = 1 To numBM
currentBM = ActiveDocument.Bookmarks(countBM).Name
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Value2
Next
End Sub
In Excel, I have some cells that are percentage type formatting. So the value of the cell is 0.857394723 but the cell displays "86%". How can I change my code so that "86%" is inserted into Word instead of "0.857394723"
excel vba ms-word format percentage
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
Not sure why this question attracted a down vote. In it's current form it seems a valid question (not too much code, clear question, not too much context, ...). I think it is our responsibility to drop a comment when down voting (especially with new users), allowing them to learn and improve. Anyway, +1 from me puts you back at neutral score :-)
– Paul van Leeuwen
Dec 31 '18 at 13:39
add a comment |
I am taking data from an Excel spreadsheet and inserting it into a Word document. Here is the code I am using which works as intended.
Private Sub insertData(wb As Excel.Workbook)
Dim numBM As Integer
Dim countBM As Integer
Dim currentBM As String
numBM = ActiveDocument.Bookmarks.Count
For countBM = 1 To numBM
currentBM = ActiveDocument.Bookmarks(countBM).Name
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Value2
Next
End Sub
In Excel, I have some cells that are percentage type formatting. So the value of the cell is 0.857394723 but the cell displays "86%". How can I change my code so that "86%" is inserted into Word instead of "0.857394723"
excel vba ms-word format percentage
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
I am taking data from an Excel spreadsheet and inserting it into a Word document. Here is the code I am using which works as intended.
Private Sub insertData(wb As Excel.Workbook)
Dim numBM As Integer
Dim countBM As Integer
Dim currentBM As String
numBM = ActiveDocument.Bookmarks.Count
For countBM = 1 To numBM
currentBM = ActiveDocument.Bookmarks(countBM).Name
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Value2
Next
End Sub
In Excel, I have some cells that are percentage type formatting. So the value of the cell is 0.857394723 but the cell displays "86%". How can I change my code so that "86%" is inserted into Word instead of "0.857394723"
excel vba ms-word format percentage
excel vba ms-word format percentage
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
edited Jan 1 at 3:21
Paul van Leeuwen
1,1881121
1,1881121
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
asked Jan 21 '16 at 19:50
MightyMouseZMightyMouseZ
41117
41117
Not sure why this question attracted a down vote. In it's current form it seems a valid question (not too much code, clear question, not too much context, ...). I think it is our responsibility to drop a comment when down voting (especially with new users), allowing them to learn and improve. Anyway, +1 from me puts you back at neutral score :-)
– Paul van Leeuwen
Dec 31 '18 at 13:39
add a comment |
Not sure why this question attracted a down vote. In it's current form it seems a valid question (not too much code, clear question, not too much context, ...). I think it is our responsibility to drop a comment when down voting (especially with new users), allowing them to learn and improve. Anyway, +1 from me puts you back at neutral score :-)
– Paul van Leeuwen
Dec 31 '18 at 13:39
Not sure why this question attracted a down vote. In it's current form it seems a valid question (not too much code, clear question, not too much context, ...). I think it is our responsibility to drop a comment when down voting (especially with new users), allowing them to learn and improve. Anyway, +1 from me puts you back at neutral score :-)
– Paul van Leeuwen
Dec 31 '18 at 13:39
Not sure why this question attracted a down vote. In it's current form it seems a valid question (not too much code, clear question, not too much context, ...). I think it is our responsibility to drop a comment when down voting (especially with new users), allowing them to learn and improve. Anyway, +1 from me puts you back at neutral score :-)
– Paul van Leeuwen
Dec 31 '18 at 13:39
add a comment |
2 Answers
2
active
oldest
votes
Format the value accordingly, using the Strings.Format method from the VBA standard library:
Dim formattedValue As String
formattedValue = Format(wb.Names(currentBM).RefersToRange.Value2,"0%")
ActiveDocument.Bookmarks(currentBM).Range.Text = formattedValue
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
Thanks for your answer. I tried it out and it does work but I would have to expand my code to format some values and not others.
– MightyMouseZ
Jan 21 '16 at 23:07
add a comment |
I actually figured out the answer to this, which is extremely simple. Instead of using ...RefersToRange.Value I can use ...RefersToRange.Text so the line becomes
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Text
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f34933012%2fhow-to-read-the-formatted-percentage-from-a-cell-with-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Format the value accordingly, using the Strings.Format method from the VBA standard library:
Dim formattedValue As String
formattedValue = Format(wb.Names(currentBM).RefersToRange.Value2,"0%")
ActiveDocument.Bookmarks(currentBM).Range.Text = formattedValue
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
Thanks for your answer. I tried it out and it does work but I would have to expand my code to format some values and not others.
– MightyMouseZ
Jan 21 '16 at 23:07
add a comment |
Format the value accordingly, using the Strings.Format method from the VBA standard library:
Dim formattedValue As String
formattedValue = Format(wb.Names(currentBM).RefersToRange.Value2,"0%")
ActiveDocument.Bookmarks(currentBM).Range.Text = formattedValue
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
Thanks for your answer. I tried it out and it does work but I would have to expand my code to format some values and not others.
– MightyMouseZ
Jan 21 '16 at 23:07
add a comment |
Format the value accordingly, using the Strings.Format method from the VBA standard library:
Dim formattedValue As String
formattedValue = Format(wb.Names(currentBM).RefersToRange.Value2,"0%")
ActiveDocument.Bookmarks(currentBM).Range.Text = formattedValue
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
Format the value accordingly, using the Strings.Format method from the VBA standard library:
Dim formattedValue As String
formattedValue = Format(wb.Names(currentBM).RefersToRange.Value2,"0%")
ActiveDocument.Bookmarks(currentBM).Range.Text = formattedValue
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
answered Jan 21 '16 at 20:07
Mathieu GuindonMathieu Guindon
43.3k767147
43.3k767147
Thanks for your answer. I tried it out and it does work but I would have to expand my code to format some values and not others.
– MightyMouseZ
Jan 21 '16 at 23:07
add a comment |
Thanks for your answer. I tried it out and it does work but I would have to expand my code to format some values and not others.
– MightyMouseZ
Jan 21 '16 at 23:07
Thanks for your answer. I tried it out and it does work but I would have to expand my code to format some values and not others.
– MightyMouseZ
Jan 21 '16 at 23:07
Thanks for your answer. I tried it out and it does work but I would have to expand my code to format some values and not others.
– MightyMouseZ
Jan 21 '16 at 23:07
add a comment |
I actually figured out the answer to this, which is extremely simple. Instead of using ...RefersToRange.Value I can use ...RefersToRange.Text so the line becomes
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Text
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
add a comment |
I actually figured out the answer to this, which is extremely simple. Instead of using ...RefersToRange.Value I can use ...RefersToRange.Text so the line becomes
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Text
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
add a comment |
I actually figured out the answer to this, which is extremely simple. Instead of using ...RefersToRange.Value I can use ...RefersToRange.Text so the line becomes
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Text
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
I actually figured out the answer to this, which is extremely simple. Instead of using ...RefersToRange.Value I can use ...RefersToRange.Text so the line becomes
ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Text
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
answered Jan 21 '16 at 23:05
MightyMouseZMightyMouseZ
41117
41117
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f34933012%2fhow-to-read-the-formatted-percentage-from-a-cell-with-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Not sure why this question attracted a down vote. In it's current form it seems a valid question (not too much code, clear question, not too much context, ...). I think it is our responsibility to drop a comment when down voting (especially with new users), allowing them to learn and improve. Anyway, +1 from me puts you back at neutral score :-)
– Paul van Leeuwen
Dec 31 '18 at 13:39