Can't wrap my head around this problem. Here's what I'm going for Variables

Y (set to cell A1)

X (set to cell B1

IncY (set to 3)

IncX (set to 1)

Days(result)What I want to know is is Y increases by 3 daily and X increases by 1 daily then how many days will it take for Y to be equal or greater than X.

IncY should be set to another cell at well so I can play around with how much Y increases but default it 3 for the moment.

✔ Best Answer

Here is some VBA code that should get the job done. I've offered 3 different ways to assign an Incremental value to Y: a hardcoded value, a reference to a cell and an InputBox to get the value from the user.

I've offered 2 different output methods: Place the count in a cell or present a Message Box to the user.

Remove/replace the comment symbols ' where appropriate to test the various options.

After the code, I've offered a brute force way to get the answer in an Excel spreadsheet.

BTW, if you are going to playing around with VBA, you should probably review this tutorial:

http://www.computing.net/howtos/sho...

VBA MethodSub CountDays() Dim X, Y, IncX, IncY 'Initialize variables Y = Range("A1") X = Range("B1") IncX = 1 'Hardcode incremental value for Y IncY = 3 'Use cell for incremental value for Y 'IncY = Range("C1") ''Get incremental value for Y from user, Exit if Cancelled 'IncY = Application.InputBox("Enter Increment Value For Y", _ "Incremental Value", Type:=1) 'If IncY = False Then Exit Sub 'Loop while incrementing Y and X, count loops Do While Y < X X = X + IncX Y = Y + IncY cntDays = cntDays + 1 Loop 'Set cntDays = 0 if Y is initially >= X 'This avoids a blank output If cntDays = "" Then cntDays = 0 'Put Day Count in D1 Range("D1") = cntDays 'Present Day Count to user 'MsgBox "It took " & cntDays & _ " Days For Y to be equal to or greater than X", _ Title:="Day Count Result" End Sub

Spreadsheet MethodEnter your Y, X and IncY values in A1, B1 and C1 respectively. In each of the following formulas, make sure you use the $ exactly as shown.

In A2, enter this formula:

=A1+$C$1

In B2, enter this formula:

=B1+1

In C2, enter this formula:

=IF(A2>=B2,COUNTA($A$2:A2),"")

Drag all 3 formulas down until a value appears in Column C.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

First, I assume that you are looking to use a macro, correct? Doesn't there need to be restrictions related to what A1and B1 can contain? If A1 is greater than B1, then you're done before you even start.

BTW this sounds very much like a homework problem. Is it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Haha no its not a homework problem. I haven't been in school for quite sometime. This is actually for a very nerdy thing. It going to be a general formula for when I'm grinding certain things in video games. I like to make spreadsheets to track my progress through out different games or event. This one is particular it to track how many events I can complete to get my ratio of 1 event per day. I have this solved already in a long way where I have an array where the day increased by 1 and the events complete increases by whatever I set it at (3 is the goal) and the array increases until events complete >= days. Then I just use a countif formula to count how many cells have data in a particular column.

I'm looking for a way to streamline it so I can see how many days it will take to me to reach my goal if I complete 3 events a day, It should be flexible that I can change it to 4 events a day and see the new result.

Logically I can work this out but I'm not sure what base functions to use in excel to achieve the goal or the syntax to code it in VBA.

Here is some VBA code that should get the job done. I've offered 3 different ways to assign an Incremental value to Y: a hardcoded value, a reference to a cell and an InputBox to get the value from the user.

I've offered 2 different output methods: Place the count in a cell or present a Message Box to the user.

Remove/replace the comment symbols ' where appropriate to test the various options.

After the code, I've offered a brute force way to get the answer in an Excel spreadsheet.

BTW, if you are going to playing around with VBA, you should probably review this tutorial:

http://www.computing.net/howtos/sho...

VBA MethodSub CountDays() Dim X, Y, IncX, IncY 'Initialize variables Y = Range("A1") X = Range("B1") IncX = 1 'Hardcode incremental value for Y IncY = 3 'Use cell for incremental value for Y 'IncY = Range("C1") ''Get incremental value for Y from user, Exit if Cancelled 'IncY = Application.InputBox("Enter Increment Value For Y", _ "Incremental Value", Type:=1) 'If IncY = False Then Exit Sub 'Loop while incrementing Y and X, count loops Do While Y < X X = X + IncX Y = Y + IncY cntDays = cntDays + 1 Loop 'Set cntDays = 0 if Y is initially >= X 'This avoids a blank output If cntDays = "" Then cntDays = 0 'Put Day Count in D1 Range("D1") = cntDays 'Present Day Count to user 'MsgBox "It took " & cntDays & _ " Days For Y to be equal to or greater than X", _ Title:="Day Count Result" End Sub

Spreadsheet MethodEnter your Y, X and IncY values in A1, B1 and C1 respectively. In each of the following formulas, make sure you use the $ exactly as shown.

In A2, enter this formula:

=A1+$C$1

In B2, enter this formula:

=B1+1

In C2, enter this formula:

=IF(A2>=B2,COUNTA($A$2:A2),"")

Drag all 3 formulas down until a value appears in Column C.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History