Loop a range in code

[ONIWE]

Hello,

Please how can I put a loop in the code below to change the range so the code can perform 10 or more different searches. that is change range("y5") to Y6 and more.

With Sheets("Creditsafe").QueryTables.Add(Connection:="URL;" & Sheets("report").range("y5").Value, _
Destination:=Sheets("Creditsafe").range("A1"))

 

[patel45]

set rng=Sheets("report").Range("Y5:Y25")
for each cell in rng
   With Sheets("Creditsafe").QueryTables.Add(Connection:="URL;" & cell.Value, _
         Destination:=Sheets("Creditsafe").range("A1"))
.........
next

 

[daverunt]

Hi,

something along the lines of:

Inpt = InputBox("Enter end search number")
For x = 5 To Inpt

With Sheets("Creditsafe").QueryTables.Add(Connection:="URL;" & Sheets("report").Range("y" & x).Value, _
Destination:=Sheets("Creditsafe").range("A1"))'Destination:=Sheets("Creditsafe").Range("A1"))
End With

Next

 

[ONIWE]

Guys,
thanks but Im struggling with it. Its still returning errors with both codes. that keeps bouncing back as it cannot find the right URL.

.Refresh BackgroundQuery:=False

 

[ONIWE]

ADVERTISEMENT

It now works.

Thanks Patel and Daverunt.


Cheers

 

[daverunt]

Hi,

I was just about to respond.
that failure appears on the specified line if there is no valid URL in one of the search cells.

Glad it's working.

 

[ONIWE]

ADVERTISEMENT

Hi Dave,

One more problem. Please see below.

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
Sheets("creditsafe").Delete

Next

Application.ScreenUpdating = False
Loop Until IsEmpty(Sheets("report").Range("y" & x))
End Sub




How do I end the code. It returns errors once it gets to a blank and i tried using a loop to stop it.

 

[daverunt]

Hi,

Not sure how it's going to fit into your current code.
I am posting the code i used for testing. Hopefully you'll be able to see what you need.

Sub URL_Get_Query()
x = 1
For x = 1 To 25
Do While Not IsEmpty(Sheets("Sheet3").Range("A" & x).Value)
  With ActiveSheet.QueryTables.Add(Connection:="URL;" & Sheets("Sheet3").Range("A" & x).Value, _
       Destination:=Sheets("Sheet2").Range("A1")) 'Destination:=Sheets("Creditsafe").Range("A1"))
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
   x = x + 1
 Loop
   Next
End Sub

 

[ONIWE]

all sorted now. Thanks Daverunt

 

[daverunt]

Typical....just as i post.
Great glad it's working though