excel vba error 91 object variable or with block variable not set

[Ramo964516]

Hi everybody,


I have some VBA code wich generates a bug. The message is : "excel vba error 91 object variable or with block variable not set"


The bug code is :

strAdrDateEnvoiRelance = Range("C1:C100").Find(DateSerial(Mid(strAyJourOuvréMois(i), 7, 4), Mid(strAyJourOuvréMois(i), 4, 2), Mid(strAyJourOuvréMois(i), 1, 2)), Range("C1")).Address(False, False)
The value of the variable
strAyJourOuvréMois(i)=01/04/2016


The vba code run when the sheet (Solde mensuel 2016) from the workbook (Palettes europe BMF 2016) is activated. and then it looks for values less than -100 , copy them paste them in another workbook, check for the net work days of the month and then creating a table in the top of the same sheet.

all the code runs well, but the last section when it hae to lookfor values in the table create a bug. But i really don't know how to fix this bug. I checked almost every posts concerning this type of error, i tried the same synthax of the error line in another sheet and it works.

Can somebody see why it doesn't work?
Thanks for your help


157891579115790

[snb]

It is rather complicated to

- activate a sheet of the month april that is not available in the workbook "SUIVI..." stc.
- it is even more complicated so search for data if only sheet 'feuil1' contains any data.

This code produces an error, not a bug.

Is my assumption correct that you did not create these macros yourself ?

The worksheet_activate event:

To determine the first workday of the month you are in this code suffices:


y = DateSerial(Year(Date), Month(Date), 1)
If Weekday(y, 2) > 5 Then y = y + 8 - Weekday(y, 2)

[Ramo964516]

Hi snb, and thanks for your rapid answer.

Your assumption is'nt correct, i create the code myself.
The sheet of the month is created by the sub Relance_Alertes row 26 in the vba code (module Relance1).

I'm not looking to determine the first workday of the mont. i have already this data with my macro and it works very well. it's created in the second workbook with all the other workdays of the month. But when i try to look into this range i cannot find the day, even if it exists in the range.

Do you have any other idea about the error ?

[Ramo964516]

i tried this one (with the workbooks name and the sheet name )

a = Workbooks("SUIVI Tableau relance mensuel transporteurs.xlsx").Worksheets(MonthName(strMoisNow)).Range("C:C").Find(CDate(Mid(strAyJourOuvréMois(i), 7, 4) & "/" & Mid(strAyJourOuvréMois(i), 4, 2) & "/" & Mid(strAyJourOuvréMois(i), 1, 2)), LookAt:=xlWhole)

and it works.

I will do some update on my post later if it's as good as i expect or if i have some other issues.