CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Simple Code - Help

    I Need a simple script like this one to do this in excel .... but with a loop or something from row 17 to row 100 and i dont want to repeat the same line 80 times. I know its simple but i dont know : (

    Code:
    Sub Macro1()
    
    Range("H17").Select
    Selection.Hyperlinks.Add Anchor:=Selection, Address:=Range("AZ17")
    
    Range("H18").Select
    Selection.Hyperlinks.Add Anchor:=Selection, Address:=Range("AZ18")
    
    Range("H19").Select
    Selection.Hyperlinks.Add Anchor:=Selection, Address:=Range("AZ19")
    
    End Sub

    Thanks !!!!!!



    Regards
    Francisco
    Last edited by Frlecube; August 4th, 2009 at 02:31 PM.

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Simple Code - Help

    Freehand, but should be fine

    Code:
    Sub Macro1()
    
    Dim myRange as String, Ctr as Integer
      For Ctr=17 to 100 ' or whatever
        myRange="H" & val(ctr) ' Build Value
        Range(myRange).Select ' Use Value here!
          Selection.Hyperlinks.Add Anchor:=Selection, Address:=Range("AZ17")
      Next Ctr
    End Sub
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Simple Code - Help

    Looks good. Except for Address:Range("AZ17")
    Shouldn't that be Address:Range("AZ"&CSTr(ctr))
    also myRange="H"&Cstr(ctr) not &Val(ctr)
    Val is not resulting in a string, unless VBA is completely different to VB.

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Simple Code - Help

    Cstr() adds a leading space, as I recall. I figured the compiler would help out there
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Simple Code - Help

    If I remember correctly CStr() does not use a leading space STR() adds one if the number is positive.

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Simple Code - Help

    That's correct. New it was close...
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Mar 2009
    Posts
    12

    Re: Simple Code - Help

    instead of range user cells(row,col).
    Here you provide integer as row and column.
    eg : for B you use 2, c 3, etc

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured