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

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

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
 
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?
Click to expand...

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
 
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
 
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
Click to expand...

so what should the not working line be like:

with or without set?

tx
 
so what should the not working line be like:
with or without set?
Click to expand...

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

Merjet
 
The thing is that if you try with the files I have uploaded, it
doesn't work.What needs to be done?
 
If you getting run-time error 91, then you can do this.

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

Merjet
 
Something is not working on my system.
From the file I have uploaded, when you run the macro can you have the
Click to expand...
colomn C in Workbook "clients" filled up with the initials in the
yellow comment tags?

Let me know and please show me the macro you're running because me
nothing happens but everything closes down as if nothing had happen. I
tried with On Error Resume, but it yields no changes.

Free me!

Pascal
 
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
 
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
Click to expand...

My fault,

I update the link right now.

Sorry about that.
Pascal
 
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
 
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:D" & 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
 
Back
Top