How to Loop a Macro??

[rodrigo_solares]

Hello to all,

I've been using this primitive macro to bring some values from website:

Sub Tracker()
    Sheets.Add
    Application.DisplayAlerts = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C3").Value, 16) _
        , Destination:=Range("A1"))
        .WebTables = "6,12"
        .Refresh BackgroundQuery:=False
    Range("GUS8301!A3").FormulaR1C1 = _
        "=IF(VLOOKUP(""Start run."",Sheet1!C[1],1,FALSE)=""Start run."",OFFSET(Sheet1!C[1],MATCH(""Start Run."",Sheet1!C[1],0)-1,-1,1,1),FALSE)"
    Range("GUS8301!A3").Copy
    Range("GUS8301!A3").PasteSpecial Paste:=xlPasteValues
    Range("GUS8301!B3").FormulaR1C1 = _
        "=IF(VLOOKUP(""End run."",Sheet1!C,1,FALSE)=""End Run."",OFFSET(Sheet1!C,MATCH(""End Run."",Sheet1!C,0)-1,-1,1,1),FALSE)"
    Range("GUS8301!B3").Copy
    Range("GUS8301!B3").PasteSpecial Paste:=xlPasteValues
    End With
End Sub

This macro is currently looking the website with data from cell C3 of GUS8301 Sheet... I want to be able to do this task for every cell with data in Column "C"...

Any help here would be greatly appreciated!!

 

[ravishankar]

HI
try this

x= cells(rows.count,3).end(xlUp).row
for a = 3 to x
.......paste your macro here.....
next a

and change "URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C3").Value, 16) _
to "URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C" & a).Value, 16) _
Run the macro
Ravi

 

[rodrigo_solares]

Hello Ravi,

The loop is working perfectly; however now i have two new problems:

1.- The macro is only copying the data to cell A3 & B3... not to all cells A & B... In fact, it is creating new sheets for every web query, but it is not copying the information from every new sheet to it's location...

2.- How can i add an instruction to the macro to delete the new sheet after it has copy & pasted the data that I'm looking; because after I've pasted the data, I don't longer need the sheet.

I'm attaching a copy of the Worksheet.. Hope this will help clarify a few things.

TRACKER PROJECT APRIL.xls
	A	B	C	D	E
1	StartRun	EndRun	File	RunTime	State
2			GUS8301DBNAWECLD	2.00705E+13	H
3	39203.48	39203.49	GUS8301DBNAAKQ4K	2.00705E+13	C
4			GUS8301DBN5TQWIL	2.00704E+13	C
5			GUS8301DBN5I5OXM	2.00704E+13	C
6			GUS8301DBN4QS12B	2.00704E+13	C
7			GUS8301DBN4FMCXE	2.00703E+13	C
8			GUS8301DBN40145J	2.00703E+13	C
9			GUS8301DBN3OK0SD	2.00703E+13	C
10			GUS8301DBN31ZVXH	2.00703E+13	C
11			GUS8301DBN2AFH3M	2.00702E+13	C
12			GUS8301DBN1NVFIG	2.00702E+13	C
GUS8301

As you can see.. the macro is only copying data from the first sheet to cells A3 & B3.. I want it to be able to copy data from every new sheet to it's corresponding Row.

Thanks in advance guys! and sorry for all the trouble!

 

[rodrigo_solares]

Ok,

I've solved the part where it's only copying data to the Cell A2 & B2 by adding the same character "& a" to every cell referece. However; The Offset formulas that I'm using are still refering to the Sheet1 instead of every new sheet.

I can only see two ways around this:

Either the formula needs to be changing to every new sheet or we enter into the macro an instruction to rename every new sheet to a specific name used by the formula (e.g. "TRACKER"). Therefore we would always look in the right Sheet.

 

[rodrigo_solares]

Hello again,

All these problems have been solved with this code:

