adding a formula to VBA code

James__S · Jul 11, 2014

I have the following code which works plus a formula in cell range G5:G123....how do i add the the formula to the VBA code please

Formula =IF(F5<>"".F5< today () +3 )<today()+3)

Code:

Private Sub Workbook_Open()
For Each cell In Range("F5:F19")
If cell.Value < Date + 3 And cell.Value <> "" Then
cell.Interior.Color = 3
cell.Font.ColorIndex = 2
cell.Font.Bold = True
End If
Next
End Sub

<today(),"send reminder","")
<today()+3)
<today(),"send reminder","")
<today()+3)<today()+3)
<today()+3)

Also instead of having the code update when book opens could it have a Commandbutton so it can be updated anytime

Thanks</today()+3)
</today()+3)<today()+3)
</today(),"send></today()+3)
</today(),"send></today()+3)

Andrew Poulsom · Jul 11, 2014

In what way do you want to "add the formula to the VBA code"? If you want to put it in a cell use the macro recorder while entering it manually to get the VBA syntax.

vds1 · Jul 11, 2014

This would be the code that would go to your Module ( Goto VB Screen > Insert > Module)

Also remember to delete the existing code from Thisworkbook.

Code:

Private Sub Data_Format_Formulas()
For Each cell In Range("F5:F19")
    If cell.Value < Date + 3 And cell.Value <> "" Then
        cell.Interior.Color = 3
        cell.Font.ColorIndex = 2
        cell.Font.Bold = True
    End If
Next
[COLOR=#0000cd]Range("G5:G123").Formula = "=IF(F5<>"""",F5< today () +3 ) "[/COLOR]
End Sub

Your Formula =IF(F5<>"".F5< today () +3 )<today()+3)
, i dont think there should be a dot, it should be comma... Please double check.. My code includes the formula from G5 to G123

Next, you can insert a shape, right click , assign macros.. </today()+3)

James__S · Jul 11, 2014

Thanks D

the formula i posted for some reason would not show up so i threw in spaces etc..etc..just to get it into the message

Thanks for the VBA code

James__S · Jul 11, 2014

I get an error in the code,,,,the following is highlighted Yellow

Range("G5:G123").Formula = "=IF(F5<>"""",F5< today () +3 ) "

vds1 · Jul 11, 2014

Can you post exact formula ?

James__S · Jul 11, 2014

Still receiving error

"application defined or object defined error"

vds1 · Jul 11, 2014

The formula which you posted is not correct.. what are you trying to achieve in G5 ?

vds1 · Jul 11, 2014

Just Guessing..

Range("G5:G123").Formula = "=IF(F5<>"""",F5,today()+3)"

Andrew Poulsom · Jul 11, 2014

Post your formula surrounding all less than and greater than signs with a space.

adding a formula to VBA code

James__S

Active Member

Excel Facts

Andrew Poulsom

MrExcel MVP

vds1

Well-known Member

James__S

Active Member

James__S

Active Member

vds1

Well-known Member

James__S

Active Member

vds1

Well-known Member

vds1

Well-known Member

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

adding a formula to VBA code

Active Member

Excel Facts

MrExcel MVP

Well-known Member

Active Member

Active Member

Well-known Member

Active Member

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock