Help with a macro please

  • Thread starter Thread starter rjc44
  • Start date Start date
R

rjc44

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
 
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)
 
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)
 
Back
Top