Using select/case to select correct date from a certain cell, subtract months listed in another cell and...
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I am trying to use Select/Case that if Column 6 active row matches a certain number then it goes to a certain cell that has a date in m/dd/yyyy format and subtracts the amount of months listed in number format Column 8 for that active row and puts it into Column 9 of that active row.
Issue is after it reads the value of Column 6 and proceeds to S.Cells(E, 9) = p4 - S.Cells(E, 8). It is throwing a mismatch error on Phase4 = S.Cells(2, 13).
Thanks in advance and let me know if I did not explain it correctly :)
Here is a screenshot of the layout.
Dim E As Integer
Dim Phase1 As Integer
Dim Phase2 As Integer
Dim Phase3 As Integer
Dim Phase4 As Integer
Dim Phase5 As Integer
Dim Phase6 As Integer
Dim Phase7 As Integer
Dim Phase8 As Integer
Dim p1 As Date
Dim p2 As Date
Dim p3 As Date
Dim p4 As Date
Dim p5 As Date
Dim p6 As Date
Dim p7 As Date
Dim p8 As Date
''
E = 2
Set S = Worksheets(ActiveSheet.Name)
''
'''''''''''''''''''''TUM CALCULATION'''''''''''''''''''''''
Phase1 = S.Cells(2, 10)
Phase2 = S.Cells(2, 11)
Phase3 = S.Cells(2, 12)
Phase4 = S.Cells(2, 13)
Phase5 = S.Cells(2, 14)
Phase6 = S.Cells(2, 15)
Phase7 = S.Cells(2, 16)
Phase8 = S.Cells(2, 17)
'''''''''''''''''''''''
p1 = DateValue(Phase1)
p2 = DateValue(Phase2)
p3 = DateValue(Phase3)
p4 = DateValue(Phase4)
p5 = DateValue(Phase5)
p6 = DateValue(Phase6)
p7 = DateValue(Phase7)
p8 = DateValue(Phase8)
'''''''''''''''''''''''
Select Case Right(S.Cells(E, 6), 1)
Case "1"
S.Cells(E, 9) = (p1 - S.Cells(E, 8))
Case "2"
S.Cells(E, 9) = (p2 - S.Cells(E, 8))
Case "3"
S.Cells(E, 9) = (p3 - S.Cells(E, 8))
Case "4"
S.Cells(E, 9) = (p4 - S.Cells(E, 8))
Case "5"
S.Cells(E, 9) = (p5 - S.Cells(E, 8))
Case "6"
S.Cells(E, 9) = (p6 - S.Cells(E, 8))
Case "7"
S.Cells(E, 9) = (p7 - S.Cells(E, 8))
Case "8"
S.Cells(E, 9) = (p8 - S.Cells(E, 8))
End Select
excel vba
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
|
show 7 more comments
0
I am trying to use Select/Case that if Column 6 active row matches a certain number then it goes to a certain cell that has a date in m/dd/yyyy format and subtracts the amount of months listed in number format Column 8 for that active row and puts it into Column 9 of that active row.
Issue is after it reads the value of Column 6 and proceeds to S.Cells(E, 9) = p4 - S.Cells(E, 8). It is throwing a mismatch error on Phase4 = S.Cells(2, 13).
Thanks in advance and let me know if I did not explain it correctly :)
Here is a screenshot of the layout.
Dim E As Integer
Dim Phase1 As Integer
Dim Phase2 As Integer
Dim Phase3 As Integer
Dim Phase4 As Integer
Dim Phase5 As Integer
Dim Phase6 As Integer
Dim Phase7 As Integer
Dim Phase8 As Integer
Dim p1 As Date
Dim p2 As Date
Dim p3 As Date
Dim p4 As Date
Dim p5 As Date
Dim p6 As Date
Dim p7 As Date
Dim p8 As Date
''
E = 2
Set S = Worksheets(ActiveSheet.Name)
''
'''''''''''''''''''''TUM CALCULATION'''''''''''''''''''''''
Phase1 = S.Cells(2, 10)
Phase2 = S.Cells(2, 11)
Phase3 = S.Cells(2, 12)
Phase4 = S.Cells(2, 13)
Phase5 = S.Cells(2, 14)
Phase6 = S.Cells(2, 15)
Phase7 = S.Cells(2, 16)
Phase8 = S.Cells(2, 17)
'''''''''''''''''''''''
p1 = DateValue(Phase1)
p2 = DateValue(Phase2)
p3 = DateValue(Phase3)
p4 = DateValue(Phase4)
p5 = DateValue(Phase5)
p6 = DateValue(Phase6)
p7 = DateValue(Phase7)
p8 = DateValue(Phase8)
'''''''''''''''''''''''
Select Case Right(S.Cells(E, 6), 1)
Case "1"
S.Cells(E, 9) = (p1 - S.Cells(E, 8))
Case "2"
S.Cells(E, 9) = (p2 - S.Cells(E, 8))
Case "3"
S.Cells(E, 9) = (p3 - S.Cells(E, 8))
Case "4"
S.Cells(E, 9) = (p4 - S.Cells(E, 8))
Case "5"
S.Cells(E, 9) = (p5 - S.Cells(E, 8))
Case "6"
S.Cells(E, 9) = (p6 - S.Cells(E, 8))
Case "7"
S.Cells(E, 9) = (p7 - S.Cells(E, 8))
Case "8"
S.Cells(E, 9) = (p8 - S.Cells(E, 8))
End Select
excel vba
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
1
Okay, you've stated what you're trying to do, but what is the actual problem and your question?
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
AndSet S = Worksheets(ActiveSheet.Name)can be simplified toSet S = ActiveSheet. But it's strange that you declared all your other variables but you didn'tDim S As Worksheet
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
Can you explain a bit more about the "certain number" and "certain cell" please. I tried to figure out using the "certain number" of 4 which occurs in rows2:7. But ifE=2thenSelect Case Right(S.Cells(E, 6), 1)will always look atF2rather than a "certain cell". Then looking at everything else it always looks at row 2... I'm confused.
– Darren Bartrup-Cook
Nov 16 '18 at 17:02
1
In that case can you read Minimal, Complete, and Verifiable example please.
– Darren Bartrup-Cook
Nov 16 '18 at 17:06
1
However, the other cells should be giving you an overflow error, since dates are stored as serial numbers with1 = 1-Jan-1900and an Integer datatype can only contain up to2^15, unless those dates are much earlier (approx. before Nov 1989) than what you show above.
– Ron Rosenfeld
Nov 16 '18 at 17:50
|
show 7 more comments
0
0
0
I am trying to use Select/Case that if Column 6 active row matches a certain number then it goes to a certain cell that has a date in m/dd/yyyy format and subtracts the amount of months listed in number format Column 8 for that active row and puts it into Column 9 of that active row.
Issue is after it reads the value of Column 6 and proceeds to S.Cells(E, 9) = p4 - S.Cells(E, 8). It is throwing a mismatch error on Phase4 = S.Cells(2, 13).
Thanks in advance and let me know if I did not explain it correctly :)
Here is a screenshot of the layout.
Dim E As Integer
Dim Phase1 As Integer
Dim Phase2 As Integer
Dim Phase3 As Integer
Dim Phase4 As Integer
Dim Phase5 As Integer
Dim Phase6 As Integer
Dim Phase7 As Integer
Dim Phase8 As Integer
Dim p1 As Date
Dim p2 As Date
Dim p3 As Date
Dim p4 As Date
Dim p5 As Date
Dim p6 As Date
Dim p7 As Date
Dim p8 As Date
''
E = 2
Set S = Worksheets(ActiveSheet.Name)
''
'''''''''''''''''''''TUM CALCULATION'''''''''''''''''''''''
Phase1 = S.Cells(2, 10)
Phase2 = S.Cells(2, 11)
Phase3 = S.Cells(2, 12)
Phase4 = S.Cells(2, 13)
Phase5 = S.Cells(2, 14)
Phase6 = S.Cells(2, 15)
Phase7 = S.Cells(2, 16)
Phase8 = S.Cells(2, 17)
'''''''''''''''''''''''
p1 = DateValue(Phase1)
p2 = DateValue(Phase2)
p3 = DateValue(Phase3)
p4 = DateValue(Phase4)
p5 = DateValue(Phase5)
p6 = DateValue(Phase6)
p7 = DateValue(Phase7)
p8 = DateValue(Phase8)
'''''''''''''''''''''''
Select Case Right(S.Cells(E, 6), 1)
Case "1"
S.Cells(E, 9) = (p1 - S.Cells(E, 8))
Case "2"
S.Cells(E, 9) = (p2 - S.Cells(E, 8))
Case "3"
S.Cells(E, 9) = (p3 - S.Cells(E, 8))
Case "4"
S.Cells(E, 9) = (p4 - S.Cells(E, 8))
Case "5"
S.Cells(E, 9) = (p5 - S.Cells(E, 8))
Case "6"
S.Cells(E, 9) = (p6 - S.Cells(E, 8))
Case "7"
S.Cells(E, 9) = (p7 - S.Cells(E, 8))
Case "8"
S.Cells(E, 9) = (p8 - S.Cells(E, 8))
End Select
excel vba
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
I am trying to use Select/Case that if Column 6 active row matches a certain number then it goes to a certain cell that has a date in m/dd/yyyy format and subtracts the amount of months listed in number format Column 8 for that active row and puts it into Column 9 of that active row.
Issue is after it reads the value of Column 6 and proceeds to S.Cells(E, 9) = p4 - S.Cells(E, 8). It is throwing a mismatch error on Phase4 = S.Cells(2, 13).
Thanks in advance and let me know if I did not explain it correctly :)
Here is a screenshot of the layout.
Dim E As Integer
Dim Phase1 As Integer
Dim Phase2 As Integer
Dim Phase3 As Integer
Dim Phase4 As Integer
Dim Phase5 As Integer
Dim Phase6 As Integer
Dim Phase7 As Integer
Dim Phase8 As Integer
Dim p1 As Date
Dim p2 As Date
Dim p3 As Date
Dim p4 As Date
Dim p5 As Date
Dim p6 As Date
Dim p7 As Date
Dim p8 As Date
''
E = 2
Set S = Worksheets(ActiveSheet.Name)
''
'''''''''''''''''''''TUM CALCULATION'''''''''''''''''''''''
Phase1 = S.Cells(2, 10)
Phase2 = S.Cells(2, 11)
Phase3 = S.Cells(2, 12)
Phase4 = S.Cells(2, 13)
Phase5 = S.Cells(2, 14)
Phase6 = S.Cells(2, 15)
Phase7 = S.Cells(2, 16)
Phase8 = S.Cells(2, 17)
'''''''''''''''''''''''
p1 = DateValue(Phase1)
p2 = DateValue(Phase2)
p3 = DateValue(Phase3)
p4 = DateValue(Phase4)
p5 = DateValue(Phase5)
p6 = DateValue(Phase6)
p7 = DateValue(Phase7)
p8 = DateValue(Phase8)
'''''''''''''''''''''''
Select Case Right(S.Cells(E, 6), 1)
Case "1"
S.Cells(E, 9) = (p1 - S.Cells(E, 8))
Case "2"
S.Cells(E, 9) = (p2 - S.Cells(E, 8))
Case "3"
S.Cells(E, 9) = (p3 - S.Cells(E, 8))
Case "4"
S.Cells(E, 9) = (p4 - S.Cells(E, 8))
Case "5"
S.Cells(E, 9) = (p5 - S.Cells(E, 8))
Case "6"
S.Cells(E, 9) = (p6 - S.Cells(E, 8))
Case "7"
S.Cells(E, 9) = (p7 - S.Cells(E, 8))
Case "8"
S.Cells(E, 9) = (p8 - S.Cells(E, 8))
End Select
excel vba
excel vba
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
edited Nov 16 '18 at 17:04
Joshua Ivy
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
asked Nov 16 '18 at 16:27
Joshua IvyJoshua Ivy
11
11
1
Okay, you've stated what you're trying to do, but what is the actual problem and your question?
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
AndSet S = Worksheets(ActiveSheet.Name)can be simplified toSet S = ActiveSheet. But it's strange that you declared all your other variables but you didn'tDim S As Worksheet
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
Can you explain a bit more about the "certain number" and "certain cell" please. I tried to figure out using the "certain number" of 4 which occurs in rows2:7. But ifE=2thenSelect Case Right(S.Cells(E, 6), 1)will always look atF2rather than a "certain cell". Then looking at everything else it always looks at row 2... I'm confused.
– Darren Bartrup-Cook
Nov 16 '18 at 17:02
1
In that case can you read Minimal, Complete, and Verifiable example please.
– Darren Bartrup-Cook
Nov 16 '18 at 17:06
1
However, the other cells should be giving you an overflow error, since dates are stored as serial numbers with1 = 1-Jan-1900and an Integer datatype can only contain up to2^15, unless those dates are much earlier (approx. before Nov 1989) than what you show above.
– Ron Rosenfeld
Nov 16 '18 at 17:50
|
show 7 more comments
1
Okay, you've stated what you're trying to do, but what is the actual problem and your question?
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
AndSet S = Worksheets(ActiveSheet.Name)can be simplified toSet S = ActiveSheet. But it's strange that you declared all your other variables but you didn'tDim S As Worksheet
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
Can you explain a bit more about the "certain number" and "certain cell" please. I tried to figure out using the "certain number" of 4 which occurs in rows2:7. But ifE=2thenSelect Case Right(S.Cells(E, 6), 1)will always look atF2rather than a "certain cell". Then looking at everything else it always looks at row 2... I'm confused.
– Darren Bartrup-Cook
Nov 16 '18 at 17:02
1
In that case can you read Minimal, Complete, and Verifiable example please.
– Darren Bartrup-Cook
Nov 16 '18 at 17:06
1
However, the other cells should be giving you an overflow error, since dates are stored as serial numbers with1 = 1-Jan-1900and an Integer datatype can only contain up to2^15, unless those dates are much earlier (approx. before Nov 1989) than what you show above.
– Ron Rosenfeld
Nov 16 '18 at 17:50
1
1
Okay, you've stated what you're trying to do, but what is the actual problem and your question?
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
Okay, you've stated what you're trying to do, but what is the actual problem and your question?
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
And
Set S = Worksheets(ActiveSheet.Name) can be simplified to Set S = ActiveSheet. But it's strange that you declared all your other variables but you didn't Dim S As Worksheet– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
And
Set S = Worksheets(ActiveSheet.Name) can be simplified to Set S = ActiveSheet. But it's strange that you declared all your other variables but you didn't Dim S As Worksheet– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
Can you explain a bit more about the "certain number" and "certain cell" please. I tried to figure out using the "certain number" of 4 which occurs in rows
2:7. But if E=2 then Select Case Right(S.Cells(E, 6), 1) will always look at F2 rather than a "certain cell". Then looking at everything else it always looks at row 2... I'm confused.– Darren Bartrup-Cook
Nov 16 '18 at 17:02
Can you explain a bit more about the "certain number" and "certain cell" please. I tried to figure out using the "certain number" of 4 which occurs in rows
2:7. But if E=2 then Select Case Right(S.Cells(E, 6), 1) will always look at F2 rather than a "certain cell". Then looking at everything else it always looks at row 2... I'm confused.– Darren Bartrup-Cook
Nov 16 '18 at 17:02
1
1
In that case can you read Minimal, Complete, and Verifiable example please.
– Darren Bartrup-Cook
Nov 16 '18 at 17:06
In that case can you read Minimal, Complete, and Verifiable example please.
– Darren Bartrup-Cook
Nov 16 '18 at 17:06
1
1
However, the other cells should be giving you an overflow error, since dates are stored as serial numbers with
1 = 1-Jan-1900 and an Integer datatype can only contain up to 2^15, unless those dates are much earlier (approx. before Nov 1989) than what you show above.– Ron Rosenfeld
Nov 16 '18 at 17:50
However, the other cells should be giving you an overflow error, since dates are stored as serial numbers with
1 = 1-Jan-1900 and an Integer datatype can only contain up to 2^15, unless those dates are much earlier (approx. before Nov 1989) than what you show above.– Ron Rosenfeld
Nov 16 '18 at 17:50
|
show 7 more comments
0
active
oldest
votes
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%2f53341844%2fusing-select-case-to-select-correct-date-from-a-certain-cell-subtract-months-li%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53341844%2fusing-select-case-to-select-correct-date-from-a-certain-cell-subtract-months-li%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
Okay, you've stated what you're trying to do, but what is the actual problem and your question?
– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
And
Set S = Worksheets(ActiveSheet.Name)can be simplified toSet S = ActiveSheet. But it's strange that you declared all your other variables but you didn'tDim S As Worksheet– K.Dᴀᴠɪs
Nov 16 '18 at 16:49
Can you explain a bit more about the "certain number" and "certain cell" please. I tried to figure out using the "certain number" of 4 which occurs in rows
2:7. But ifE=2thenSelect Case Right(S.Cells(E, 6), 1)will always look atF2rather than a "certain cell". Then looking at everything else it always looks at row 2... I'm confused.– Darren Bartrup-Cook
Nov 16 '18 at 17:02
1
In that case can you read Minimal, Complete, and Verifiable example please.
– Darren Bartrup-Cook
Nov 16 '18 at 17:06
1
However, the other cells should be giving you an overflow error, since dates are stored as serial numbers with
1 = 1-Jan-1900and an Integer datatype can only contain up to2^15, unless those dates are much earlier (approx. before Nov 1989) than what you show above.– Ron Rosenfeld
Nov 16 '18 at 17:50