Excel VBA - finding max for winter months

-1

I'm writing a macro public function for finding max of the last N non empty cells for winter months (November, December, January, February).

Here's what I got:

Public Function SuperMax_Winter(rng2 As Range, rng As Range, N As Long) As Double

Dim RngCnt, RngCnt2 As Long, i As Long, Zum As Double, j As Long

Dim ary() As Double

ReDim ary(0)



j = 0

RngCnt = rng.Count

RngCnt2 = rng2.Count



If RngCnt <> RngCnt2 Then SuperMax_Winter = "#ERROR!"



For i = RngCnt To 1 Step -1

    If rng(i).Value <> "" Then

        If rng2(i).Month = 11 Or rng2(i).Month = 12 Or rng2(i).Month = 1 Or rng2(i).Month = 2 Then

            ary(j) = rng(i).Value

            If j = N - 1 Then Exit For

            ReDim Preserve ary(j + 1)

            j = j + 1

        End If

    End If

Next i



SuperMax_Winter = Application.WorksheetFunction.Max(ary)



End Function

But I get a #VALUE! error.

excel vba excel-vba

share|improve this question

edited Nov 19 '18 at 13:10

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

• 
  
  
  

  
  

  
  
  
  
  
  

  
  you are using Exit Sub in a function, so it wont work.
  – Nathan_Sav
  Nov 19 '18 at 13:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I've noticed that and have corrected it. But it's not that.
  – Creakush Coliko
  Nov 19 '18 at 13:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  also, .... month(rng2(1)) not .month
  – Nathan_Sav
  Nov 19 '18 at 13:10
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Could you explain further how your code doesn't work. It doesn't return the last N non empty cells? Does rng2 or rng hold a reference to winter months? How does rng2(i).Month work? E.g If I have 15/12/18 in A1 then Range("A1").Month returns a Object doesn't support this property or method error.
  – Darren Bartrup-Cook
  Nov 19 '18 at 13:13
  

  
  
  
  

 | 
show 2 more comments

-1

I'm writing a macro public function for finding max of the last N non empty cells for winter months (November, December, January, February).

Here's what I got:

Public Function SuperMax_Winter(rng2 As Range, rng As Range, N As Long) As Double

Dim RngCnt, RngCnt2 As Long, i As Long, Zum As Double, j As Long

Dim ary() As Double

ReDim ary(0)



j = 0

RngCnt = rng.Count

RngCnt2 = rng2.Count



If RngCnt <> RngCnt2 Then SuperMax_Winter = "#ERROR!"



For i = RngCnt To 1 Step -1

    If rng(i).Value <> "" Then

        If rng2(i).Month = 11 Or rng2(i).Month = 12 Or rng2(i).Month = 1 Or rng2(i).Month = 2 Then

            ary(j) = rng(i).Value

            If j = N - 1 Then Exit For

            ReDim Preserve ary(j + 1)

            j = j + 1

        End If

    End If

Next i



SuperMax_Winter = Application.WorksheetFunction.Max(ary)



End Function

But I get a #VALUE! error.

excel vba excel-vba

share|improve this question

edited Nov 19 '18 at 13:10

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

• 
  
  
  

  
  

  
  
  
  
  
  

  
  you are using Exit Sub in a function, so it wont work.
  – Nathan_Sav
  Nov 19 '18 at 13:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I've noticed that and have corrected it. But it's not that.
  – Creakush Coliko
  Nov 19 '18 at 13:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  also, .... month(rng2(1)) not .month
  – Nathan_Sav
  Nov 19 '18 at 13:10
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Could you explain further how your code doesn't work. It doesn't return the last N non empty cells? Does rng2 or rng hold a reference to winter months? How does rng2(i).Month work? E.g If I have 15/12/18 in A1 then Range("A1").Month returns a Object doesn't support this property or method error.
  – Darren Bartrup-Cook
  Nov 19 '18 at 13:13
  

  
  
  
  

 | 
show 2 more comments

-1

-1

-1

I'm writing a macro public function for finding max of the last N non empty cells for winter months (November, December, January, February).

Here's what I got:

Public Function SuperMax_Winter(rng2 As Range, rng As Range, N As Long) As Double

Dim RngCnt, RngCnt2 As Long, i As Long, Zum As Double, j As Long

Dim ary() As Double

ReDim ary(0)



j = 0

RngCnt = rng.Count

RngCnt2 = rng2.Count



If RngCnt <> RngCnt2 Then SuperMax_Winter = "#ERROR!"



For i = RngCnt To 1 Step -1

    If rng(i).Value <> "" Then

        If rng2(i).Month = 11 Or rng2(i).Month = 12 Or rng2(i).Month = 1 Or rng2(i).Month = 2 Then

            ary(j) = rng(i).Value

            If j = N - 1 Then Exit For

            ReDim Preserve ary(j + 1)

            j = j + 1

        End If

    End If

Next i



SuperMax_Winter = Application.WorksheetFunction.Max(ary)



End Function

But I get a #VALUE! error.

