formula returning #NAME inside loop via selection from input box
I have a small macro and I would like that it does the following: the first input box ask the user to select a empty range where he wants to have in each cell of this range the debit/Credit… The 2nd input box ask the user to select a range of amounts (each amount in each cell of this range can be either negative or positive) . Then from these 2 selections, I would like that it returns “D” (debit) or “C” (credit) on each cell where it was selected the range of first input box. Unfortunately my macro does not work properly, indeed when I try to execute it, it returns me #NAME? (instead of “C” or “D” ) In each cell of the range selected via my first input box. For example on the picture below, I selected via my 1st input box the range K2:K5 (as an empty range) and via my second input box the range C2:C5, after executing the macro, it returns me #NAME in each cell of range K2:K5.
Many Thanks in advance for your help.
Xavi
Please find my VBA code below:
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Range
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng2 = Application.Selection
Set WorkRng2 = Application.InputBox("Please select the range of amounts", xTitleId, WorkRng.Address, Type:=8)
For Each c In WorkRng
c.FormulaR1C1 = _
"=If(WorkRng2.Value>0, ""D"",""C"")"
Next c
End Sub
excel vba excel-vba loops inputbox
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
asked Nov 14 '18 at 16:43
XaviXavi
617
add a comment |
I have a small macro and I would like that it does the following: the first input box ask the user to select a empty range where he wants to have in each cell of this range the debit/Credit… The 2nd input box ask the user to select a range of amounts (each amount in each cell of this range can be either negative or positive) . Then from these 2 selections, I would like that it returns “D” (debit) or “C” (credit) on each cell where it was selected the range of first input box. Unfortunately my macro does not work properly, indeed when I try to execute it, it returns me #NAME? (instead of “C” or “D” ) In each cell of the range selected via my first input box. For example on the picture below, I selected via my 1st input box the range K2:K5 (as an empty range) and via my second input box the range C2:C5, after executing the macro, it returns me #NAME in each cell of range K2:K5.
Many Thanks in advance for your help.
Xavi
Please find my VBA code below:
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Range
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng2 = Application.Selection
Set WorkRng2 = Application.InputBox("Please select the range of amounts", xTitleId, WorkRng.Address, Type:=8)
For Each c In WorkRng
c.FormulaR1C1 = _
"=If(WorkRng2.Value>0, ""D"",""C"")"
Next c
End Sub
excel vba excel-vba loops inputbox
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
asked Nov 14 '18 at 16:43
XaviXavi
617
2
Excel will not know whatWorkRng2.Valueis [as part of a formula]. You might be able to replace it with" & WorkRng.Address & "?
– CLR
Nov 14 '18 at 16:47
2
"=If(" & WorkRng2.Value & " >0, ""D"",""C"")"
– Comintern
Nov 14 '18 at 16:47
Thank you CLR, thank you Comintern, I tried to insert your formula Comintern in the VBA code but when I run the macro, I get an error message : run time error 13, type mismatch.
– Xavi
Nov 14 '18 at 17:32
add a comment |
I have a small macro and I would like that it does the following: the first input box ask the user to select a empty range where he wants to have in each cell of this range the debit/Credit… The 2nd input box ask the user to select a range of amounts (each amount in each cell of this range can be either negative or positive) . Then from these 2 selections, I would like that it returns “D” (debit) or “C” (credit) on each cell where it was selected the range of first input box. Unfortunately my macro does not work properly, indeed when I try to execute it, it returns me #NAME? (instead of “C” or “D” ) In each cell of the range selected via my first input box. For example on the picture below, I selected via my 1st input box the range K2:K5 (as an empty range) and via my second input box the range C2:C5, after executing the macro, it returns me #NAME in each cell of range K2:K5.
Many Thanks in advance for your help.
Xavi
Please find my VBA code below:
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Range
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng2 = Application.Selection
Set WorkRng2 = Application.InputBox("Please select the range of amounts", xTitleId, WorkRng.Address, Type:=8)
For Each c In WorkRng
c.FormulaR1C1 = _
"=If(WorkRng2.Value>0, ""D"",""C"")"
Next c
End Sub
excel vba excel-vba loops inputbox
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
asked Nov 14 '18 at 16:43
XaviXavi
617
I have a small macro and I would like that it does the following: the first input box ask the user to select a empty range where he wants to have in each cell of this range the debit/Credit… The 2nd input box ask the user to select a range of amounts (each amount in each cell of this range can be either negative or positive) . Then from these 2 selections, I would like that it returns “D” (debit) or “C” (credit) on each cell where it was selected the range of first input box. Unfortunately my macro does not work properly, indeed when I try to execute it, it returns me #NAME? (instead of “C” or “D” ) In each cell of the range selected via my first input box. For example on the picture below, I selected via my 1st input box the range K2:K5 (as an empty range) and via my second input box the range C2:C5, after executing the macro, it returns me #NAME in each cell of range K2:K5.
Many Thanks in advance for your help.
Xavi
Please find my VBA code below:
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Range
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng2 = Application.Selection
Set WorkRng2 = Application.InputBox("Please select the range of amounts", xTitleId, WorkRng.Address, Type:=8)
For Each c In WorkRng
c.FormulaR1C1 = _
"=If(WorkRng2.Value>0, ""D"",""C"")"
Next c
End Sub
excel vba excel-vba loops inputbox
excel vba excel-vba loops inputbox
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
asked Nov 14 '18 at 16:43
XaviXavi
617
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
asked Nov 14 '18 at 16:43
XaviXavi
617
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
edited Nov 15 '18 at 8:26
Pᴇʜ
22.8k62850
22.8k62850
asked Nov 14 '18 at 16:43
XaviXavi
617
asked Nov 14 '18 at 16:43
XaviXavi
617
asked Nov 14 '18 at 16:43
XaviXavi
617
617
2
Excel will not know whatWorkRng2.Valueis [as part of a formula]. You might be able to replace it with" & WorkRng.Address & "?
– CLR
Nov 14 '18 at 16:47
2
"=If(" & WorkRng2.Value & " >0, ""D"",""C"")"
– Comintern
Nov 14 '18 at 16:47
Thank you CLR, thank you Comintern, I tried to insert your formula Comintern in the VBA code but when I run the macro, I get an error message : run time error 13, type mismatch.
– Xavi
Nov 14 '18 at 17:32
add a comment |
2
Excel will not know whatWorkRng2.Valueis [as part of a formula]. You might be able to replace it with" & WorkRng.Address & "?
– CLR
Nov 14 '18 at 16:47
2
"=If(" & WorkRng2.Value & " >0, ""D"",""C"")"
– Comintern
Nov 14 '18 at 16:47
Thank you CLR, thank you Comintern, I tried to insert your formula Comintern in the VBA code but when I run the macro, I get an error message : run time error 13, type mismatch.
– Xavi
Nov 14 '18 at 17:32
2
2
Excel will not know what
WorkRng2.Value is [as part of a formula]. You might be able to replace it with " & WorkRng.Address & " ?– CLR
Nov 14 '18 at 16:47
Excel will not know what
WorkRng2.Value is [as part of a formula]. You might be able to replace it with " & WorkRng.Address & " ?– CLR
Nov 14 '18 at 16:47
2
2
"=If(" & WorkRng2.Value & " >0, ""D"",""C"")"– Comintern
Nov 14 '18 at 16:47
"=If(" & WorkRng2.Value & " >0, ""D"",""C"")"– Comintern
Nov 14 '18 at 16:47
Thank you CLR, thank you Comintern, I tried to insert your formula Comintern in the VBA code but when I run the macro, I get an error message : run time error 13, type mismatch.
– Xavi
Nov 14 '18 at 17:32
Thank you CLR, thank you Comintern, I tried to insert your formula Comintern in the VBA code but when I run the macro, I get an error message : run time error 13, type mismatch.
– Xavi
Nov 14 '18 at 17:32
add a comment |
1 Answer
1
active
oldest
votes
You can't compare the value of multiple cells in one go so it will only work if WorkRng2 is a single cell. Try the below, which is my best guess at what you are trying to do.
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Long
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", , , Type:=8)
Set WorkRng2 = Application.InputBox("Please select the range of amounts", , , Type:=8)
If WorkRng.Count <> WorkRng2.Count Then
MsgBox "Ranges must be same size"
Else
For c = 1 To WorkRng.Count
WorkRng.Cells(c).Formula = "=If(" & WorkRng2.Cells(c).Value & ">0, ""D"",""C"")"
Next c
End If
End Sub
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
Thanks a lot SJR for your explanation and your modification, it is solved my problem!
– Xavi
Nov 15 '18 at 8:57
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%2f53305006%2fformula-returning-name-inside-loop-via-selection-from-input-box%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can't compare the value of multiple cells in one go so it will only work if WorkRng2 is a single cell. Try the below, which is my best guess at what you are trying to do.
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Long
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", , , Type:=8)
Set WorkRng2 = Application.InputBox("Please select the range of amounts", , , Type:=8)
If WorkRng.Count <> WorkRng2.Count Then
MsgBox "Ranges must be same size"
Else
For c = 1 To WorkRng.Count
WorkRng.Cells(c).Formula = "=If(" & WorkRng2.Cells(c).Value & ">0, ""D"",""C"")"
Next c
End If
End Sub
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
Thanks a lot SJR for your explanation and your modification, it is solved my problem!
– Xavi
Nov 15 '18 at 8:57
add a comment |
You can't compare the value of multiple cells in one go so it will only work if WorkRng2 is a single cell. Try the below, which is my best guess at what you are trying to do.
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Long
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", , , Type:=8)
Set WorkRng2 = Application.InputBox("Please select the range of amounts", , , Type:=8)
If WorkRng.Count <> WorkRng2.Count Then
MsgBox "Ranges must be same size"
Else
For c = 1 To WorkRng.Count
WorkRng.Cells(c).Formula = "=If(" & WorkRng2.Cells(c).Value & ">0, ""D"",""C"")"
Next c
End If
End Sub
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
Thanks a lot SJR for your explanation and your modification, it is solved my problem!
– Xavi
Nov 15 '18 at 8:57
add a comment |
You can't compare the value of multiple cells in one go so it will only work if WorkRng2 is a single cell. Try the below, which is my best guess at what you are trying to do.
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Long
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", , , Type:=8)
Set WorkRng2 = Application.InputBox("Please select the range of amounts", , , Type:=8)
If WorkRng.Count <> WorkRng2.Count Then
MsgBox "Ranges must be same size"
Else
For c = 1 To WorkRng.Count
WorkRng.Cells(c).Formula = "=If(" & WorkRng2.Cells(c).Value & ">0, ""D"",""C"")"
Next c
End If
End Sub
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
You can't compare the value of multiple cells in one go so it will only work if WorkRng2 is a single cell. Try the below, which is my best guess at what you are trying to do.
Sub ReturncorrectCreditdebitcolumnbasedonselectioninputbox()
Dim c As Long
Dim WorkRng2 As Range
Dim WorkRng As Range
Set WorkRng = Application.InputBox("Please select the range where you want your Dedit/Credit", , , Type:=8)
Set WorkRng2 = Application.InputBox("Please select the range of amounts", , , Type:=8)
If WorkRng.Count <> WorkRng2.Count Then
MsgBox "Ranges must be same size"
Else
For c = 1 To WorkRng.Count
WorkRng.Cells(c).Formula = "=If(" & WorkRng2.Cells(c).Value & ">0, ""D"",""C"")"
Next c
End If
End Sub
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
edited Nov 14 '18 at 20:25
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
answered Nov 14 '18 at 19:43
SJRSJR
12.7k31217
12.7k31217
Thanks a lot SJR for your explanation and your modification, it is solved my problem!
– Xavi
Nov 15 '18 at 8:57
add a comment |
Thanks a lot SJR for your explanation and your modification, it is solved my problem!
– Xavi
Nov 15 '18 at 8:57
Thanks a lot SJR for your explanation and your modification, it is solved my problem!
– Xavi
Nov 15 '18 at 8:57
Thanks a lot SJR for your explanation and your modification, it is solved my problem!
– Xavi
Nov 15 '18 at 8:57
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%2f53305006%2fformula-returning-name-inside-loop-via-selection-from-input-box%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
2
Excel will not know what
WorkRng2.Valueis [as part of a formula]. You might be able to replace it with" & WorkRng.Address & "?– CLR
Nov 14 '18 at 16:47
2
"=If(" & WorkRng2.Value & " >0, ""D"",""C"")"– Comintern
Nov 14 '18 at 16:47
Thank you CLR, thank you Comintern, I tried to insert your formula Comintern in the VBA code but when I run the macro, I get an error message : run time error 13, type mismatch.
– Xavi
Nov 14 '18 at 17:32