Help with a macro please
 

Hi All,
I have a macro that extracts data from the Web and inserts
it into my spreadsheet.

However, the number of names (entries) it extracts can
vary, and this results in my data being inserted in a
different Row position in my spreadsheet. The data however
is always inserted in column B to column H.


IMPORTANT: There is data above and below the headings
which I ignore.


This is how my data is presented after downloading.

Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 5.AOL 3.9 1.6
2.Shell 7.6 2.1 6.Spender 33.2 19.6
3.Jetson 16.2 2.6 7.Formit 26.3 6.4
4.Maybell n/a n/a 8.Jackson 9.6 5.3
Final First Second Day Scan Year

Or this etc (just more entries)
Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 7.Formit 26.3 6.4
2.Shell 7.6 2.1 8.Jackson 29.6 5.3
3.Jetson 16.2 2.6 9.Wenton 129.3 110.0
4.Maybell n/a n/a 10.Jacks 15.4 8.6
5.AOL 3.9 1.6
6.Spender 33.2 19.6
Final First Second Day Scan Year

I'm trying to get all the data between the top & bottom
headings and combine them into one list.

But the data downloaded from the Web is never placed in
the same Row on my spreadsheet.



This is what I'm trying to achieve, starting in cell
B51:C. Both sets of data are combined in a list.

Name Option1 Option2 Three Columns B, C, D
1.Nokia 35.8 10.0
2.Shell 7.6 2.1
3.Jetson 16.2 2.6
4.Maybell n/a n/a
5.AOL 3.9 1.6
6.Spender 33.2 19.6
7.Formit 26.3 6.4
8.Jackson 29.6 5.3
or 9 etc

Could anyone assist me with a macro that will achieve this.

Thankyou for any help
Regards,
Rick

Help with a macro please
 

You can always check for the last row of data with

iLastRow = Cells(Rows.Count,"B").End(xlUp).Row

and then paste the new data into the next row

ACtivesheet.Cells(iLatsRow,"B").Paste

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

" wrote in
message ...
Hi All,
I have a macro that extracts data from the Web and inserts
it into my spreadsheet.

However, the number of names (entries) it extracts can
vary, and this results in my data being inserted in a
different Row position in my spreadsheet. The data however
is always inserted in column B to column H.


IMPORTANT: There is data above and below the headings
which I ignore.


This is how my data is presented after downloading.

Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 5.AOL 3.9 1.6
2.Shell 7.6 2.1 6.Spender 33.2 19.6
3.Jetson 16.2 2.6 7.Formit 26.3 6.4
4.Maybell n/a n/a 8.Jackson 9.6 5.3
Final First Second Day Scan Year

Or this etc (just more entries)
Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 7.Formit 26.3 6.4
2.Shell 7.6 2.1 8.Jackson 29.6 5.3
3.Jetson 16.2 2.6 9.Wenton 129.3 110.0
4.Maybell n/a n/a 10.Jacks 15.4 8.6
5.AOL 3.9 1.6
6.Spender 33.2 19.6
Final First Second Day Scan Year

I'm trying to get all the data between the top & bottom
headings and combine them into one list.

But the data downloaded from the Web is never placed in
the same Row on my spreadsheet.



This is what I'm trying to achieve, starting in cell
B51:C. Both sets of data are combined in a list.

Name Option1 Option2 Three Columns B, C, D
1.Nokia 35.8 10.0
2.Shell 7.6 2.1
3.Jetson 16.2 2.6
4.Maybell n/a n/a
5.AOL 3.9 1.6
6.Spender 33.2 19.6
7.Formit 26.3 6.4
8.Jackson 29.6 5.3
or 9 etc

Could anyone assist me with a macro that will achieve this.

Thankyou for any help
Regards,
Rick

Help with a macro please
 

Hello Bob,
Thanks for your reply. Unfortunately I don't understand
your answer or I may have confused you with my post.

Basically, the question is how can I find a block of data
in my spreadsheet which has other data above and below it
after it has been downloaded, but is positioned in a
different Row location each time and has variable numbers
of entries. The data is always extracted to Col B but on a
different Row.

The data is extracted in the form below across columns
B, C, D, | E, | F, G, H,
Name, Option1, Option2,| Blank,| Name, Option1, Option2
Part A | | Part B


Then Part A and Part B are combined in a single list.