Sub Tracker()
    x = Cells(Rows.Count, 3).End(xlUp).Row
    For a = 2 To x
    Sheets.Add.Name = "Tracker"
    Application.DisplayAlerts = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C" & a).Value, 16) _
        , Destination:=Range("A1"))
        .WebTables = "6,12"
        .Refresh BackgroundQuery:=False
    Range("GUS8301!A" & a).FormulaR1C1 = _
        "=IF(VLOOKUP(""Start run."",Tracker!C[1],1,FALSE)=""Start run."",OFFSET(Tracker!C[1],MATCH(""Start Run."",Tracker!C[1],0)-1,-1,1,1),FALSE)"
    Range("GUS8301!A" & a).Copy
    Range("GUS8301!A" & a).PasteSpecial Paste:=xlPasteValues
    Range("GUS8301!B" & a).FormulaR1C1 = _
        "=IF(VLOOKUP(""End run."",Tracker!C,1,FALSE)=""End Run."",OFFSET(Tracker!C,MATCH(""End Run."",Tracker!C,0)-1,-1,1,1),FALSE)"
    Range("GUS8301!B" & a).Copy
    Range("GUS8301!B" & a).PasteSpecial Paste:=xlPasteValues
    End With
    Sheets("Tracker").Delete
    Next a
End Sub

Now I've arrived to stage 2 of the file. In the next worksheet all cells in column A & B have been correctly filled with the macro. The table beggining in column "C" it's a webquery, so I refresh it daily. Here's a image of the file filled by the macro...

TRACKER PROJECT JULY.xls
	A	B	C	D	E
70	38552.41	38552.42	GUS8301DBNEBHZKD	2.00507E+13	C
71	38534.48	38534.48	GUS8301DBNDF1KCP	2.00507E+13	C
72	38530.79	38530.8	GUS8301DBND4UMFJ	2.00506E+13	C
73	38523.71	38523.71	GUS8301DBNCTDHTI	2.00506E+13	C
74	38518.71	38518.71	GUS8301DBNCI1SNA	2.00506E+13	C
75	38504.88	38504.88	GUS8301DBNBUCOBM	2.00506E+13	C
76	38495.88	38495.88	GUS8301DBNBFHZVK	2.00505E+13	C
GUS8301

If I refresh the table the data now looks like this..

TRACKER PROJECT JULY.xls
	A	B	C	D	E
71	38534.48	38534.48	GUS8301DBNH3R3EI	2.00509E+13	C
72	38530.79	38530.8	GUS8301DBNGLB03J	2.00509E+13	C
73	38523.71	38523.71	GUS8301DBNG13QSO	2.00508E+13	C
74	38518.71	38518.71	GUS8301DBNFPMMEJ	2.00508E+13	C
75	38504.88	38504.88	GUS8301DBNEY4DZG	2.00508E+13	C
76	38495.88	38495.88	GUS8301DBNEMZ5CI	2.00507E+13	C
77			GUS8301DBNEBHZKD	2.00507E+13	C
78			GUS8301DBNDF1KCP	2.00507E+13	C
79			GUS8301DBND4UMFJ	2.00506E+13	C
80			GUS8301DBNCTDHTI	2.00506E+13	C
81			GUS8301DBNCI1SNA	2.00506E+13	C
82			GUS8301DBNBUCOBM	2.00506E+13	C
83			GUS8301DBNBFHZVK	2.00505E+13	C
GUS8301

I want the macro to Refresh the table, then copy the cells in A & B to the bottom of the table (to match column C) and then go look in the new webquery (the macro i already have) the data but "ONLY FOR THE CELLS IN COLUMN A that are blank i.e. the new ones".

Any help here would be greatly appreciated!!!

 

[ravishankar]

Hi
you have used the hint I provided and have solved the rest of your problem. Good. I am unable to follow how you generate col A & B and refresh it and what relation it has with C etc.. can you elaborate?
Ravi

 

[rodrigo_solares]

Hi Ravi,

There is a webquery in the Worksheet in Cell C1... (That's the green cells). Columns A & B were filled with the macro you helped me make (The macro copy and pastes the values for all cells in Column A & B).

So far, so good

Let's say that the webquery has data from row 1 (Cell C1) up to row 70. When i refresh the webquery, new information gets refreshed and the data is now from row 1 to row 75. Therefore what was in cell C1 is now in cell C6.

Here is where the problem begins, because the data that was previously pasted into Column A & B doesn't move... And i have a problem because A1, B1 & C1 have a relationship, but since cell C1 has moved to cell C6 (because there is new data from the refresh) the data is no longer accurate. A1, B1 & C6 is the correct relation.

What i want the macro to do, is to move the data from Columns A & B to match the correct data from column C. For example, If the data in column C was moved by 5 rows below, i want to be able to copy column A & B five columns below.

After I made the movement, i want the macro to be able to run (the orignal macro where we populate the column A & B) only if the cell in column A is blank.

It's a little bit complicated... hope this explanation makes sense,.