IF else then

A

Aussie_Bob_C

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm trying to check date in cell G3 with a =today() date in cell B3.

If date in G3 is greater than cell B3 do nothing.

Else if date in cell G3 is less than or equal to cell B3 run Sub PlusOneMonth() macro which increases cell G3 date by 1 calendar month.

Sub PlusOneMonth()

Dim dt As Date
Dim mnth As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
dt = Range("G3").Value
mnth = Month(dt)
If Day(dt) >= 17 Then mnth = mnth + 1
[G3].Value = DateSerial(Year(dt), mnth, 17)
Application.Calculation = xlCalculationAutomatic

End Sub

TIA

Aussie Bob C
 
B

Bob Greenblatt

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm trying to check date in cell G3 with a =today() date in cell B3.

If date in G3 is greater than cell B3 do nothing.

Else if date in cell G3 is less than or equal to cell B3 run Sub
PlusOneMonth() macro which increases cell G3 date by 1 calendar month.

Sub PlusOneMonth()

Dim dt As Date
Dim mnth As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
dt = Range("G3").Value
mnth = Month(dt)
If Day(dt) >= 17 Then mnth = mnth + 1
[G3].Value = DateSerial(Year(dt), mnth, 17)
Application.Calculation = xlCalculationAutomatic

End Sub

TIA

Aussie Bob C
Click to expand...
What is your question? If you are looking for a formula in a cell, try:
=if(g3>b3,"",plusonemonth)
I assume you realize, that you can not put this formula in B3 or G3.
 
J

JE McGimpsey

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm trying to check date in cell G3 with a =today() date in cell B3.

If date in G3 is greater than cell B3 do nothing.

Else if date in cell G3 is less than or equal to cell B3 run Sub
PlusOneMonth() macro which increases cell G3 date by 1 calendar month.

Sub PlusOneMonth()

Dim dt As Date
Dim mnth As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
dt = Range("G3").Value
mnth = Month(dt)
If Day(dt) >= 17 Then mnth = mnth + 1
[G3].Value = DateSerial(Year(dt), mnth, 17)
Application.Calculation = xlCalculationAutomatic

End Sub
Click to expand...

Not sure what you're asking. In order to change G3 automatically, you'd
need to use an event macro. For instance, put this in your worksheet
code module:

Private Sub Worksheet_Calculate()
Dim dt As Date
On Error GoTo End_Calc
Application.EnableEvents = False
dt = Date
With Me.Range("G3")
If .Value < dt Then _
.Value = DateSerial(Year(dt), Month(dt - 17) + 1, 17)
End With
End_Calc:
Application.EnableEvents = True
End Sub
 
A

Aussie_Bob_C

Bob

I'm trying to call up a macro procedure called
"PlusOneMonth". I know I can't do that via formulas.
I'm after a macro to check two dates in two cells
and either do nothing, if cell G3 date is greater than cell B3 date, Or if cell G3 date is less than cell B3 date calls up & runs the macro procedure called "PlusOneMonth" which increases cell G3's date by 1 calendar month.

The macro shown below in original post, I know works.

TIA

Aussie Bob C
Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm trying to check date in cell G3 with a =today() date in cell B3.

If date in G3 is greater than cell B3 do nothing.

Else if date in cell G3 is less than or equal to cell B3 run Sub
PlusOneMonth() macro which increases cell G3 date by 1 calendar month.

Sub PlusOneMonth()

Dim dt As Date
Dim mnth As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
dt = Range("G3").Value
mnth = Month(dt)
If Day(dt) >= 17 Then mnth = mnth + 1
[G3].Value = DateSerial(Year(dt), mnth, 17)
Application.Calculation = xlCalculationAutomatic

End Sub

TIA

Aussie Bob C
Click to expand...
What is your question? If you are looking for a formula in a cell, try:
=if(g3>b3,"",plusonemonth)
I assume you realize, that you can not put this formula in B3 or G3.
Click to expand...
 
A

Aussie_Bob_C

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm trying to check date in cell G3 with a =today() date in cell B3.

