newbie, vba simple prg from a book doesn't write...see vlookup with vba?

[echo---pscbro]

Hi there, message already posted as a reply but fallen into disuse

Thanks thanks for your help anyway...

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)
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

Just a question in between, is this line equivalent to v/hlookup
function in Excel?

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

 

[merjet]

I didn't download your files, but created a file to use your macro.
The line you said is not working (without "Set") worked fine for me.

Just a question in between, is this line equivalent to v/hlookup
function in Excel?

No. Find is not restricted to a rectangular block of cells. Vlookup
and Hlookup are so restricted. They look for something in the first
column (row) of the restricted range and then get the value in a
prescribed column (row) in the same row (column).

Hth,
Merjet

 

[merjet]

Correction. It worked fine for me when it could find what it looked
for. However, I got a run-time error 91 when it could NOT find what it
looked for.

Hth,
Merjet

 

[echo---pscbro]

Correction. It worked fine for me when it could find what it looked
for. However, I got a run-time error 91 when it could NOT find what it
looked for.

Hth,
Merjet

so what should the not working line be like:

with or without set?

tx

 

[merjet]

so what should the not working line be like:

with or without set?

Without. "Set" isn't in the Sub you posted. But you added "Set" in
your comment after the Sub.

Merjet

 

[merjet]

If you getting run-time error 91, then you can do this.

After: Range("D4").Select
Insert: On Error Resume Next

Merjet

 

[merjet]

What you are trying to achieve big picture wise?
What changes are you expecting? Your code doesn't appear to try to
change anything in either workbook; all it does is look.
Which workbook is your macro in?
Why does your macro get Representants.xls, but close clients.xls? In
general, when VBA code works with 2 workbooks, it should be made clear
which one it is trying to read, change, etc.
By the way, the link you gave allows downloading Representants.xls,
but not clients.xls. Clicking the latter sent me to another webpage.

Merjet

 

[echo---pscbro]

What you are trying to achieve big picture wise?
What changes are you expecting? Your code doesn't appear to try to
change anything in either workbook; all it does is look.
Which workbook is your macro in?
Why does your macro get Representants.xls, but close clients.xls? In
general, when VBA code works with 2 workbooks, it should be made clear
which one it is trying to read, change, etc.
By the way, the link you gave allows downloading Representants.xls,
but not clients.xls. Clicking the latter sent me to another webpage.

Merjet

My fault,

I update the link right now.

Sorry about that.
Pascal

 

[echo---pscbro]

Okay, the link is now updated ::: the file "client.xls" was
missing...That's where the macro is.

This macro should get the content of the yellow tab comment in
"representant.xls" and insert it into "clients.xls" regarding their
geographic repartition. The 2 first numbers of the client numbers are
related to the geographic distribution of sales representatives...you
see. Read, the macro with the 2 workbooks open and things should take
shape.

Thanks Merjet for everything again, I'll check more carefully my link
next time when posting,

Pascal

 

[merjet]

Try then following.
-Merjet

Sub InsertRepresentativesInitials()
Dim ClasseurRep As Workbook
Dim Numdpt As String
Dim Colonne As Variant
Dim c As Range
Dim iEnd As Long

Set ClasseurRep = Workbooks.Open("C:\TPExcel\Representants.xls")
On Error Resume Next
iEnd = ThisWorkbook.Sheets("Feuil1").Range("D4").End(xlDown).Row
For Each c In ThisWorkbook.Sheets("Feuil1").Range("D4" & iEnd)
Numdpt = Left(c.Value, 2)
Colonne = ""
Colonne =
ClasseurRep.Sheets(1).Range("A4:F20").Find(What:=Numdpt, _
LookIn:=xlFormulas, LookAt:=xlWhole).Address
If Colonne <> "" Then
Colonne = CInt(Range(Colonne).Column)
c.Offset(0, -1) = ClasseurRep.Sheets(1).Cells(3,
Colonne).Comment.Text
End If
Next c
ClasseurRep.Close
Set ClasseurRep = Nothing
End Sub