Newbie with VBA for Excel

  • Thread starter Thread starter echo---pscbro
  • Start date Start date
E

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
 
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
 
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.
 
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
 
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
 
That won't work - if NumOpt is found, the range's address will be
returned, not the range itself, so Set will fail.
 
ClasseurRepresentants. looks superfluous, what is it?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
 
Back
Top