If date in G3 is greater than cell B3 do nothing.

Else if date in cell G3 is less than or equal to cell B3 run Sub
PlusOneMonth() macro which increases cell G3 date by 1 calendar month.

Sub PlusOneMonth()

Dim dt As Date
Dim mnth As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
dt = Range("G3").Value
mnth = Month(dt)
If Day(dt) >= 17 Then mnth = mnth + 1
[G3].Value = DateSerial(Year(dt), mnth, 17)
Application.Calculation = xlCalculationAutomatic

End Sub
Click to expand...

Not sure what you're asking. In order to change G3 automatically, you'd
need to use an event macro. For instance, put this in your worksheet
code module:

Private Sub Worksheet_Calculate()
Dim dt As Date
On Error GoTo End_Calc
Application.EnableEvents = False
dt = Date
With Me.Range("G3")
If .Value .Value = DateSerial(Year(dt), Month(dt - 17) + 1, 17)
End With
End_Calc:
Application.EnableEvents = True
End Sub
[/QUOTE]

J.E.

I have the macro to increase the date in cell G3.

I'm after macro which will check which of the two dates is greater and if G3 is the lesser of the two do nothing &n Exit Sub, but if G3 is the larger date then call up the sub procedure.

i.e. something like:

If G3 <= B3 Then Exit Sub
Elseif G3 > B3 run macro
application.run "PlusOneMonth"

The macro would be called up from a macro which is run daily. The formula in B3 is =TODAY() which increases B3 date each day. When B3's date is the greater of the two, the macro PlusOneMonth is called up to increase G3's date by 1 Month.

If a question is required it would be "Can a If Then Elseif part of a macro call up a second macro"? I know Application.Run "PlusOneMonth" will.

TIA
Aussie Bob C
 
B

Bob Greenblatt

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm trying to check date in cell G3 with a =today() date in cell B3.

If date in G3 is greater than cell B3 do nothing.

Else if date in cell G3 is less than or equal to cell B3 run Sub
PlusOneMonth() macro which increases cell G3 date by 1 calendar month.

Sub PlusOneMonth()

Dim dt As Date
Dim mnth As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
dt = Range("G3").Value
mnth = Month(dt)
If Day(dt) >= 17 Then mnth = mnth + 1
[G3].Value = DateSerial(Year(dt), mnth, 17)
Application.Calculation = xlCalculationAutomatic

End Sub
Click to expand...

Not sure what you're asking. In order to change G3 automatically, you'd
need to use an event macro. For instance, put this in your worksheet
code module:

Private Sub Worksheet_Calculate()
Dim dt As Date
On Error GoTo End_Calc
Application.EnableEvents = False
dt = Date
With Me.Range("G3")
If .Value .Value = DateSerial(Year(dt), Month(dt - 17) + 1, 17)
End With
End_Calc:
Application.EnableEvents = True
End Sub
Click to expand...

J.E.

I have the macro to increase the date in cell G3.

I'm after macro which will check which of the two dates is greater and if G3
is the lesser of the two do nothing &n Exit Sub, but if G3 is the larger date
then call up the sub procedure.

i.e. something like:

If G3 <= B3 Then Exit Sub
Elseif G3 > B3 run macro
application.run "PlusOneMonth"

The macro would be called up from a macro which is run daily. The formula in
B3 is =TODAY() which increases B3 date each day. When B3's date is the greater
of the two, the macro PlusOneMonth is called up to increase G3's date by 1
Month.

If a question is required it would be "Can a If Then Elseif part of a macro
call up a second macro"? I know Application.Run "PlusOneMonth" will.

TIA
Aussie Bob C
Click to expand...
Yes, it can. Both my and J.E.'s responses show a way to do it. Have you
tried? What is not working?
 
A

Aussie_Bob_C

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm trying to check date in cell G3 with a =today() date in cell B3.

If date in G3 is greater than cell B3 do nothing.

Else if date in cell G3 is less than or equal to cell B3 run Sub
PlusOneMonth() macro which increases cell G3 date by 1 calendar month.

