Variable with offset

[petofisanyi]

Hi All,

I have a vba code, can't figure out what's wrong, I'm trying to download with query web tables. What I want is:
entry some share names in cells, (the share names are the only variables in the weblinks, with concatenate equation I have the right links in cells) and I expect to get back every table to different sheet, with sheet name = share names entered to cells.

The vba codes works fine individually.
1. code:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff ; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }span.s1 {color: #011993 }span.s2 {color: #008f00 }span.s3 {color: #000000 }</style>

Sub newsheettest()




    Dim MyCell As Range, MyRange As Range
     
    Set MyRange = Sheets("CONTROL").Range("B:B")


    For Each MyCell In MyRange
    If MyCell <> "" Then


        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
   End If

        
Next MyCell




End Sub

2. code:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00 ; background-color: #ffffff }span.s1 {color: #011993 }span.s2 {color: #000000 }</style>

Sub UseDynamicURL()
    Dim wb As Workbook
    Dim src As Worksheet
    Dim tgt As Worksheet


    Set wb = ThisWorkbook
    Set src = wb.Sheets("DATA")
    Set tgt = Sheets(Sheets.Count)




    Dim url As String


    url = src.Range("A1")




    With tgt.QueryTables.Add(Connection:= _
        url, _
        Destination:=tgt.Range("A1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
End Sub

3. code (would be the combination of 1.-2.) not working:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff ; min-height: 13.0px}span.s1 {color: #011993 }span.s2 {color: #000000 }span.s3 {color: #008f00 }</style>

Sub Macro3()


    Dim wb As Workbook
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim url As Integer


    
    Set wb = ThisWorkbook
    Set src = wb.Sheets("DATA")
    Set tgt = Sheets(Sheets.Count)
    


    Dim MyCell As Range, MyRange As Range
    


     
    Set MyRange = Sheets("DATA").Range("C:C")


    For Each MyCell In MyRange
    If MyCell <> "" Then


        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
        Sheets(Sheets.Count).Select
        Range("A1").Select


[COLOR=#ff0000][U]        url = MyCell.Offset(0, -2)[/U][/COLOR]
       
        With tgt.QueryTables.Add(Connection:= _
        url, _
        Destination:=tgt.Range("A1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With




    End If


        
Next MyCell




End Sub

I have the links with 2 columns to left from the share codes, I try to us offset, but there is something wrong with specifications: I got error: Run-time error '13'
Type mismatch

options: Abort , Debug

Hoping that for somebody is pretty simple to resolve, Thank You in advance!

 

[petofisanyi]

Hey Guys, I worked it out, maybe it is a help for somebody, it works if web link is concatenated in sheet "DATA" column "A:A" with variables in link in column "C:C":

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff ; min-height: 13.0px}span.s1 {color: #011993 }span.s2 {color: #000000 }</style>Sub Macro3()


Dim url As Variant
Dim MyCell As Range
Dim MyRange As Range

Set MyRange = Sheets("DATA").Range("C:C")

For Each MyCell In MyRange
url = MyCell.Offset(0, -2).Value

If MyCell <> "" Then


Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value

Sheets(Sheets.Count).Select


With ActiveSheet.QueryTables.Add(Connection:= _
url, _
Destination:=ActiveSheet.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


End If

Next MyCell




End Sub