Because there is data above and below the block I am
interested in, there must be a way to find the data block
using markers like Name to define the start of the block
in columnB and Year to define the end of the block in
column H. Maybe I'm wrong but it seems logical to me. I'm
just trying to get this block but there is other
information above and below the block which confuses the
issue.

Would appreciate any further assistance as I'm lost here.

Kind regards,
Rick


-----Original Message-----
You can always check for the last row of data with

iLastRow = Cells(Rows.Count,"B").End(xlUp).Row

and then paste the new data into the next row

ACtivesheet.Cells(iLatsRow,"B").Paste

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"
wrote in
message ...
Hi All,
I have a macro that extracts data from the Web and
inserts
it into my spreadsheet.

However, the number of names (entries) it extracts can
vary, and this results in my data being inserted in a
different Row position in my spreadsheet. The data
however
is always inserted in column B to column H.


IMPORTANT: There is data above and below the headings
which I ignore.


This is how my data is presented after downloading.

Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 5.AOL 3.9 1.6
2.Shell 7.6 2.1 6.Spender 33.2 19.6
3.Jetson 16.2 2.6 7.Formit 26.3 6.4
4.Maybell n/a n/a 8.Jackson 9.6 5.3
Final First Second Day Scan Year

Or this etc (just more entries)
Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 7.Formit 26.3 6.4
2.Shell 7.6 2.1 8.Jackson 29.6 5.3
3.Jetson 16.2 2.6 9.Wenton 129.3 110.0
4.Maybell n/a n/a 10.Jacks 15.4 8.6
5.AOL 3.9 1.6
6.Spender 33.2 19.6
Final First Second Day Scan Year

I'm trying to get all the data between the top & bottom
headings and combine them into one list.

But the data downloaded from the Web is never placed in
the same Row on my spreadsheet.



This is what I'm trying to achieve, starting in cell
B51:C. Both sets of data are combined in a list.

Name Option1 Option2 Three Columns B, C, D
1.Nokia 35.8 10.0
2.Shell 7.6 2.1
3.Jetson 16.2 2.6
4.Maybell n/a n/a
5.AOL 3.9 1.6
6.Spender 33.2 19.6
7.Formit 26.3 6.4
8.Jackson 29.6 5.3
or 9 etc

Could anyone assist me with a macro that will achieve
this.

Thankyou for any help
Regards,
Rick



.

Help with a macro please
 

Hello Rick,,
Do you want to try this instead? It selects the block in my example


Dim oFound As Range
Dim iStart As Long
Dim iEnd As Long

With ActiveSheet.Cells
Set oFound = .Find("Name")
If Not oFound Is Nothing Then
iStart = oFound.Row
Set oFound = .Find("Year", after:=oFound.Offset(1, 0))
If Not oFound Is Nothing Then
iEnd = oFound.Row
Range(Cells(iStart, "B"), Cells(iEnd, "H")).Select
End If
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

" wrote in
message ...
Hello Bob,
Thanks for your reply. Unfortunately I don't understand
your answer or I may have confused you with my post.

Basically, the question is how can I find a block of data
in my spreadsheet which has other data above and below it
after it has been downloaded, but is positioned in a
different Row location each time and has variable numbers
of entries. The data is always extracted to Col B but on a
different Row.

The data is extracted in the form below across columns
B, C, D, | E, | F, G, H,
Name, Option1, Option2,| Blank,| Name, Option1, Option2
Part A | | Part B


Then Part A and Part B are combined in a single list.

Because there is data above and below the block I am
interested in, there must be a way to find the data block
using markers like Name to define the start of the block
in columnB and Year to define the end of the block in
column H. Maybe I'm wrong but it seems logical to me. I'm
just trying to get this block but there is other
information above and below the block which confuses the
issue.

Would appreciate any further assistance as I'm lost here.

Kind regards,
Rick


-----Original Message-----
You can always check for the last row of data with

iLastRow = Cells(Rows.Count,"B").End(xlUp).Row

and then paste the new data into the next row

ACtivesheet.Cells(iLatsRow,"B").Paste

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"
wrote in
message ...
Hi All,
I have a macro that extracts data from the Web and
inserts
it into my spreadsheet.

However, the number of names (entries) it extracts can
vary, and this results in my data being inserted in a
different Row position in my spreadsheet. The data
however
is always inserted in column B to column H.


IMPORTANT: There is data above and below the headings
which I ignore.


