Extracting a text string Problem

[fari1]

my below code is perfect of extracting my another text string, but when i modify it to extract my another text string, it is not giving me results, trying real hard but unable to find the problem.

the text string to find is

BIK=0000055135

out of this line

<td bgcolor="#E6E6E6" valign="top" align="left"><a href="/chi-kin/browse-gar?action=getcompany&BIK=0000055135&owner=include&count=100">KELLY SERVICES INC (0000055135) (Filer)</a></td>

Sub GetCIK()
  Sheets("wquery").Select
  Dim X As Long, LastRow As Long, OutputRow As Long, CellContent As String
  Const StartRow As Long = 1
  Const DataCol As String = "A"
  Const OutputSheet As String = "URLs"
  Const OutputCol As String = "B"
  LastRow = Sheets("wquery").Cells(Rows.Count, DataCol).End(xlUp).Row
  OutputRow = Worksheets(OutputSheet).Cells(Rows.Count, OutputCol).End(xlUp).Row
  For X = StartRow To LastRow
    If LCase(Sheets("wquery").Cells(X, DataCol).Value) Like "*getcompany&""CIK*&*" Then
      OutputRow = OutputRow + 1
      CellContent = Sheets("wquery").Cells(X, DataCol).Value
      Worksheets(OutputSheet).Cells(OutputRow, OutputCol).Value = Split(Mid(CellContent, InStr(1, CellContent, "getcompany&""CIK", vbTextCompare) + 15), """amp")(0)
    End If
  Next

 

[kpark91]

Re: Help with the code

it worked on your side? its not giving me error now, but not giving any results either, i changed my outputcol2 to "B"

Yes it has worked on my side.
Send me your worksheet or the code you're using and I might be able to point out the problem.

it doens't have to be the real data you use on but it will certainly help since there may be many exceptions in the real data that I have not thought of...

 

[fari1]

i've emailed u

 

[fari1]

Hi kevin, waiting for the response

 

[kpark91]

What seems to be the problem?

I've run the code posted previously onto your workbook by renaming the datasheet "wquery"
and destination sheet "Filings" (both without the quotations).

I will send you the email with the attachment.

 

[fari1]

why the output rows are starting from nineth row, why not from the 2nd row?

 

[kpark91]

It's because ... the HTML tag was removed from the code when posting..

[EDIT: here is the code you can use]

Sub GetType()
    Dim X As Long, LastRow1 As Long, CellContent As String, lastRow&
    Dim pos1&, pos2&, OutputRow2&
 
    Const StartRow As Long = 1
    Const DataCol As String = "A"
    Const OutputSheet As String = "Filings"
    Const OutputCol2 As String = "C"
 
    lastRow = Sheets("wquery").Cells(Rows.Count, DataCol).End(xlUp).Row
    OutputRow2 = Worksheets(OutputSheet).Cells(Rows.Count, OutputCol2).End(xlUp).Row
 
    For X = StartRow To lastRow
        If Sheets("wquery").Cells(X, DataCol).Value Like "*""nowrap*</td>*" Then
            OutputRow2 = OutputRow2 + 1
            CellContent = Sheets("wquery").Cells(X, DataCol).Value
            pos1 = InStr(1, CellContent, """nowrap""")
            pos2 = InStr(1, CellContent, "</td>")
            If pos1 = 0 Or pos2 = 0 Then GoTo continue
            Worksheets(OutputSheet).Cells(OutputRow2, OutputCol2).Value = Mid(CellContent, pos1 + 9, pos2 - pos1 - 9)
        End If
continue:
    Next X
End Sub

 

[fari1]

i mean in my fillings sheets, i want the output to start from 2nd row, can't it done to start bring data in output sheet from 2nd row?

 

[fari1]

yes its done, thank u

 

[kpark91]

You're welcome
It only took us 68 posts + 3 emails lol.

 

[fari1]

yeps, but having another problem now, posted here
http://www.mrexcel.com/forum/showthread.php?p=2823895&posted=1#post2823895

if u could help