You can either set the variable within your procedure (after the Sub xxxx() line), or create it as a public function - in that way it can also be used from within the worksheet.

This air code will allow you to set the days before today whenever the function is used:

Public Function Data_to_find(i as Integer)
Data_to_find = Date - i
End Function

This way you would call it by saying Data_to_find(3) for three days back etc.

If you always want it to be yesterday then use,

Public Function Data_to_find()
Data_to_find = Date - 1
End Function

simply call Data_to_find()

Baz

"aneurin" wrote:

Hi i am having a problem with a variable
the variable is called data_to_find and is used several time in my excel workbook
i have declared it as public


Option Explicit
Public data_to_find As Variant
data_to_find = Date - 1

but when i run my code i get the error invalid outside proceedure

hope someone can help