This is how my data is presented after downloading.

Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 5.AOL 3.9 1.6
2.Shell 7.6 2.1 6.Spender 33.2 19.6
3.Jetson 16.2 2.6 7.Formit 26.3 6.4
4.Maybell n/a n/a 8.Jackson 9.6 5.3
Final First Second Day Scan Year

Or this etc (just more entries)
Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 7.Formit 26.3 6.4
2.Shell 7.6 2.1 8.Jackson 29.6 5.3
3.Jetson 16.2 2.6 9.Wenton 129.3 110.0
4.Maybell n/a n/a 10.Jacks 15.4 8.6
5.AOL 3.9 1.6
6.Spender 33.2 19.6
Final First Second Day Scan Year

I'm trying to get all the data between the top & bottom
headings and combine them into one list.

But the data downloaded from the Web is never placed in
the same Row on my spreadsheet.



This is what I'm trying to achieve, starting in cell
B51:C. Both sets of data are combined in a list.

Name Option1 Option2 Three Columns B, C, D
1.Nokia 35.8 10.0
2.Shell 7.6 2.1
3.Jetson 16.2 2.6
4.Maybell n/a n/a
5.AOL 3.9 1.6
6.Spender 33.2 19.6
7.Formit 26.3 6.4
8.Jackson 29.6 5.3
or 9 etc

Could anyone assist me with a macro that will achieve
this.

Thankyou for any help
Regards,
Rick



.

Help with a macro please
 

Thanks very much Bob,
Appreciate your assistance,
Kind regards,
Rick

-----Original Message-----
Hello Rick,,
Do you want to try this instead? It selects the block in
my example


Dim oFound As Range
Dim iStart As Long
Dim iEnd As Long

With ActiveSheet.Cells
Set oFound = .Find("Name")
If Not oFound Is Nothing Then
iStart = oFound.Row
Set oFound = .Find("Year",
after:=oFound.Offset(1, 0))
If Not oFound Is Nothing Then
iEnd = oFound.Row
Range(Cells(iStart, "B"), Cells
(iEnd, "H")).Select
End If
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"
wrote in
message ...
Hello Bob,
Thanks for your reply. Unfortunately I don't understand
your answer or I may have confused you with my post.

Basically, the question is how can I find a block of
data
in my spreadsheet which has other data above and below
it
after it has been downloaded, but is positioned in a
different Row location each time and has variable
numbers
of entries. The data is always extracted to Col B but
on a
different Row.

The data is extracted in the form below across columns
B, C, D, | E, | F, G, H,
Name, Option1, Option2,| Blank,| Name, Option1, Option2
Part A | | Part B


Then Part A and Part B are combined in a single list.

Because there is data above and below the block I am
interested in, there must be a way to find the data
block
using markers like Name to define the start of the block
in columnB and Year to define the end of the block in
column H. Maybe I'm wrong but it seems logical to me.
I'm
just trying to get this block but there is other
information above and below the block which confuses the
issue.

Would appreciate any further assistance as I'm lost
here.

Kind regards,
Rick


-----Original Message-----
You can always check for the last row of data with

iLastRow = Cells(Rows.Count,"B").End(xlUp).Row

and then paste the new data into the next row

ACtivesheet.Cells(iLatsRow,"B").Paste

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"
wrote in
message ...
Hi All,
I have a macro that extracts data from the Web and
inserts
it into my spreadsheet.

However, the number of names (entries) it extracts
can
vary, and this results in my data being inserted in a
different Row position in my spreadsheet. The data
however
is always inserted in column B to column H.


IMPORTANT: There is data above and below the headings
which I ignore.


This is how my data is presented after downloading.

Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 5.AOL 3.9 1.6
2.Shell 7.6 2.1 6.Spender 33.2 19.6
3.Jetson 16.2 2.6 7.Formit 26.3 6.4
4.Maybell n/a n/a 8.Jackson 9.6 5.3
Final First Second Day Scan Year

Or this etc (just more entries)
Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 7.Formit 26.3 6.4
2.Shell 7.6 2.1 8.Jackson 29.6 5.3
3.Jetson 16.2 2.6 9.Wenton 129.3 110.0
4.Maybell n/a n/a 10.Jacks 15.4 8.6
5.AOL 3.9 1.6
6.Spender 33.2 19.6
Final First Second Day Scan Year

I'm trying to get all the data between the top &
bottom
headings and combine them into one list.

