Mixing
Choose the correct Private Sub
0
I have some VBA Code that works of a Control Button. The code work fine but I would like it to run automatically. I can't seem to get the Private Sub correct. Code is as follows:
Private Sub CommandButton3_Click()
Dim num As String
num = Range("L5").Value
Select Case num
Case Is = 15, 16, 17, 18, 19
Range("X5").Value = "510"
Case Is = 20, 21, 22, 23, 24
Range("X5").Value = "570"
Case Is = 25, 26, 27, 28, 29
Range("X5").Value = "610"
Case Is = 30, 31, 32, 33, 34
Range("X5").Value = "630"
Case Is = 35, 36, 37, 38, 39
Range("X5").Value = "635"
Case Is = 40, 41, 42, 43, 44
Range("X5").Value = "632"
Case Is = 45, 46, 47, 48, 49
Range("X5").Value = "622"
Case Is = 50, 51, 52, 53, 54
Range("X5").Value = "610"
Case Is = 55, 56, 57, 58, 59
Range("X5").Value = "590"
Case Is = 60, 61, 62, 63, 64
Range("X5").Value = "565"
Case Is = 65, 66, 67, 68, 69
Range("X5").Value = "540"
Case Is = 70, 71, 72, 73, 74
Range("X5").Value = "520"
Case Is = 75, 76, 78, 79
Range("X5").Value = "490"
Case Is = 80, 81, 82, 83, 84
Range("X5").Value = "470"
Case Is = 85, 86, 87, 88, 89, 90
Range("X5").Value = "440"
End Select
End Sub
excel vba excel-vba
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
|
show 2 more comments
0
I have some VBA Code that works of a Control Button. The code work fine but I would like it to run automatically. I can't seem to get the Private Sub correct. Code is as follows:
Private Sub CommandButton3_Click()
Dim num As String
num = Range("L5").Value
Select Case num
Case Is = 15, 16, 17, 18, 19
Range("X5").Value = "510"
Case Is = 20, 21, 22, 23, 24
Range("X5").Value = "570"
Case Is = 25, 26, 27, 28, 29
Range("X5").Value = "610"
Case Is = 30, 31, 32, 33, 34
Range("X5").Value = "630"
Case Is = 35, 36, 37, 38, 39
Range("X5").Value = "635"
Case Is = 40, 41, 42, 43, 44
Range("X5").Value = "632"
Case Is = 45, 46, 47, 48, 49
Range("X5").Value = "622"
Case Is = 50, 51, 52, 53, 54
Range("X5").Value = "610"
Case Is = 55, 56, 57, 58, 59
Range("X5").Value = "590"
Case Is = 60, 61, 62, 63, 64
Range("X5").Value = "565"
Case Is = 65, 66, 67, 68, 69
Range("X5").Value = "540"
Case Is = 70, 71, 72, 73, 74
Range("X5").Value = "520"
Case Is = 75, 76, 78, 79
Range("X5").Value = "490"
Case Is = 80, 81, 82, 83, 84
Range("X5").Value = "470"
Case Is = 85, 86, 87, 88, 89, 90
Range("X5").Value = "440"
End Select
End Sub
excel vba excel-vba
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
1
What do you mean by "run automatically"? When do you want the code to run?
– John Coleman
Nov 19 '18 at 23:28
So that it runs should Range("L5").Value change without having to press the control button.
– Father Goose
Nov 19 '18 at 23:31
it is unclear what behavior you want. Something has to trigger the code, and you need to explain what you want the trigger to be?
– ja72
Nov 19 '18 at 23:32
1
Move the code to aWorksheet_changeevent handler that monitors the target cell. See this question to see how it works.
– John Coleman
Nov 19 '18 at 23:33
Do you want the values to be text or numbers? When assigning.Value = "622"it puts the text622in the cell, not as a number.
– ja72
Nov 20 '18 at 0:05
|
show 2 more comments
0
0
0
I have some VBA Code that works of a Control Button. The code work fine but I would like it to run automatically. I can't seem to get the Private Sub correct. Code is as follows:
Private Sub CommandButton3_Click()
Dim num As String
num = Range("L5").Value
Select Case num
Case Is = 15, 16, 17, 18, 19
Range("X5").Value = "510"
Case Is = 20, 21, 22, 23, 24
Range("X5").Value = "570"
Case Is = 25, 26, 27, 28, 29
Range("X5").Value = "610"
Case Is = 30, 31, 32, 33, 34
Range("X5").Value = "630"
Case Is = 35, 36, 37, 38, 39
Range("X5").Value = "635"
Case Is = 40, 41, 42, 43, 44
Range("X5").Value = "632"
Case Is = 45, 46, 47, 48, 49
Range("X5").Value = "622"
Case Is = 50, 51, 52, 53, 54
Range("X5").Value = "610"
Case Is = 55, 56, 57, 58, 59
Range("X5").Value = "590"
Case Is = 60, 61, 62, 63, 64
Range("X5").Value = "565"
Case Is = 65, 66, 67, 68, 69
Range("X5").Value = "540"
Case Is = 70, 71, 72, 73, 74
Range("X5").Value = "520"
Case Is = 75, 76, 78, 79
Range("X5").Value = "490"
Case Is = 80, 81, 82, 83, 84
Range("X5").Value = "470"
Case Is = 85, 86, 87, 88, 89, 90
Range("X5").Value = "440"
End Select
End Sub
excel vba excel-vba
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
I have some VBA Code that works of a Control Button. The code work fine but I would like it to run automatically. I can't seem to get the Private Sub correct. Code is as follows:
Private Sub CommandButton3_Click()
Dim num As String
num = Range("L5").Value
Select Case num
Case Is = 15, 16, 17, 18, 19
Range("X5").Value = "510"
Case Is = 20, 21, 22, 23, 24
Range("X5").Value = "570"
Case Is = 25, 26, 27, 28, 29
Range("X5").Value = "610"
Case Is = 30, 31, 32, 33, 34
Range("X5").Value = "630"
Case Is = 35, 36, 37, 38, 39
Range("X5").Value = "635"
Case Is = 40, 41, 42, 43, 44
Range("X5").Value = "632"
Case Is = 45, 46, 47, 48, 49
Range("X5").Value = "622"
Case Is = 50, 51, 52, 53, 54
Range("X5").Value = "610"
Case Is = 55, 56, 57, 58, 59
Range("X5").Value = "590"
Case Is = 60, 61, 62, 63, 64
Range("X5").Value = "565"
Case Is = 65, 66, 67, 68, 69
Range("X5").Value = "540"
Case Is = 70, 71, 72, 73, 74
Range("X5").Value = "520"
Case Is = 75, 76, 78, 79
Range("X5").Value = "490"
Case Is = 80, 81, 82, 83, 84
Range("X5").Value = "470"
Case Is = 85, 86, 87, 88, 89, 90
Range("X5").Value = "440"
End Select
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
edited Nov 20 '18 at 7:14
Pᴇʜ
20.5k42650
20.5k42650
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
asked Nov 19 '18 at 23:23
Father GooseFather Goose
375
375
1
What do you mean by "run automatically"? When do you want the code to run?
– John Coleman
Nov 19 '18 at 23:28
So that it runs should Range("L5").Value change without having to press the control button.
– Father Goose
Nov 19 '18 at 23:31
it is unclear what behavior you want. Something has to trigger the code, and you need to explain what you want the trigger to be?
– ja72
Nov 19 '18 at 23:32
1
Move the code to aWorksheet_changeevent handler that monitors the target cell. See this question to see how it works.
– John Coleman
Nov 19 '18 at 23:33
Do you want the values to be text or numbers? When assigning.Value = "622"it puts the text622in the cell, not as a number.
– ja72
Nov 20 '18 at 0:05
|
show 2 more comments
1
What do you mean by "run automatically"? When do you want the code to run?
– John Coleman
Nov 19 '18 at 23:28
So that it runs should Range("L5").Value change without having to press the control button.
– Father Goose
Nov 19 '18 at 23:31
it is unclear what behavior you want. Something has to trigger the code, and you need to explain what you want the trigger to be?
– ja72
Nov 19 '18 at 23:32
1
Move the code to aWorksheet_changeevent handler that monitors the target cell. See this question to see how it works.
– John Coleman
Nov 19 '18 at 23:33
Do you want the values to be text or numbers? When assigning.Value = "622"it puts the text622in the cell, not as a number.
– ja72
Nov 20 '18 at 0:05
1
1
What do you mean by "run automatically"? When do you want the code to run?
– John Coleman
Nov 19 '18 at 23:28
What do you mean by "run automatically"? When do you want the code to run?
– John Coleman
Nov 19 '18 at 23:28
So that it runs should Range("L5").Value change without having to press the control button.
– Father Goose
Nov 19 '18 at 23:31
So that it runs should Range("L5").Value change without having to press the control button.
– Father Goose
Nov 19 '18 at 23:31
it is unclear what behavior you want. Something has to trigger the code, and you need to explain what you want the trigger to be?
– ja72
Nov 19 '18 at 23:32
it is unclear what behavior you want. Something has to trigger the code, and you need to explain what you want the trigger to be?
– ja72
Nov 19 '18 at 23:32
1
1
Move the code to a
Worksheet_change event handler that monitors the target cell. See this question to see how it works.– John Coleman
Nov 19 '18 at 23:33
Move the code to a
Worksheet_change event handler that monitors the target cell. See this question to see how it works.– John Coleman
Nov 19 '18 at 23:33
Do you want the values to be text or numbers? When assigning
.Value = "622" it puts the text 622 in the cell, not as a number.– ja72
Nov 20 '18 at 0:05
Do you want the values to be text or numbers? When assigning
.Value = "622" it puts the text 622 in the cell, not as a number.– ja72
Nov 20 '18 at 0:05
|
show 2 more comments
2 Answers
2
active
oldest
votes
1
You Don't need a case statement, you can do it in 1 line of code (plus an if to capture anything outside the range) because your brackets increment in a uniform way:
Public Function MyFunction2(ByVal MyValue As Double) As Double
If MyValue < 15 Or MyValue > 90 Then
MyFunction2 = 0
Else
MyFunction2 = CDbl(Array(0, 0, 0, 510, 570, 610, 630, 635, 632, 622, 610, 590, 565, 540, 520, 490, 470, 440)(Round(MyValue / 5, 0)))
End Function
It's a UDF so follow the same logic as ja72's answer but put MyFunction2 instead of MyFunction, put them side by side so you can compare that they both give the same results.
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
Exceptional, Thank you.
– Father Goose
Nov 20 '18 at 1:38
Happy to help..
– Dan Donoghue
Nov 20 '18 at 1:39
Of course, there is a difference in the results with my answer because a) You use a rounding functionRound()and I am using a floor functionCLng(), and b) every time the function is called, the array is allocated which is going to be considerably slower than my answer if this has to happen for lots of cells.
– ja72
Nov 20 '18 at 17:11
add a comment |
1
Create a user defined function and use it to update "X5" based on the contents of "L5"
In a module write the following code:
Public Function MyFunction(ByVal value As Double) As Double
Dim x As Long, y As Long
x = CLng(value)
Select Case x
Case 15 To 19
y = 510
Case 20 To 24
y = 570
Case 25 To 29
y = 610
Case 30 To 34
y = 630
Case 35 To 39
y = 635
Case 40 To 44
y = 632
Case 45 To 49
y = 622
Case 50 To 54
y = 610
Case 55 To 59
y = 590
Case 60 To 64
y = 565
Case 65 To 69
y = 540
Case 70 To 74
y = 520
Case 75 To 79
y = 490
Case 80 To 84
y = 470
Case 85 To 90
y = 440
Case Else
y = 0
End Select
MyFunction = CDbl(y)
End Function
The result will update automatically.
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
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
});
}
});
draft saved
draft discarded
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%2f53384088%2fchoose-the-correct-private-sub%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
1
You Don't need a case statement, you can do it in 1 line of code (plus an if to capture anything outside the range) because your brackets increment in a uniform way:
Public Function MyFunction2(ByVal MyValue As Double) As Double
If MyValue < 15 Or MyValue > 90 Then
MyFunction2 = 0
Else
MyFunction2 = CDbl(Array(0, 0, 0, 510, 570, 610, 630, 635, 632, 622, 610, 590, 565, 540, 520, 490, 470, 440)(Round(MyValue / 5, 0)))
End Function
It's a UDF so follow the same logic as ja72's answer but put MyFunction2 instead of MyFunction, put them side by side so you can compare that they both give the same results.
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
Exceptional, Thank you.
– Father Goose
Nov 20 '18 at 1:38
Happy to help..
– Dan Donoghue
Nov 20 '18 at 1:39
Of course, there is a difference in the results with my answer because a) You use a rounding functionRound()and I am using a floor functionCLng(), and b) every time the function is called, the array is allocated which is going to be considerably slower than my answer if this has to happen for lots of cells.
– ja72
Nov 20 '18 at 17:11
add a comment |
1
You Don't need a case statement, you can do it in 1 line of code (plus an if to capture anything outside the range) because your brackets increment in a uniform way:
Public Function MyFunction2(ByVal MyValue As Double) As Double
If MyValue < 15 Or MyValue > 90 Then
MyFunction2 = 0
Else
MyFunction2 = CDbl(Array(0, 0, 0, 510, 570, 610, 630, 635, 632, 622, 610, 590, 565, 540, 520, 490, 470, 440)(Round(MyValue / 5, 0)))
End Function
It's a UDF so follow the same logic as ja72's answer but put MyFunction2 instead of MyFunction, put them side by side so you can compare that they both give the same results.
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
Exceptional, Thank you.
– Father Goose
Nov 20 '18 at 1:38
Happy to help..
– Dan Donoghue
Nov 20 '18 at 1:39
Of course, there is a difference in the results with my answer because a) You use a rounding functionRound()and I am using a floor functionCLng(), and b) every time the function is called, the array is allocated which is going to be considerably slower than my answer if this has to happen for lots of cells.
– ja72
Nov 20 '18 at 17:11
add a comment |
1
1
1
You Don't need a case statement, you can do it in 1 line of code (plus an if to capture anything outside the range) because your brackets increment in a uniform way:
Public Function MyFunction2(ByVal MyValue As Double) As Double
If MyValue < 15 Or MyValue > 90 Then
MyFunction2 = 0
Else
MyFunction2 = CDbl(Array(0, 0, 0, 510, 570, 610, 630, 635, 632, 622, 610, 590, 565, 540, 520, 490, 470, 440)(Round(MyValue / 5, 0)))
End Function
It's a UDF so follow the same logic as ja72's answer but put MyFunction2 instead of MyFunction, put them side by side so you can compare that they both give the same results.
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
You Don't need a case statement, you can do it in 1 line of code (plus an if to capture anything outside the range) because your brackets increment in a uniform way:
Public Function MyFunction2(ByVal MyValue As Double) As Double
If MyValue < 15 Or MyValue > 90 Then
MyFunction2 = 0
Else
MyFunction2 = CDbl(Array(0, 0, 0, 510, 570, 610, 630, 635, 632, 622, 610, 590, 565, 540, 520, 490, 470, 440)(Round(MyValue / 5, 0)))
End Function
It's a UDF so follow the same logic as ja72's answer but put MyFunction2 instead of MyFunction, put them side by side so you can compare that they both give the same results.
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
edited Nov 20 '18 at 1:37
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
answered Nov 20 '18 at 1:26
Dan DonoghueDan Donoghue
4,6111631
4,6111631
Exceptional, Thank you.
– Father Goose
Nov 20 '18 at 1:38
Happy to help..
– Dan Donoghue
Nov 20 '18 at 1:39
Of course, there is a difference in the results with my answer because a) You use a rounding functionRound()and I am using a floor functionCLng(), and b) every time the function is called, the array is allocated which is going to be considerably slower than my answer if this has to happen for lots of cells.
– ja72
Nov 20 '18 at 17:11
add a comment |
Exceptional, Thank you.
– Father Goose
Nov 20 '18 at 1:38
Happy to help..
– Dan Donoghue
Nov 20 '18 at 1:39
Of course, there is a difference in the results with my answer because a) You use a rounding functionRound()and I am using a floor functionCLng(), and b) every time the function is called, the array is allocated which is going to be considerably slower than my answer if this has to happen for lots of cells.
– ja72
Nov 20 '18 at 17:11
Exceptional, Thank you.
– Father Goose
Nov 20 '18 at 1:38
Exceptional, Thank you.
– Father Goose
Nov 20 '18 at 1:38
Happy to help..
– Dan Donoghue
Nov 20 '18 at 1:39
Happy to help..
– Dan Donoghue
Nov 20 '18 at 1:39
Of course, there is a difference in the results with my answer because a) You use a rounding function
Round() and I am using a floor function CLng(), and b) every time the function is called, the array is allocated which is going to be considerably slower than my answer if this has to happen for lots of cells.– ja72
Nov 20 '18 at 17:11
Of course, there is a difference in the results with my answer because a) You use a rounding function
Round() and I am using a floor function CLng(), and b) every time the function is called, the array is allocated which is going to be considerably slower than my answer if this has to happen for lots of cells.– ja72
Nov 20 '18 at 17:11
add a comment |
1
Create a user defined function and use it to update "X5" based on the contents of "L5"
In a module write the following code:
Public Function MyFunction(ByVal value As Double) As Double
Dim x As Long, y As Long
x = CLng(value)
Select Case x
Case 15 To 19
y = 510
Case 20 To 24
y = 570
Case 25 To 29
y = 610
Case 30 To 34
y = 630
Case 35 To 39
y = 635
Case 40 To 44
y = 632
Case 45 To 49
y = 622
Case 50 To 54
y = 610
Case 55 To 59
y = 590
Case 60 To 64
y = 565
Case 65 To 69
y = 540
Case 70 To 74
y = 520
Case 75 To 79
y = 490
Case 80 To 84
y = 470
Case 85 To 90
y = 440
Case Else
y = 0
End Select
MyFunction = CDbl(y)
End Function
The result will update automatically.
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
add a comment |
1
Create a user defined function and use it to update "X5" based on the contents of "L5"
In a module write the following code:
Public Function MyFunction(ByVal value As Double) As Double
Dim x As Long, y As Long
x = CLng(value)
Select Case x
Case 15 To 19
y = 510
Case 20 To 24
y = 570
Case 25 To 29
y = 610
Case 30 To 34
y = 630
Case 35 To 39
y = 635
Case 40 To 44
y = 632
Case 45 To 49
y = 622
Case 50 To 54
y = 610
Case 55 To 59
y = 590
Case 60 To 64
y = 565
Case 65 To 69
y = 540
Case 70 To 74
y = 520
Case 75 To 79
y = 490
Case 80 To 84
y = 470
Case 85 To 90
y = 440
Case Else
y = 0
End Select
MyFunction = CDbl(y)
End Function
The result will update automatically.
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
add a comment |
1
1
1
Create a user defined function and use it to update "X5" based on the contents of "L5"
In a module write the following code:
Public Function MyFunction(ByVal value As Double) As Double
Dim x As Long, y As Long
x = CLng(value)
Select Case x
Case 15 To 19
y = 510
Case 20 To 24
y = 570
Case 25 To 29
y = 610
Case 30 To 34
y = 630
Case 35 To 39
y = 635
Case 40 To 44
y = 632
Case 45 To 49
y = 622
Case 50 To 54
y = 610
Case 55 To 59
y = 590
Case 60 To 64
y = 565
Case 65 To 69
y = 540
Case 70 To 74
y = 520
Case 75 To 79
y = 490
Case 80 To 84
y = 470
Case 85 To 90
y = 440
Case Else
y = 0
End Select
MyFunction = CDbl(y)
End Function
The result will update automatically.
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
Create a user defined function and use it to update "X5" based on the contents of "L5"
In a module write the following code:
Public Function MyFunction(ByVal value As Double) As Double
Dim x As Long, y As Long
x = CLng(value)
Select Case x
Case 15 To 19
y = 510
Case 20 To 24
y = 570
Case 25 To 29
y = 610
Case 30 To 34
y = 630
Case 35 To 39
y = 635
Case 40 To 44
y = 632
Case 45 To 49
y = 622
Case 50 To 54
y = 610
Case 55 To 59
y = 590
Case 60 To 64
y = 565
Case 65 To 69
y = 540
Case 70 To 74
y = 520
Case 75 To 79
y = 490
Case 80 To 84
y = 470
Case 85 To 90
y = 440
Case Else
y = 0
End Select
MyFunction = CDbl(y)
End Function
The result will update automatically.
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
answered Nov 20 '18 at 0:00
ja72ja72
18.1k347103
18.1k347103
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53384088%2fchoose-the-correct-private-sub%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
1
What do you mean by "run automatically"? When do you want the code to run?
– John Coleman
Nov 19 '18 at 23:28
So that it runs should Range("L5").Value change without having to press the control button.
– Father Goose
Nov 19 '18 at 23:31
it is unclear what behavior you want. Something has to trigger the code, and you need to explain what you want the trigger to be?
– ja72
Nov 19 '18 at 23:32
1
Move the code to a
Worksheet_changeevent handler that monitors the target cell. See this question to see how it works.– John Coleman
Nov 19 '18 at 23:33
Do you want the values to be text or numbers? When assigning
.Value = "622"it puts the text622in the cell, not as a number.– ja72
Nov 20 '18 at 0:05