"Runtime Error 91" Solved

Question

Hello everyone,

And thank you in advance to those who will look into my problem, I'm on the verge of tears in front of my screen or ready to throw it against the wall... ;-)

I'm still working on the same tool, progressing slowly but surely.

Today's problem: the aim of this code is to find the "Valcherchée" (concatenation of 3 cells located in the "MàJ PA - retour audités" sheet) in column Z of the "BDD résumé" sheet. I've checked via a MsgBox and "Valcherchée" is indeed what I'm trying to find.
From there and using the offset method that should point to column G, find the cell indicating the associated campaign number for the "Valcherchée" and replace it with the number entered in the "MàJ PA - retour audités" sheet (updating the campaign number for each mission every 6 months).

I tested my procedure yesterday and after a day of working on it, it finally worked when I left in the evening. But, as always, this morning it stopped working. I've tried to improve it based on online suggestions, but after 3 hours on it, nothing works and I've given up!

While stepping through my procedure, it crashes at "MsgBox Cellule" (I added this line to try to identify the problem) and error message 91 appears. However, I don't have a with without end with (I tried putting one just in case), my variables are declared (I tried making them Variant to see if that solved my issue...), etc.
If I remove the code line "MsgBox Cellule", the procedure continues and returns Nothing, my MsgBox "Non trouvé" appears and the process ends.
What I don't understand is that my "Valcherchée" is indeed present in my column Z.

 Private Sub MAJNumCampagne() 'SEARCH CELL WITH CAMPAIGN NUMBER CORRESPONDING TO THE SELECTED MISSION IN THE "BDD résumé" SHEET Dim Valcherchée As String Dim Cellule As Range 'cell containing the 3 concatenated search values Dim myRange As Range 'cell corresponding to the campaign number 'Concatenation Mission + Year + Entity to search With Sheets("MàJ PA - retour audités") Valcherchée = .Range("D6").Value & .Range("G8").Value & .Range("D10").Value End With 'Activating the cell containing the campaign number based on the mission and assigning the updated number Set Cellule = Sheets("BDD résumé").Columns(26).Find(Valcherchée, Lookat:=xlWhole) MsgBox Cellule If Cellule Is Nothing Then MsgBox "Non trouvé." Exit Sub End If If Not Cellule Is Nothing Then myRange = Cellule.Offset(, -19) myRange.Value = Sheets("MàJ PA - retour audités").Range("I17").Value End If End Sub

I'm cracking up!! No more patience.
Thank you in advance for your valuable help.

Have a nice day everyone.
Amande42

PS: how do you format code on this forum (boxed and colored) to facilitate reading?

EDIT: Added code tags (syntax highlighting).
Explanations available here: HERE

Thank you for keeping this in mind in your future messages.

jordane45 · Answer

Hello,

- To include code:
Explanations available here:
https://codes-sources.commentcamarche.net/faq/10686-le-nouveau-codes-sources-comment-ca-marche#balises-code

- Regarding the error on the line of code:

 MsgBox Cellule

.... since you declare your variable like this:

 Dim Cellule As Range

... Cellule ... is a "RANGE" (a range of cells or a single cell...)
So... this "object" contains a VALUE property (to get its content...) , an ADDRESS property (to know its coordinates.....) ... anyway... it's up to you to choose what you want to display.

You could therefore write:

 Set Cellule = Sheets("BDD résumé").Columns(26).Find(Valcherchée, Lookat:=xlWhole) MsgBox Cellule If Cellule Is Nothing Then MsgBox "Not found." Exit Sub else MsgBox Cellule.Address myRange = Cellule.Offset(, -19) myRange.Value = Sheets("MàJ PA - retour audités").Range("I17").Value End If

Then you indicate:

my "Valcherchée" is indeed present in my column Z.

... but... is the text present AS IS in your cell... or is it part of a TEXT contained in your cell???
As a reminder: xlWhole corresponds to => EXACT MATCH.

And finally ....
=> Do not use ACCENTED characters in the names of your variables!
(nor any special characters... (except underscores))

--
Best regards,
Jordane

beber67470 · Answer

Hello

If it can be useful to you >>https://forums.commentcamarche.net/forum/affich-4687136-erreur-d-execution-91-sous-excel-vba

--
Computing is just a tool, like a paintbrush or a pencil.
Avoid these so-called free repair software, they are of no major use. The best companions are Dos and Powershell..

eriiic · Answer

Hello,

To complete:

 If Not Cellule Is Nothing Then
 myRange = Cellule.Offset(, -19)
 myRange.Value = Sheets("MàJ PA - retour audités").Range("I17").Value
 End If

Why test again since you've already done it above and it can no longer be anything but Not Nothing?
And above all: Set myRange = Cellule.Offset(, -19)
(it's an object)

Or you could directly put:

Cellule.Offset(, -19).Value = Sheets("MàJ PA - retour audités").Range("I17").Value

Eric

By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it will work. (the Shadoks)
In addition to the thank you (yes, yes, it happens!!!), remember to mark as resolved. Thank you.

Amande42 · Answer

Thank you all three for your response.
I just realized that my reply from 12:05 PM was not published...

So, I modified my code taking into account your comments. Normally, my "Valcherchee" corresponds exactly to what is in column Z, but as a precaution, I replaced the LookAt parameter of the Find method with xlPart.
My code now looks like this:

Private Sub MAJNumCampagne() 'SEARCH CELL WITH CAMPAIGN NUMBER CORRESPONDING TO THE SELECTED MISSION IN THE "BDD résumé" SHEET Dim Valcherchee As String Dim Cellule As Range 'cell containing the 3 searched values concatenated 'Dim myRange As Range 'cell corresponding to the campaign number 'Concatenation Mission + Year + Entity to search With Sheets("MàJ PA - retour audités") Valcherchee = .Range("D6").Value & .Range("G8").Value & .Range("D10").Value End With 'Activating the cell containing the campaign number based on the mission and assigning the updated number Set Cellule = Sheets("BDD résumé").Columns(26).Find(Valcherchee, Lookat:=xlPart) If Cellule Is Nothing Then MsgBox "Not found." Exit Sub Else MsgBox Cellule.Address Cellule.Offset(, -19) = Sheets("MàJ PA - retour audités").Range("I17").Value End If End Sub

But unfortunately, it still doesn't work.
I had already tried "MsgBox Cellule.Address" before to see if it pointed to the right place. It didn't work then, and it still doesn't work now.
The procedure always stops at Exit Sub and does not process my following lines of code, whereas there is indeed a match between my two sheets.
The "runtime error 91" occurs at the line "Msgbox Cellule.Address".

jordane45 · Answer

Are you sure about your values?

Can you try this and let us know what it gives you?

 Private Sub MAJNumCampagne() 'SEARCH FOR CELL WITH CAMPAIGN NUMBER CORRESPONDING TO THE SELECTED MISSION IN THE "BDD résumé" SHEET Dim Valcherchee As String Dim Cellule As Range 'cell including the 3 searched values concatenated Dim myRange As Range 'cell corresponding to the campaign number Dim oF_Maj As Worksheet ' Sheet Set oF_Maj = ThisWorkbook.Sheets("MàJ PA - retour audités") Dim oF_Resum As Worksheet ' Sheet Set oF_Resum = ThisWorkbook.Sheets("BDD résumé") 'Concatenation Mission + Year + Entity to search Valcherchee = oF_Maj.Range("D6").Value & oF_Maj.Range("G8").Value & oF_Maj.Range("D10").Value 'Activating the cell containing the campaign number based on the mission and assigning the updated number Set Cellule = oF_Resum.Columns(26).Find(Valcherchee, Lookat:=xlPart) If Cellule Is Nothing Then MsgBox "'" & Valcherchee & "' -> Not found." Exit Sub Else MsgBox Cellule.Address Set myRange = Cellule.Offset(, -19) myRange.Value = oF.Range("I17").Value End If End Sub

By the way... make sure you are looking in the right column and... that Valcherchee is strictly written the same way as in the cells where you are searching!

For my part, the code given above works very well.

--
Best regards,
Jordane

eriiic · Answer

Hi,

it's time to provide a minimally reduced working file, otherwise we can discuss for a long time.
We don’t even know what you’re looking for. For dates, for example, it’s more sensitive.
eric

--
By continually trying, we eventually succeed.
So the more it fails, the more chances we have that it will work. (the Shadoks)
In addition to the thank you (yes, yes, it’s done!!!), remember to mark it as resolved. Thank you.

Amande42 · Answer

Jordane, I tried your code and it doesn't work...

At Eric's request and indeed, since it seems more complicated than expected, I created a simplified version of my file.
I hope that deleting sheets and modifying confidential data hasn't messed up the file and the code too much.

For your information, the code we're talking about is in the sheet "MàJ PA - retour audités." The long-term goal is to integrate it into my module 2 once it works.
As for the USF and the modules, they are linked to the sheets I deleted but that shouldn't impact the code concerning us.

http://www.cjoint.com/c/FADpzHnOChm

If you need explanations, don't hesitate to ask me.

Thank you.

PS: Eric, the value I am looking for with my offset is the number of the last recorded follow-up campaign (1, 2, 3, 4...) which therefore refers to a positive whole number.

Amande42 · Answer

Thank you for your replies.
And sorry for the oversight, but let him who is without sin cast the first stone.

I modified the line of code as you pointed out.
It still didn't work. After a flash of genius (or not), I copied the values from my column Z and pasted only the values, not the formulas. And now it works.
All I have left to do is modify my code so that column Z contains only text values automatically and not formulas.

Thank you all for the time you devoted to a clumsy novice.
Have a great day everyone.

"Runtime Error 91"

8 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.