Convert formula to vba

[George_ATH]

Hello,
i want to transorm this type to a vba code so to run it when i want

the type is

IF(DAY(E3)=DAY(EOMONTH(E3;0));IF(F3>TODAY();EOMONTH(E3;1);TODAY());IF(F3>TODAY();DATE(YEAR(F3);MONTH(F3);DAY(F3)+1);TODAY()))

i try this but is not working

If Day(Cells(Selection.Row, 5)) = Day(EoMonth(Cells(Selection.Row, 5), 0)) Then
If today() < Cells(Selection.Row, 6) Then
Range(Cells(Selection.Row, 5)).Value = EoMonth(Cells(Selection.Row, 5), 1)
Else: Range(Cells(Selection.Row, 5)).Value = today()
End If

If today() < Cells(Selection.Row, 6) Then
Range(Cells(Selection.Row, 5)).Value = DateAdd("d", 1, Cells(Selection.Row, 6))
Else: Range(Cells(Selection.Row, 5)).Value = today()
End If

End If
End Sub

 

[Scott T]

To use excel functions like EOMONTH in VBA you need to specify that it is an excel function since they are not VBA funcitons

application.WorksheetFunction.EoMonth(cells(....

or

application.EoMonth(Cells(.....

 

[George_ATH]

i tried this but not working

thank you anyway

 

[Tetra201]

See if this works for you:

Sub Test()
    Dim r As Long
    Dim d As Date
    r = Selection.Row
    d = Date
    If Cells(r, "F") > Date Then
        d = Cells(r, "F") + 1
        If Day(Cells(r, "E") + 1) = 1 Then
            d = DateAdd("m", 1, Cells(r, "E") + 1) - 1
        End If
    End If
    Cells(r, "E") = d
End Sub

 

[George_ATH]

it works but not corrent.

To help you

[TABLE="width: 600"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]code

[/TD]
[TD="align: center"]name
[/TD]
[TD="align: center"]mobile
[/TD]
[TD="align: center"]CLUB
[/TD]
[TD="align: center"]from
[/TD]
[TD="align: center"]til
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]CA001
[/TD]
[TD="align: center"]user 1
[/TD]
[TD="align: center"]6951668880

[/TD]
[TD="align: center"]YES

[/TD]
[TD="align: center"]27/10/2017

[/TD]
[TD="align: center"]30/11/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]

[/TD]
[TD="align: center"]user 2

[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]YES

[/TD]
[TD="align: center"]30/4/2018
[/TD]
[TD="align: center"]31/5/2018
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]

[/TD]
[TD="align: center"]user 3
[/TD]
[TD="align: center"]

[/TD]
[TD="align: center"]NO
[/TD]
[TD="align: center"]30/9/2017
[/TD]
[TD="align: center"]31/10/2017

[/TD]
[/TR]
</tbody>[/TABLE]


now the correct formula is
IF(F3>TODAY();IF(DAY(E3)=DAY(EOMONTH(E3;0));DATE(YEAR(F3);MONTH(F3)+1;DAY(EOMONTH(F3;1)+1));DATE(YEAR(F3);MONTH(F3);DAY(F3+1)));TODAY())

i want the line to be selected by the selected cell

thank you

 

[Tetra201]

Your formulas in Post # 1 and in Post # 5 are different and overcomplicated.

For example, the formula from Post # 1 can be simplified to:

=IF(G3>TODAY();IF(DAY(F3+1)=1;EOMONTH(F3;1),G3+1);TODAY())

Please try to describe with words the results that you want to achieve.

 

[George_ATH]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]code
[/TD]
[TD="align: center"]name[/TD]
[TD="align: center"]mobile[/TD]
[TD="align: center"]CLUB[/TD]
[TD="align: center"]from[/TD]
[TD="align: center"]til[/TD]
[TD="align: center"]result
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]CA001[/TD]
[TD="align: center"]user 1[/TD]
[TD="align: center"]6951668880
[/TD]
[TD="align: center"]YES
[/TD]
[TD="align: center"]31/10/2017
[/TD]
[TD="align: center"]30/11/2017
[/TD]
[TD="align: center"]1/12/2017
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]user 2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]YES
[/TD]
[TD="align: center"]15/4/2018
[/TD]
[TD="align: center"]14/5/2018
[/TD]
[TD="align: center"]15/6/2018
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]user 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NO[/TD]
[TD="align: center"]30/9/2017
[/TD]
[TD="align: center"]31/10/2017
[/TD]
[TD="align: center"]today
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]user 4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]YES
[/TD]
[TD="align: center"]1/2/2018
[/TD]
[TD="align: center"]28/2/2018
[/TD]
[TD="align: center"]1/3/2018
[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

every time i run the makro i want to add 1 day from column "F" and to be written in "E" column
if date in column "F" is < than today, then present date in column "E"
Selected cell gives us the line

thank you for yor patience