But the data downloaded from the Web is never placed
in
the same Row on my spreadsheet.



This is what I'm trying to achieve, starting in cell
B51:C. Both sets of data are combined in a list.

Name Option1 Option2 Three Columns B, C, D
1.Nokia 35.8 10.0
2.Shell 7.6 2.1
3.Jetson 16.2 2.6
4.Maybell n/a n/a
5.AOL 3.9 1.6
6.Spender 33.2 19.6
7.Formit 26.3 6.4
8.Jackson 29.6 5.3
or 9 etc

Could anyone assist me with a macro that will achieve
this.

Thankyou for any help
Regards,
Rick



.



.

Help with a macro please
 

Thanks very much Bob,
Appreciate your assistance,
Kind regards,
Rick

-----Original Message-----
Hello Rick,,
Do you want to try this instead? It selects the block in
my example


Dim oFound As Range
Dim iStart As Long
Dim iEnd As Long

With ActiveSheet.Cells
Set oFound = .Find("Name")
If Not oFound Is Nothing Then
iStart = oFound.Row
Set oFound = .Find("Year",
after:=oFound.Offset(1, 0))
If Not oFound Is Nothing Then
iEnd = oFound.Row
Range(Cells(iStart, "B"), Cells
(iEnd, "H")).Select
End If
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"
wrote in
message ...
Hello Bob,
Thanks for your reply. Unfortunately I don't understand
your answer or I may have confused you with my post.

Basically, the question is how can I find a block of
data
in my spreadsheet which has other data above and below
it
after it has been downloaded, but is positioned in a
different Row location each time and has variable
numbers
of entries. The data is always extracted to Col B but
on a
different Row.

The data is extracted in the form below across columns
B, C, D, | E, | F, G, H,
Name, Option1, Option2,| Blank,| Name, Option1, Option2
Part A | | Part B


Then Part A and Part B are combined in a single list.

Because there is data above and below the block I am
interested in, there must be a way to find the data
block
using markers like Name to define the start of the block
in columnB and Year to define the end of the block in
column H. Maybe I'm wrong but it seems logical to me.
I'm
just trying to get this block but there is other
information above and below the block which confuses the
issue.

Would appreciate any further assistance as I'm lost
here.

Kind regards,
Rick


-----Original Message-----
You can always check for the last row of data with

iLastRow = Cells(Rows.Count,"B").End(xlUp).Row

and then paste the new data into the next row

ACtivesheet.Cells(iLatsRow,"B").Paste

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"
wrote in
message ...
Hi All,
I have a macro that extracts data from the Web and
inserts
it into my spreadsheet.

However, the number of names (entries) it extracts
can
vary, and this results in my data being inserted in a
different Row position in my spreadsheet. The data
however
is always inserted in column B to column H.


IMPORTANT: There is data above and below the headings
which I ignore.


This is how my data is presented after downloading.

Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 5.AOL 3.9 1.6
2.Shell 7.6 2.1 6.Spender 33.2 19.6
3.Jetson 16.2 2.6 7.Formit 26.3 6.4
4.Maybell n/a n/a 8.Jackson 9.6 5.3
Final First Second Day Scan Year

Or this etc (just more entries)
Name Option1 Option2 Blank Name Option1 Option2
1.Nokia 35.8 10.0 7.Formit 26.3 6.4
2.Shell 7.6 2.1 8.Jackson 29.6 5.3
3.Jetson 16.2 2.6 9.Wenton 129.3 110.0
4.Maybell n/a n/a 10.Jacks 15.4 8.6
5.AOL 3.9 1.6
6.Spender 33.2 19.6
Final First Second Day Scan Year

I'm trying to get all the data between the top &
bottom
headings and combine them into one list.

But the data downloaded from the Web is never placed
in
the same Row on my spreadsheet.



This is what I'm trying to achieve, starting in cell
B51:C. Both sets of data are combined in a list.

Name Option1 Option2 Three Columns B, C, D
1.Nokia 35.8 10.0
2.Shell 7.6 2.1
3.Jetson 16.2 2.6
4.Maybell n/a n/a
5.AOL 3.9 1.6
6.Spender 33.2 19.6
7.Formit 26.3 6.4
8.Jackson 29.6 5.3
or 9 etc

Could anyone assist me with a macro that will achieve
this.

Thankyou for any help
Regards,
Rick



.



.