Can't Return Correct Count for Variable in VBA

Question

I have a macro that runs scenarios based on different time frames entered by users. It works properly if a user enters 2 or more scenarios as dates - it fails if someone enters only one date. This block works properly for 2 or more:

countofscenarios = Range(Range("B5"), Range("B5").End(xlDown)).Count

I just can't figure out how to make it work with only 1 date entered - I've tried different variations along the lines of:

If Range("B5") <> "" And Range("B6").End(xlDown).Count = 0 Then
    countofscenarios = 1
Else
    countofscenarios = Range(Range("B5"), Range("B5").End(xlDown)).Count
End If

But I receive an Overflow error. Apologies for lack of working data but I can strip something down if that will help. Thanks in advance.

These timeframes are entered B5 onwards? Is there a header in B4? — Luuklag
– Luuklag, Commented May 7, 2018 at 14:02
To see what Range("B6").End(xlDown) does, you can go to excel, select a cell, and press: ctrl + shift + down. If you do this in B5 and there is data in B6 it will select B5 + B6, if you do this in B5 and there is no data in B6 it will select till the end of the worksheet. — Luuklag
– Luuklag, Commented May 7, 2018 at 14:04
Correct - B1:B3 are empty, B4 is a header, and B5:B16 are where they can enter the dates. @Luuklag I did run ctrl+shift+down to confirm nothing else is in column B through the end. — Hatt
– Hatt, Commented May 7, 2018 at 14:09

Luuklag · Accepted Answer · 2018-05-07 14:09:48Z

1

Your answer is almost there:

If Range("B5") = "" Then
   MsgBox("Enter a date")
   Exit sub
ElseIF Range("B6") = "" AND Range("B5") <> "" Then      
    countofscenarios = 1
Else
    countofscenarios = Range(Range("B5"), Range("B5").End(xlDown)).Count
End if

I entered some "error handling" to avoid running the code without any dates entered (first part of if statement). Then check for the event that there is a value in B5, but not B6, in that case set the count to 1 (second part of the if statement). Lastly your original counter in the Else clause.

Alternatively you can make use of the header row. This will give you 0 when there is no value in B5. And ofcourse the count in the other cells. This only works however when there is a value in B4.

countofscenarios = Range(Range("B4"), Range("B4").End(xlDown)).Count -1

answered May 7, 2018 at 14:09

Luuklag

3,91412 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hatt Over a year ago

Cool, thank you very much, that does remove the error and apply the macro if only one scenario is entered.

Collectives™ on Stack Overflow

Can't Return Correct Count for Variable in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related