Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie with VBA for Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie with VBA for Excel
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 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie with VBA for Excel
try
SET Colonne = ClasseurRepresentants.Sheets(1).Range("A4:F20").Fi nd(What:=NumDpt, _ LookIn:=xlFormulas, LookAt:=xlWhole).Address -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie with VBA for Excel
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 -- HTH... Jim Thomlinson "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie with VBA for Excel
That won't work - if NumOpt is found, the range's address will be
returned, not the range itself, so Set will fail. In article , Gary Brown wrote: try SET Colonne = ClasseurRepresentants.Sheets(1).Range("A4:F20").Fi nd(What:=NumDpt, _ LookIn:=xlFormulas, LookAt:=xlWhole).Address |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie with VBA for Excel
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" wrote in message ups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie with VBA for Excel
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:=N umdpt, _ 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:=N umdpt, _ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real Newbie newbie question | New Users to Excel | |||
help for an excel newbie | Excel Worksheet Functions | |||
Please help an Excel Newbie! | New Users to Excel | |||
Excel VBA Newbie | Excel Programming | |||
Newbie help...VBA w/ Excel | Excel Programming |