excel vba excel-vba

share|improve this question

edited Nov 19 '18 at 13:10

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

I'm writing a macro public function for finding max of the last N non empty cells for winter months (November, December, January, February).

Here's what I got:

Public Function SuperMax_Winter(rng2 As Range, rng As Range, N As Long) As Double

Dim RngCnt, RngCnt2 As Long, i As Long, Zum As Double, j As Long

Dim ary() As Double

ReDim ary(0)



j = 0

RngCnt = rng.Count

RngCnt2 = rng2.Count



If RngCnt <> RngCnt2 Then SuperMax_Winter = "#ERROR!"



For i = RngCnt To 1 Step -1

    If rng(i).Value <> "" Then

        If rng2(i).Month = 11 Or rng2(i).Month = 12 Or rng2(i).Month = 1 Or rng2(i).Month = 2 Then

            ary(j) = rng(i).Value

            If j = N - 1 Then Exit For

            ReDim Preserve ary(j + 1)

            j = j + 1

        End If

    End If

Next i



SuperMax_Winter = Application.WorksheetFunction.Max(ary)



End Function

But I get a #VALUE! error.

excel vba excel-vba

excel vba excel-vba

share|improve this question

edited Nov 19 '18 at 13:10

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

share|improve this question

edited Nov 19 '18 at 13:10

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

share|improve this question

share|improve this question

edited Nov 19 '18 at 13:10

edited Nov 19 '18 at 13:10

edited Nov 19 '18 at 13:10

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

asked Nov 19 '18 at 13:00

Creakush Coliko

3819

3819

• 
  
  
  

  
  

  
  
  
  
  
  

  
  you are using Exit Sub in a function, so it wont work.
  – Nathan_Sav
  Nov 19 '18 at 13:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I've noticed that and have corrected it. But it's not that.
  – Creakush Coliko
  Nov 19 '18 at 13:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  also, .... month(rng2(1)) not .month
  – Nathan_Sav
  Nov 19 '18 at 13:10
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Could you explain further how your code doesn't work. It doesn't return the last N non empty cells? Does rng2 or rng hold a reference to winter months? How does rng2(i).Month work? E.g If I have 15/12/18 in A1 then Range("A1").Month returns a Object doesn't support this property or method error.
  – Darren Bartrup-Cook
  Nov 19 '18 at 13:13
  

  
  
  
  

 | 
show 2 more comments

• 
  
  
  

  
  

  
  
  
  
  
  

  
  you are using Exit Sub in a function, so it wont work.
  – Nathan_Sav
  Nov 19 '18 at 13:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I've noticed that and have corrected it. But it's not that.
  – Creakush Coliko
  Nov 19 '18 at 13:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  also, .... month(rng2(1)) not .month
  – Nathan_Sav
  Nov 19 '18 at 13:10
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Could you explain further how your code doesn't work. It doesn't return the last N non empty cells? Does rng2 or rng hold a reference to winter months? How does rng2(i).Month work? E.g If I have 15/12/18 in A1 then Range("A1").Month returns a Object doesn't support this property or method error.
  – Darren Bartrup-Cook
  Nov 19 '18 at 13:13
  

  
  
  
  

you are using Exit Sub in a function, so it wont work.
– Nathan_Sav
Nov 19 '18 at 13:08

you are using Exit Sub in a function, so it wont work.
– Nathan_Sav
Nov 19 '18 at 13:08

I've noticed that and have corrected it. But it's not that.
– Creakush Coliko
Nov 19 '18 at 13:10

I've noticed that and have corrected it. But it's not that.
– Creakush Coliko
Nov 19 '18 at 13:10

also, .... month(rng2(1)) not .month
– Nathan_Sav
Nov 19 '18 at 13:10

also, .... month(rng2(1)) not .month
– Nathan_Sav
Nov 19 '18 at 13:10

That's it thanks!
– Creakush Coliko
Nov 19 '18 at 13:13

That's it thanks!
– Creakush Coliko
Nov 19 '18 at 13:13

Could you explain further how your code doesn't work. It doesn't return the last N non empty cells? Does rng2 or rng hold a reference to winter months? How does rng2(i).Month work? E.g If I have 15/12/18 in A1 then Range("A1").Month returns a Object doesn't support this property or method error.
– Darren Bartrup-Cook
Nov 19 '18 at 13:13

Could you explain further how your code doesn't work. It doesn't return the last N non empty cells? Does rng2 or rng hold a reference to winter months? How does rng2(i).Month work? E.g If I have 15/12/18 in A1 then Range("A1").Month returns a Object doesn't support this property or method error.
– Darren Bartrup-Cook
Nov 19 '18 at 13:13

 | 
show 2 more comments

1 Answer
1

active

oldest

votes

1

I think Month should be first:

If Month(rng2(i).Value)= 11 Or Month(rng2(i).Value)= 12 Or Month(rng2(i).Value)= 1 Or Month(rng2(i).Value)= 2 Then

Hope this help.

share|improve this answer

answered Nov 19 '18 at 13:09

adhy wijaya

43817

• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it, thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you're welcome Creakush
  – adhy wijaya
  Nov 19 '18 at 13:14
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  you can just use Month(rng2(i).Value)>=11 or Month(rng2(i).Value)<=2 as well i believe.
  – Nathan_Sav
  Nov 19 '18 at 13:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Yes, that's also helpful, but with an AND.
  – Creakush Coliko
  Nov 19 '18 at 13:33
  
  
  

  
  
  
  

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
});


}
});

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• 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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375225%2fexcel-vba-finding-max-for-winter-months%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

1

I think Month should be first:

If Month(rng2(i).Value)= 11 Or Month(rng2(i).Value)= 12 Or Month(rng2(i).Value)= 1 Or Month(rng2(i).Value)= 2 Then

Hope this help.

share|improve this answer

answered Nov 19 '18 at 13:09

adhy wijaya

43817

• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it, thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you're welcome Creakush
  – adhy wijaya
  Nov 19 '18 at 13:14
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  you can just use Month(rng2(i).Value)>=11 or Month(rng2(i).Value)<=2 as well i believe.
  – Nathan_Sav
  Nov 19 '18 at 13:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Yes, that's also helpful, but with an AND.
  – Creakush Coliko
  Nov 19 '18 at 13:33
  
  
  

  
  
  
  

add a comment | 

1

I think Month should be first:

If Month(rng2(i).Value)= 11 Or Month(rng2(i).Value)= 12 Or Month(rng2(i).Value)= 1 Or Month(rng2(i).Value)= 2 Then

Hope this help.

share|improve this answer

answered Nov 19 '18 at 13:09

adhy wijaya

43817

• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it, thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you're welcome Creakush
  – adhy wijaya
  Nov 19 '18 at 13:14
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  you can just use Month(rng2(i).Value)>=11 or Month(rng2(i).Value)<=2 as well i believe.
  – Nathan_Sav
  Nov 19 '18 at 13:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Yes, that's also helpful, but with an AND.
  – Creakush Coliko
  Nov 19 '18 at 13:33
  
  
  

  
  
  
  

add a comment | 

1

1

1

I think Month should be first:

If Month(rng2(i).Value)= 11 Or Month(rng2(i).Value)= 12 Or Month(rng2(i).Value)= 1 Or Month(rng2(i).Value)= 2 Then

Hope this help.

share|improve this answer

answered Nov 19 '18 at 13:09

adhy wijaya

43817

I think Month should be first:

If Month(rng2(i).Value)= 11 Or Month(rng2(i).Value)= 12 Or Month(rng2(i).Value)= 1 Or Month(rng2(i).Value)= 2 Then

Hope this help.

share|improve this answer

answered Nov 19 '18 at 13:09

adhy wijaya

43817

share|improve this answer

share|improve this answer

answered Nov 19 '18 at 13:09

adhy wijaya

43817

answered Nov 19 '18 at 13:09

adhy wijaya

43817

answered Nov 19 '18 at 13:09

adhy wijaya

43817

43817

• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it, thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you're welcome Creakush
  – adhy wijaya
  Nov 19 '18 at 13:14
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  you can just use Month(rng2(i).Value)>=11 or Month(rng2(i).Value)<=2 as well i believe.
  – Nathan_Sav
  Nov 19 '18 at 13:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Yes, that's also helpful, but with an AND.
  – Creakush Coliko
  Nov 19 '18 at 13:33
  
  
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  That's it, thanks!
  – Creakush Coliko
  Nov 19 '18 at 13:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you're welcome Creakush
  – adhy wijaya
  Nov 19 '18 at 13:14
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  you can just use Month(rng2(i).Value)>=11 or Month(rng2(i).Value)<=2 as well i believe.
  – Nathan_Sav
  Nov 19 '18 at 13:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Yes, that's also helpful, but with an AND.
  – Creakush Coliko
  Nov 19 '18 at 13:33
  
  
  

  
  
  
  

That's it, thanks!
– Creakush Coliko
Nov 19 '18 at 13:12

That's it, thanks!
– Creakush Coliko
Nov 19 '18 at 13:12

you're welcome Creakush
– adhy wijaya
Nov 19 '18 at 13:14

you're welcome Creakush
– adhy wijaya
Nov 19 '18 at 13:14

1

1

you can just use Month(rng2(i).Value)>=11 or Month(rng2(i).Value)<=2 as well i believe.
– Nathan_Sav
Nov 19 '18 at 13:25

you can just use Month(rng2(i).Value)>=11 or Month(rng2(i).Value)<=2 as well i believe.
– Nathan_Sav
Nov 19 '18 at 13:25

Yes, that's also helpful, but with an AND.
– Creakush Coliko
Nov 19 '18 at 13:33

Yes, that's also helpful, but with an AND.
– Creakush Coliko
Nov 19 '18 at 13:33

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• 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

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• 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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375225%2fexcel-vba-finding-max-for-winter-months%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

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

Name

Email

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

Name

Email

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

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here