View original (French)

VBA Excel copy cell with hyperlink

Solved
grandjean -  
 grandjean -
Hello everyone,

I am working on a client database. In the client data, I of course have the website.
I can generate the hyperlink to the client's website, but the routine places the result not in my client database (B_Client), but in the tab that manages the numbering (Num_An), in cell C4.
I tried with Anchor = sheets("B_Client").range("P" & lig), without success.
I assume that the active cell is always C4 of Num_An.

If someone could help me... I have searched a bit everywhere on the net, with no success so far.

Here’s the beginning of my macro, for more precision (I am using Excel 2003)

Sub Creation_Client()

'creation of the client number
Dim NumClient As Integer
Worksheets("Num_An").Select
Range("C4").Select
NumClient = Worksheets("Num_An").Range("C4").Value
NumClient = NumClient + 1
Worksheets("Num_An").Range("C4").Value = NumClient

'identify a free row and copy the data (C_Client = input form & B_Client = database)
Dim lig As Integer

With Sheets("B_Client")
lig = .Columns("A").Find("", .Range("A3"), xlValues).Row
.Cells(lig, "A").Resize(1, 27) = Application.Transpose(Sheets("C_Client").Range("C2:C28").Value)
.Range("A3:AA" & lig).Sort .Range("A3")

'creation of the hyperlink to the website

Sheets("C_Client").Range("C17").Copy
site = Sheets("C_Client").Range("C17")
Selection = .Range("P" & lig)
Sheets("B_Client").Hyperlinks.Add anchor:=Selection, Address:="http://" & site

End With

Configuration: Windows XP / Safari 535.1

6 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    Selection is a keyword in VBA, you cannot use it as a variable name.
    And you can simplify your code, you have unnecessary .select and .copy

    eric
    0
    1. grandjean
       
      Thank you Eric for looking at my topic, but I admit that your response doesn't help me much... I have a feeling there is something wrong... but I would like to know how to write my instruction.
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Sel
      Pour le reste avec un fichier joint (allégé et anonymisé) pour ne pas avoir à tout refaire on pourra modifier ton code.
      cijoint.fr et coller ici le lien fourni
      eric
      0
  2. grandjean
     
    I tried replacing it with Sel, as you suggested. No success. I also tried with Anchor.

    My cleaned file is available at:
    http://www.cijoint.fr/cjlink.php?file=cj201109/cijY07VilL.xls

    I explain the functionality on the homepage.
    Thank you in advance for your help. Still, enjoy the sunshine on this beautiful day.

    Jean
    0
  3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    re,

    your corrected code: 
    Sub Creation_Client() 'creation of the client number Dim NumClient As Integer  NumClient = [Num_An!C4] + 1 [Num_An!C4] = NumClient 'identify a free row and copy the data (C_Client = input form & B_Client = database) Dim lig As Integer With Sheets("B_Client") lig = .Columns("A").Find("", .Range("A3"), xlValues).Row .Cells(lig, "A").Resize(1, 27) = Application.Transpose(Sheets("C_Client").Range("C2:C28").Value) .Range("A3:AA" & lig).Sort .Range("A3") 'create the hyperlink for the website  Sheets("B_Client").Hyperlinks.Add anchor:=.Range("P" & lig), Address:="http://" & [C_Client!C17] End With 'sort the items in ascending order Range("A3:AA3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'clear the form With Sheets("C_Client") .Range("c2").ClearContents .Range("c5:c10").ClearContents .Range("c13").Value = "FRANCE" .Range("C23").Value = "FRANCE" End With 'return to the Clients Base sheet Sheets("B_Client").Activate Range("A2").Select End Sub

    I made the hyperlink and also modified the first lines a bit (5 lines while 2 are enough).
    When you are in a With Sheets("B_Client"), you have to replace the sheet name with a . : anchor:=.Range(...

    eric
    0
  4. grandjean
     
    Thank you very much, Eric, because your solution works perfectly. It's nice to know!
    However, I have a problem with sorting.

    I deleted all the records from my database to start over.
    When I enter the first client, the information goes well on the first line, except the website which displays on the second line.
    When I enter the second client, it goes back in order.
    However, after that, I created a client that inserts between the first two, and there, it properly attaches the website but without the link.
    Could the fact that I start on line 3 and not on line 2 cause an error?
    Otherwise, my sorting method might not be the right one.
    As you may have noticed, I'm just starting with VBA, which I find great. I've found some nice tutorials, but we quickly become overly ambitious!
    0
  6. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Re,

    First, I won't use .find for the first free row, but:
    row = .[A65536].end(xlup).row + 1
    (it's the equivalent of ctrl+up arrow, starting from the bottom, which takes you to the last filled row. +1 for the next one. It's just a matter of speed, but it's good that you noticed .find, it will come in handy later ;-) ))

    Next, you need to paste everything: data and link.
    Only after can you sort (there's a .sort floating around in the middle, and row no longer points to the right line...)

    Eric
    0
  7. grandjean
     
    Thank you for your help, Eric. I struggled a bit to find the source of the problem. Indeed, there was an extra spell.
    Everything is working well now.
    I can tackle the next phases!
    Jean
    0