Newbie with VBA for Excel

[echo---pscbro]

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").Find(What:=NumDpt, _
LookIn:=xlFormulas, LookAt:=xlWhole).Address

Thanks in advance,

Pascal

 

[Dave Peterson]

If NumDpt isn't in A4:F20, then the .find fails. And if the .find fails, then
there's no .address. And then KABLEWIE!

One way around it:

dim myCell as range
dim Colonne as string
set mycell = ClasseurRepresentants.Sheets(1).Range("A4:F20") _
.Find(What:=NumDpt, LookIn:=xlFormulas, LookAt:=xlWhole)

if mycell is nothing then
colonne = "not found!
else
colonne = mycell.address
end if

 

[Gary Brown]

try
SET Colonne =
ClasseurRepresentants.Sheets(1).Range("A4:F20").Find(What:=NumDpt, _
LookIn:=xlFormulas, LookAt:=xlWhole).Address
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[JE McGimpsey]

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").Find( _
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

 

[Jim Thomlinson]

What if nothing is found??? Then it will crash... Try this...

dim rng as range

set rng = Sheets(1).Range("A4:F20").Find(What:=NumDpt, _
LookIn:=xlFormulas, LookAt:=xlWhole)

if not rng is nothing then Colonne =rng.address

 

[JE McGimpsey]

That won't work - if NumOpt is found, the range's address will be
returned, not the range itself, so Set will fail.

 

[Bob Phillips]

ClasseurRepresentants. looks superfluous, what is it?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[echo---pscbro]

Hi there,

Thanks thanks for your help...

To see what this macro is all about, please follow this link :

http://www.good2know.freesurf.fr/startengtemp.html

And the complete macro :

Sub InsertRepresentativesInitials()
Dim ClasseurRep As Workbook
Dim Numdpt As String
Dim Colonne As Variant
Dim Initiales
Set ClasseurRep = GetObject("C:\TPExcel\Representants.xls")
Range("D4").Select
While ActiveCell.Value <> ""
Numdpt = Left(ActiveCell.Value, 2)
Set Colonne =
ClasseurRep.Sheets(1).Range("A4:F20").Find(What:=Numdpt, _
LookIn:=xlFormulas, LookAt:=xlWhole).Address
Colonne = Range(Colonne).Column
Colonne = CInt(Colonne)
Initiales = ClasseurRep.Sheets(1).Cells(3,
Colonne).Comment.Text
ActiveCell.Offset(0, -1).Range("A1").Select
Wend
Set ClasseurRep = Nothing
Workbooks("clients.xls").Close
End Sub

And the not working line :

Set Colonne =
ClasseurRep.Sheets(1).Range("A4:F20").Find(What:=Numdpt, _
LookIn:=xlFormulas, LookAt:=xlWhole).Address

I have tried a few of your answers, maybe I have missed one or two,
but it still wouldn't work.

This macro is from a book about Excel and VBA 2003. I am learning the
basic of VBA programming from there.

Hope this can help you help me.

Cheers,
Pascal