Sub PlusOneMonth()

Dim dt As Date
Dim mnth As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
dt = Range("G3").Value
mnth = Month(dt)
If Day(dt) >= 17 Then mnth = mnth + 1
[G3].Value = DateSerial(Year(dt), mnth, 17)
Application.Calculation = xlCalculationAutomatic

End Sub

Not sure what you're asking. In order to change G3 automatically, you'd
need to use an event macro. For instance, put this in your worksheet
code module:

Private Sub Worksheet_Calculate()
Dim dt As Date
On Error GoTo End_Calc
Application.EnableEvents = False
dt = Date
With Me.Range("G3")
If .Value .Value = DateSerial(Year(dt), Month(dt - 17) + 1, 17)
End With
End_Calc:
Application.EnableEvents = True
End Sub
Click to expand...

J.E.

I have the macro to increase the date in cell G3.

I'm after macro which will check which of the two dates is greater and if G3
is the lesser of the two do nothing &n Exit Sub, but if G3 is the larger date
then call up the sub procedure.

i.e. something like:

If G3 > Elseif G3 > B3 run macro
application.run "PlusOneMonth"

The macro would be called up from a macro which is run daily. The formula in
B3 is =TODAY() which increases B3 date each day. When B3's date is the greater
of the two, the macro PlusOneMonth is called up to increase G3's date by 1
Month.

If a question is required it would be "Can a If Then Elseif part of a macro
call up a second macro"? I know Application.Run "PlusOneMonth" will.

TIA
Aussie Bob C
Click to expand...
Yes, it can. Both my and J.E.'s responses show a way to do it. Have you
tried? What is not working?
[/QUOTE]
Hi Bob & J.E.

I'm sorry, the penny just dropped.
I already use similar code attached to a form button on another spreadsheet.

I placed J.E.'s code in Sheet 1 (Code) & it works. Thank you.

What actually triggers the code to run?
I assume some calculation?

TIA
Aussie Bob C
 
J

JE McGimpsey

I'm sorry, the penny just dropped.
I already use similar code attached to a form button on another spreadsheet.

I placed J.E.'s code in Sheet 1 (Code) & it works. Thank you.

What actually triggers the code to run?
I assume some calculation?
Click to expand...

Event code is triggered by an "event" - in this case, whenever the sheet
is recalculated (i.e., either explicitly via CMD= or after an cell
entry.

Other events include printing, changing the selection, saving, etc.
 
A

Aussie_Bob_C

I'm sorry, the penny just dropped.
I already use similar code attached to a form button on another spreadsheet.

I placed J.E.'s code in Sheet 1 (Code) & it works. Thank you.

What actually triggers the code to run?
I assume some calculation?
Click to expand...

Event code is triggered by an "event" - in this case, whenever the sheet
is recalculated (i.e., either explicitly via CMD= or after an cell
entry.

Other events include printing, changing the selection, saving, etc.
[/QUOTE]

Hi J.E.

I run another macro which imports a CSV file. This macro formats & re-positions columns, locates specific words & deletes whole rows etc.. The CSV file is positioned on screen with sheet1 to allow selection of rows of data for final inserted into Sheet1.

While it runs the new macro is run many many times slowing the original macro dramatically,
The WorkBook has 13 sheets each with calculations referencing the Sheet1 data & vise-versa.

Question:
Which option event would only run the new macro once at the opening of the WorkBook or selection of the Sheet1?

TIA
Aussie Bob C
 
J

JE McGimpsey

Question:
Which option event would only run the new macro once at the opening of the
WorkBook or selection of the Sheet1?
Click to expand...

The Workbook_Open event in the ThisWorkbook module. Make sure you
specify the worksheet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If Else Statement in VBA 5
Can't Display Userform 2
Help with variables!!! 1
Commandbutton question 2
Running parameter queries from VBA, suppressing parameter prompts 5
Run macro on all files in a specific folder 3
If value is then copy 2
MACRO Effeciency 3

Top