Extract Specific Data frm wkbk (what's wrong w/ my vba?)

[sisieko]

I've been struggling with this all day, and im at my fustration limit now.
I am trying to pick up specific data from the following spreadsheet, I have imported from a text file.
What's wrong with my vba? And why does my result sheet, come out blank?

Please see code i got from 1wek4dep for a different task, but im trying to apply it to this case. I have attached the spreedsheet.

Clicking on the second button should trigger wkscmd_ExtractData_Click().
Thank you.

Im trying to get the data from "Global House Count:" rows.
I want result to look like Sheet1.

Private Sub wkscmd_ExtractData_Click()

'   Local Variables
    Dim cell As Range, rngOut As Range
    Dim strDate As String, strTable As String
    Dim strPreAGG As String, strPostAGG As String, strCompression As String
    Dim strRenovated As String, strRenopercent As String

'   Read data
    For Each cell In Me.Range("rdi_TableTop", Me.Range("rdi_TableTop").End(xlDown))
        If ActiveSheet.Name = Me.Name Then cell.Select
    ' Get effective date
        If InStr(cell, "SUMMARY METRICS:") > 0 Then
            If strDate = "" Or strDate <> Right(cell, 10) Then strDate = Right(cell, 10)
            'strDate = Format(strDate, "m/d/yyyy")
        End If
'    ' Get Pre-Aggregation
'        If InStr(cell, "GLOBAL HOUSE COUNT:") > 0 Then
'            If strPreAGG = "" Or strPreAGG <> Trim(Mid(cell, 22, 13)) Then strPreAGG = Trim(Mid(cell, 22, 13))
'        End If
'    ' Get Post-Aggregation
'        If InStr(cell, "GLOBAL HOUSE COUNT:") > 0 Then
'            If strPostAGG = "" Or strPostAGG <> Trim(Mid(cell, 35, 14)) Then strPostAGG = Trim(Mid(cell, 35, 14))
'        End If
'    ' Get Compression
'        If InStr(cell, "GLOBAL HOUSE COUNT:") > 0 Then
'            If strPreAGG = "" Or strPostAGG <> Trim(Mid(cell, 35, 14)) Then strPostAGG = Trim(Mid(cell, 35, 14))
'        End If

    ' Get Global House Count:
        If InStr(cell, "GLOBAL HOUSE COUNT:") > 0 Then
            'If strPreAGG = "" Or strPreAGG <> Trim(Mid(cell, 22, 13)) Then
            strPreAGG = Trim(Mid(cell, 22, 13))
            strRenovated = Trim(Mid(cell, 38, 12))
            strRenopercent = Trim(Mid(cell, 52, 4))
            strPostAGG = Trim(Mid(cell, 59, 11))
            strCompression = Trim(Right(cell, 4))
        If InStr(cell, "TOTAL") = 0 Then
            cell.Select
            If IsNumeric(strPreAGG) And IsNumeric(strPostAGG) Then
                Set rngOut = Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)
                rngOut.Offset(0, 0) = strTable
                rngOut.Offset(0, 1) = strDate
                rngOut.Offset(0, 2) = strPreAGG
                rngOut.Offset(0, 3) = strPostAGG
                rngOut.Offset(0, 4) = strRenovated
                rngOut.Offset(0, 5) = strRenopercent
            End If
        End If
    End If
    Next cell
    
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ThisWorkbook.PATH & "\UsageReport-" & Format(Date, "mmmyy")
    ActiveSheet.Name = "UsageReportData"
    Application.DisplayAlerts = True
    
    Windows("ToBeExtractedFrom.xls").Activate 'Go back to rawdata workbook
    ActiveWorkbook.Close SaveChanges:=False

End Sub

 

[sisieko]

I guess ican't attach files here

 

[xlbo]

have you tried stepping through the code line by line using F8 ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question