Loop Only Partially Works

gallopingant

New Member
Joined
Aug 19, 2020
Messages
9
Office Version
  1. 365
Platform
  1. MacOS
I have a For Each loop that only partially works. The idea is that when the submit button is clicked, hyperlinks are created with the value of the cell appended to the end of the hyperlink. Something like a unique ID that takes you to a specific website for that particular identifier. The problem is that the loop only returns the last value, though when I add a MsgBox and have it return that, it returns all the values.

The cells are across multiple rows and columns, as indicated before and I'd like the hyperlinks to be generated in the same manner.

What would cause this? Here is the relevant portion of the code. There can only be up to a pre-determined number of codes entered. There can be less, but not more, which is why I through the If statement in there.

VBA Code:
        Dim c As Range
        Dim rng As Range
        Set rng = Worksheets("Form").Range("H1:I4")
        
        For Each c In rng
            If c > 0 Then
                ThisWorkbook.Worksheets("Form").Hyperlinks.Add Range("J1:K4"), Address:="website.url?ID=" & c.Value, TextToDisplay:="Product Number " & c.Value
                MsgBox (c.Value)
            End If
        Next c
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try changing this line
Rich (BB code):
ThisWorkbook.Worksheets("Form").Hyperlinks.Add c.Offset(0, 2), Address:="website.url?ID=" & c.Value, TextToDisplay:="Product Number " & c.Value
As you have it written, you're adding each hyperlink to all 8 cells in J1:K4 rather than just the one in a relative position to c. Each pass of the loop is overwriting the previous one.
 
Upvote 0
That worked and solved the issue, thank you! Could you explain what is happening with the offset function? Previously the hyperlink only appeared once (though it was clickable in all 8 cells). I don't think I understand your last sentence that "each pass of the loop is overwriting the previous one."
 
Upvote 0
Your loop is looping through 8 cells in the range H1:I4, so the code between For and Next is executed 8 times (8 hyperlinks written, 8 message boxes shown).

On the first pass, the message box shows what is expected from H1, but the line above it is editing every cell in the range J1:K4, not just J1 as you might think.
On the second pass of the loop, the message box shows I1, the hyperlink for I1 has been written to all 8 cells in J1:K4, overwriting the link for H1.
This repeats until the message box shows I4 and all 8 cells in J1:K4 have been overwritten with the hyperlink of I4.

Using the Offset function means that only the cell in J1:K4 that is relative to the current cell in the loop is edited. c.Offset(0, 2) means that the hyperlink is added to the cell that is 2 to the right of c.
 
Upvote 0
As a follow-up, if I wanted to have the hyperlinks display on a different worksheet in a different range, how would I do that? I tried the following:

VBA Code:
Dim c As Range
        Dim rng As Range
        Set rng = Worksheets("Form").Range("H1:I4")
        
        For Each c In rng
            If c > 0 Then
                ThisWorkbook.Worksheets("OtherForm").Hyperlinks.Add Range("J1:K4"), Address:="website.url?ID=" & c.Value, TextToDisplay:="Product Number " & c.Value
                MsgBox (c.Value)
            End If
        Next c

I thought if I went back to the original code that it would work, but instead it still posts to the main worksheet.
 
Upvote 0
If you go back to the original code you will go back to the original problem, you will be adding the same link to all 8 cells in J1:K4 then overwriting it each time the loop runs.
Offset only works with ranges on the same sheet so this time a relative reference has to be created manually. I think this should work but haven't tested it.
VBA Code:
Dim c As Range
        Dim rng As Range
        Set rng = Worksheets("Form").Range("H1:I4")
        
        For Each c In rng
            If c > 0 Then
                With ThisWorkbook.Worksheets("OtherForm")
                    .Hyperlinks.Add .Cells(c.Row, c.Column +2), Address:="website.url?ID=" & c.Value, TextToDisplay:="Product Number " & c.Value
                End With
                MsgBox (c.Value)
            End If
        Next c
 
Upvote 0
If you go back to the original code you will go back to the original problem, you will be adding the same link to all 8 cells in J1:K4 then overwriting it each time the loop runs. Offset only works with ranges on the same sheet so this time a relative reference has to be created manually. I think this should work but haven't tested it.
VBA Code:
Dim c As Range
        Dim rng As Range
        Set rng = Worksheets("Form").Range("H1:I4")
       
        For Each c In rng
            If c > 0 Then
                With ThisWorkbook.Worksheets("OtherForm")
                    .Hyperlinks.Add .Cells(c.Row, c.Column +2), Address:="website.url?ID=" & c.Value, TextToDisplay:="Product Number " & c.Value
                End With
                MsgBox (c.Value)
            End If
        Next c
Thanks, that partially worked. It ended up on a different page of the worksheet so I played with the relative settings and was able to get it close, but not exactly where I need it. Is it not possible to just specify the range you want it to display in?
 
Upvote 0
You need to specify the individual cell that each link is to be applied to, you can not specify the whole range otherwise you will be back to the problem that you had before.
This means that you either need to set a relative position as in the code that I provided for you, or you need to use search commands to find the correct position on the other sheet based on information that is already in that sheet.

The answer that I provided was correct for the question that you asked and the ranges that you had used in your code, if it's not doing what you want then you need to be clearer on what it is that you actually want instead of constantly trying to return to a method that will never work.
 
Upvote 0
You need to specify the individual cell that each link is to be applied to, you can not specify the whole range otherwise you will be back to the problem that you had before.
This means that you either need to set a relative position as in the code that I provided for you, or you need to use search commands to find the correct position on the other sheet based on information that is already in that sheet.

The answer that I provided was correct for the question that you asked and the ranges that you had used in your code, if it's not doing what you want then you need to be clearer on what it is that you actually want instead of constantly trying to return to a method that will never work.
I'm new to VBA so I'm learning as I go. If there's a way to specify specific cells that would be great since that will remain a constant and not change at any point. By mentioning me playing with the relative positioning, I was attempting to see if I could move it around on my own and learn how it works in the process; I wasn't implying your answer wasn't helpful.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top