View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
JE McGimpsey JE McGimpsey is offline
external usenet poster
 
Posts: 4,624
Default Newbie with VBA for Excel

The error means that the .Find() did not return a range object, so the
object for the .Address property is not defined.

This is either because numOpt doesn't exist in Sheets(1).Range("A4:F20")
or because ClasseurRepresentants isn't a valid workbook.

A different way:

Dim rFound As Range
Set rFound = ClasseurRepresentants.Sheets(1).Range("A4:F20").Fi nd( _
What:=NumDpt, _
LookIn:=xlFormulas, _
LookAt:=xlWhole)
If rFound Is Nothing Then
MsgBox NumOpt & " not found in A4:F20"
Else
Colonne = rFound.Address
'put the rest of your code here
End If

In article . com,
"echo---pscbro" wrote:

Hi,

Can anyone help me out with the integrity of this line (on execution,
Excel says "Object or bloc variable not defined"). However I have set
Option Explicit in my own learning session of VBA and it all my
variables have been declared. See line below:

Colonne =
ClasseurRepresentants.Sheets(1).Range("A4:F20").Fi nd(What:=NumDpt, _
LookIn:=xlFormulas, LookAt:=xlWhole).Address

Thanks in advance,

Pascal