Excel Macro Reciprocal Conversion between two columns (lbs/kg)
This is my first question here.
I am very new to excel macro/vba.
Can someone please guide me on how to create a macro which does the following:
when someone enters value into either column B (in kgs)/ or column C (in lbs), the other column gets automatically updated to reflect the appropriate value. For example, if someone entered the kgs value in column B then column c would show the lbs value and if someone entered the lbs value in column c then column b would would show the appropriate lbs value.
Thanks for your help.
excel vba excel-vba
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
asked Nov 14 '18 at 18:08
xissa11xissa11
11
add a comment |
This is my first question here.
I am very new to excel macro/vba.
Can someone please guide me on how to create a macro which does the following:
when someone enters value into either column B (in kgs)/ or column C (in lbs), the other column gets automatically updated to reflect the appropriate value. For example, if someone entered the kgs value in column B then column c would show the lbs value and if someone entered the lbs value in column c then column b would would show the appropriate lbs value.
Thanks for your help.
excel vba excel-vba
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
asked Nov 14 '18 at 18:08
xissa11xissa11
11
I think this would be considered a circular reference so formulas may not work, but you can use VBA andWorksheet_Change
– Kubie
Nov 14 '18 at 18:09
You could write a macro triggered by a change in either cell that will insert the value in the other cell. this could get you started.
– cybernetic.nomad
Nov 14 '18 at 18:12
add a comment |
This is my first question here.
I am very new to excel macro/vba.
Can someone please guide me on how to create a macro which does the following:
when someone enters value into either column B (in kgs)/ or column C (in lbs), the other column gets automatically updated to reflect the appropriate value. For example, if someone entered the kgs value in column B then column c would show the lbs value and if someone entered the lbs value in column c then column b would would show the appropriate lbs value.
Thanks for your help.
excel vba excel-vba
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
asked Nov 14 '18 at 18:08
xissa11xissa11
11
This is my first question here.
I am very new to excel macro/vba.
Can someone please guide me on how to create a macro which does the following:
when someone enters value into either column B (in kgs)/ or column C (in lbs), the other column gets automatically updated to reflect the appropriate value. For example, if someone entered the kgs value in column B then column c would show the lbs value and if someone entered the lbs value in column c then column b would would show the appropriate lbs value.
Thanks for your help.
excel vba excel-vba
excel vba excel-vba
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
asked Nov 14 '18 at 18:08
xissa11xissa11
11
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
asked Nov 14 '18 at 18:08
xissa11xissa11
11
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
edited Nov 15 '18 at 7:47
Pᴇʜ
23k62950
23k62950
asked Nov 14 '18 at 18:08
xissa11xissa11
11
asked Nov 14 '18 at 18:08
xissa11xissa11
11
asked Nov 14 '18 at 18:08
xissa11xissa11
11
11
I think this would be considered a circular reference so formulas may not work, but you can use VBA andWorksheet_Change
– Kubie
Nov 14 '18 at 18:09
You could write a macro triggered by a change in either cell that will insert the value in the other cell. this could get you started.
– cybernetic.nomad
Nov 14 '18 at 18:12
add a comment |
I think this would be considered a circular reference so formulas may not work, but you can use VBA andWorksheet_Change
– Kubie
Nov 14 '18 at 18:09
You could write a macro triggered by a change in either cell that will insert the value in the other cell. this could get you started.
– cybernetic.nomad
Nov 14 '18 at 18:12
I think this would be considered a circular reference so formulas may not work, but you can use VBA and
Worksheet_Change– Kubie
Nov 14 '18 at 18:09
I think this would be considered a circular reference so formulas may not work, but you can use VBA and
Worksheet_Change– Kubie
Nov 14 '18 at 18:09
You could write a macro triggered by a change in either cell that will insert the value in the other cell. this could get you started.
– cybernetic.nomad
Nov 14 '18 at 18:12
You could write a macro triggered by a change in either cell that will insert the value in the other cell. this could get you started.
– cybernetic.nomad
Nov 14 '18 at 18:12
add a comment |
2 Answers
2
active
oldest
votes
If you go to the VBE Editor and double-click on the sheet you want to monitor, you can place this Sub there:
Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Variant
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
If Target.Column = 2 Then
' data entered in column B, so change C
Target.Offset(0,1).Value = Target.Value * 2.2
ElseIf Target.Column = 3 Then
' data entered in column C, so change B
Target.Offset(0,-1).Value = Target.Value / 2.2
End If
End Sub
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
I get a compile error while trying to run. it highlights: Else If Target.Column = 3 Then
– xissa11
Nov 14 '18 at 18:37
@xissa11 oops remove the space between Else and If
– Kubie
Nov 14 '18 at 18:39
1
thanks a bunch. works like a charm!
– xissa11
Nov 14 '18 at 18:46
@xissa11 just please keep in mind this can break if you copy & paste a bunch of values
– Kubie
Nov 14 '18 at 18:46
@xissa11 I added a line of code for it to exit if it detects a copy-paste
– Kubie
Nov 14 '18 at 18:56
add a comment |
Possible with a formula where that example would need to be adapted with appropriate conversion factors.
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
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%2f53306362%2fexcel-macro-reciprocal-conversion-between-two-columns-lbs-kg%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
If you go to the VBE Editor and double-click on the sheet you want to monitor, you can place this Sub there:
Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Variant
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
If Target.Column = 2 Then
' data entered in column B, so change C
Target.Offset(0,1).Value = Target.Value * 2.2
ElseIf Target.Column = 3 Then
' data entered in column C, so change B
Target.Offset(0,-1).Value = Target.Value / 2.2
End If
End Sub
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
I get a compile error while trying to run. it highlights: Else If Target.Column = 3 Then
– xissa11
Nov 14 '18 at 18:37
@xissa11 oops remove the space between Else and If
– Kubie
Nov 14 '18 at 18:39
1
thanks a bunch. works like a charm!
– xissa11
Nov 14 '18 at 18:46
@xissa11 just please keep in mind this can break if you copy & paste a bunch of values
– Kubie
Nov 14 '18 at 18:46
@xissa11 I added a line of code for it to exit if it detects a copy-paste
– Kubie
Nov 14 '18 at 18:56
add a comment |
If you go to the VBE Editor and double-click on the sheet you want to monitor, you can place this Sub there:
Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Variant
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
If Target.Column = 2 Then
' data entered in column B, so change C
Target.Offset(0,1).Value = Target.Value * 2.2
ElseIf Target.Column = 3 Then
' data entered in column C, so change B
Target.Offset(0,-1).Value = Target.Value / 2.2
End If
End Sub
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
I get a compile error while trying to run. it highlights: Else If Target.Column = 3 Then
– xissa11
Nov 14 '18 at 18:37
@xissa11 oops remove the space between Else and If
– Kubie
Nov 14 '18 at 18:39
1
thanks a bunch. works like a charm!
– xissa11
Nov 14 '18 at 18:46
@xissa11 just please keep in mind this can break if you copy & paste a bunch of values
– Kubie
Nov 14 '18 at 18:46
@xissa11 I added a line of code for it to exit if it detects a copy-paste
– Kubie
Nov 14 '18 at 18:56
add a comment |
If you go to the VBE Editor and double-click on the sheet you want to monitor, you can place this Sub there:
Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Variant
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
If Target.Column = 2 Then
' data entered in column B, so change C
Target.Offset(0,1).Value = Target.Value * 2.2
ElseIf Target.Column = 3 Then
' data entered in column C, so change B
Target.Offset(0,-1).Value = Target.Value / 2.2
End If
End Sub
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
If you go to the VBE Editor and double-click on the sheet you want to monitor, you can place this Sub there:
Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Variant
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
If Target.Column = 2 Then
' data entered in column B, so change C
Target.Offset(0,1).Value = Target.Value * 2.2
ElseIf Target.Column = 3 Then
' data entered in column C, so change B
Target.Offset(0,-1).Value = Target.Value / 2.2
End If
End Sub
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
edited Nov 14 '18 at 18:56
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
answered Nov 14 '18 at 18:23
KubieKubie
1,3421518
1,3421518
I get a compile error while trying to run. it highlights: Else If Target.Column = 3 Then
– xissa11
Nov 14 '18 at 18:37
@xissa11 oops remove the space between Else and If
– Kubie
Nov 14 '18 at 18:39
1
thanks a bunch. works like a charm!
– xissa11
Nov 14 '18 at 18:46
@xissa11 just please keep in mind this can break if you copy & paste a bunch of values
– Kubie
Nov 14 '18 at 18:46
@xissa11 I added a line of code for it to exit if it detects a copy-paste
– Kubie
Nov 14 '18 at 18:56
add a comment |
I get a compile error while trying to run. it highlights: Else If Target.Column = 3 Then
– xissa11
Nov 14 '18 at 18:37
@xissa11 oops remove the space between Else and If
– Kubie
Nov 14 '18 at 18:39
1
thanks a bunch. works like a charm!
– xissa11
Nov 14 '18 at 18:46
@xissa11 just please keep in mind this can break if you copy & paste a bunch of values
– Kubie
Nov 14 '18 at 18:46
@xissa11 I added a line of code for it to exit if it detects a copy-paste
– Kubie
Nov 14 '18 at 18:56
I get a compile error while trying to run. it highlights: Else If Target.Column = 3 Then
– xissa11
Nov 14 '18 at 18:37
I get a compile error while trying to run. it highlights: Else If Target.Column = 3 Then
– xissa11
Nov 14 '18 at 18:37
@xissa11 oops remove the space between Else and If
– Kubie
Nov 14 '18 at 18:39
@xissa11 oops remove the space between Else and If
– Kubie
Nov 14 '18 at 18:39
1
1
thanks a bunch. works like a charm!
– xissa11
Nov 14 '18 at 18:46
thanks a bunch. works like a charm!
– xissa11
Nov 14 '18 at 18:46
@xissa11 just please keep in mind this can break if you copy & paste a bunch of values
– Kubie
Nov 14 '18 at 18:46
@xissa11 just please keep in mind this can break if you copy & paste a bunch of values
– Kubie
Nov 14 '18 at 18:46
@xissa11 I added a line of code for it to exit if it detects a copy-paste
– Kubie
Nov 14 '18 at 18:56
@xissa11 I added a line of code for it to exit if it detects a copy-paste
– Kubie
Nov 14 '18 at 18:56
add a comment |
Possible with a formula where that example would need to be adapted with appropriate conversion factors.
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
add a comment |
Possible with a formula where that example would need to be adapted with appropriate conversion factors.
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
add a comment |
Possible with a formula where that example would need to be adapted with appropriate conversion factors.
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
Possible with a formula where that example would need to be adapted with appropriate conversion factors.
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
answered Nov 24 '18 at 13:07
pnutspnuts
48.5k76299
48.5k76299
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%2f53306362%2fexcel-macro-reciprocal-conversion-between-two-columns-lbs-kg%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
I think this would be considered a circular reference so formulas may not work, but you can use VBA and
Worksheet_Change– Kubie
Nov 14 '18 at 18:09
You could write a macro triggered by a change in either cell that will insert the value in the other cell. this could get you started.
– cybernetic.nomad
Nov 14 '18 at 18:12