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

	A	B	C	D	E	F
1	code	name	mobile	CLUB	from	til
2	CA001	user 1	6951668880	YES	27/10/2017	30/11/2017
3		user 2		YES	30/4/2018	31/5/2018
4		user 3		NO	30/9/2017	31/10/2017

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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]

	A	B	C	D	E	F
1	code	name	mobile	CLUB	from	til	result
2	CA001	user 1	6951668880	YES	31/10/2017	30/11/2017	1/12/2017
3		user 2		YES	15/4/2018	14/5/2018	15/6/2018
4		user 3		NO	30/9/2017	31/10/2017	today
5		user 4		YES	1/2/2018	28/2/2018	1/3/2018

<tbody>
</tbody>

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