Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
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:=N umdpt, _ LookIn:=xlFormulas, LookAt:=xlWhole).Address Colonne = Range(Colonne).Column Colonne = CInt(Colonne) Initiales = ClasseurRep.Sheets(1).Cells(3,Colonne).Comment.Tex t 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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
On Feb 6, 7:13 pm, "merjet" wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
The thing is that if you try with the files I have uploaded, it
doesn't work.What needs to be done? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
If you getting run-time error 91, then you can do this.
After: Range("D4").Select Insert: On Error Resume Next Merjet |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
Something is not working on my system.
From the file I have uploaded, when you run the macro can you have the 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
On Feb 7, 12:33 am, "merjet" wrote:
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
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 On Feb 7, 12:33 am, "merjet" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie, vba simple prg from a book doesn't write...see vlookup with vba?
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(xlDo wn).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:=N umdpt, _ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie he I have a question about a print macro I am trying to write... | Excel Programming | |||
Newbie questions - probably simple | Excel Programming | |||
I want to write a book on my computer.How can I type? | New Users to Excel | |||
How do I get a Template to write a Book ? | New Users to Excel | |||
IF then write new worksheet/book | Excel Programming |