Section Of Code Modification

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,465
Office Version
  1. 2007
Platform
  1. Windows
Hi Guys,

I have the following part of code

It counts rows after the word "Date" is found

the problem i have is that it counts some ugly text after the dates, which is what i dont need

is there a way to enter a blank line after the dates have been counted?

the code is

Code:
Set keywordCell = .Cells.Find(What:="Date")
        
        If Not keywordCell Is Nothing Then
        
            'Count the rows of Match Stats
            
            rows = 0
            Do While keywordCell.Offset(rows + 1, 0).Value <> ""
                rows = rows + 1
            Loop
            
            If rows > 0 Then
            
                'Copy the rows and 3 columns of Match Stats from web query sheet to Data sheet
                'using copy/paste special/values only
                            
                .Range(keywordCell.Offset(1, 0), keywordCell.Offset(rows, 2)).Copy
                dataSheet.Range(URLcell.Offset(0, 1).Value).PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
            
            End If
            
        Else
            MsgBox "Unable to extract Match Stats for " & URLcell.Value & " because the data returned by the web query " & _
                "doesn't contain the word 'Date' which should mark the start of the Match Stats data"
        End If

Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,465
Office Version
  1. 2007
Platform
  1. Windows
Hi Andrew,

Maybe "Ugly" is the wrong word

The code (part of a bigger code) counts "Date","Match","Points" from a web page and copies it to another sheet but...

it also copies "Game Stats" and some other text at the bottom of the "Date"

ie

Code:
22/08/2009	Arsenal v Portsmouth	0
18/08/2009	Celtic v Arsenal	             8
15/08/2009	Everton v Arsenal	             0
Game Stats		Match Stats
2009/10 Value	£5m	Games Started (sub)
Total points	87	No. of Rated 7+
Ranking (position)	13	Star Man Awards
Ranking (overall)	120	Goals
No. of NotW +8	0	No. of NotW +2
Previous Dream Team Record Summary [2008/09]		
Game Stats		Match Stats
2008/09 Value	£5m	Games Started (sub)
Total points	139	No. of Rated 7+
Ranking (position)	3	Star Man Awards
Ranking (overall)	32	Goals
No. of NotW +8	0	No. of NotW +2
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,915
Messages
5,834,355
Members
430,279
Latest member
mkasledge1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top