Name Phone Data classificando de linha para coluna usando Macro no Excel 2010

1

eu quero dividir meus dados e organizados em colunas de nome de cidade de estado do telefone n telefone, mas o nome não está juntando todos os dados na mesma linha, você pode pls me ajudar a consertá-lo? A seguir, os códigos macro obrigado

Sub ExtractDataFromTextFile()
  Dim strFilename, strTextLine, tState, tZip, tCity, tAddress, tCityState  As String
  Dim iFile, iRow, ChkName, ChkAddress, ChkPhone As Integer
  Dim SplitAddress, TempAddressSplit As Variant
'ChkScrapLine1, ChkScrapLine2, ChkScrapLine3, ChkScrapLine4, ChkScrapLine5, ChkScrapLine6, ChkScrapLine7, ChkScrapLine8, ChkScrapLine9, ChkScrapLine10

  'text file path
  strFilename = "C:\Users\Wasim\Desktop\N.txt"

  'Set how many rows you want to leave on top of data
  iRow = 1

  iFile = FreeFile
  Open strFilename For Input As #iFile

  Do Until EOF(1)
    Line Input #1, strTextLine

    strTextLine = Application.WorksheetFunction.Clean(strTextLine)
    strTextLine = Application.WorksheetFunction.Trim(strTextLine)


    If Len(strTextLine) > 1 Then

        ChkScrapLine1 = InStr(LCase(strTextLine), "confirm")
        ChkScrapLine2 = InStr(UCase(strTextLine), "SPONSORED")
        ChkScrapLine3 = InStr(LCase(strTextLine), "more")
        ChkScrapLine4 = InStr(LCase(strTextLine), "background")
        ChkScrapLine5 = InStr(LCase(strTextLine), "find")
        ChkScrapLine6 = InStr(UCase(strTextLine), "TRY")
        ChkScrapLine7 = InStr(UCase(strTextLine), "get")
        ChkScrapLine8 = InStr(LCase(strTextLine), "listing")
        ChkScrapLine9 = InStr(LCase(strTextLine), "search")
        If ChkScrapLine1 = 0 And ChkScrapLine2 = 0 And ChkScrapLine3 = 0 And ChkScrapLine4 = 0 And ChkScrapLine5 = 0 And ChkScrapLine6 = 0 And ChkScrapLine7 = 0 And ChkScrapLine8 = 0 And ChkScrapLine9 = 0 Then
            ChkAddress = InStr(strTextLine, ",")
            ChkPhone = InStr(strTextLine, "(")
            If ChkAddress > 0 Then
strTextLine = Replace(strTextLine, ", ", ",")
                SplitAddress = Split(strTextLine, ",")
                tAddress = SplitAddress(0)
                tCity = SplitAddress(1)

                Cells(iRow, 3).Value = strTextLine

            ElseIf ChkPhone > 0 Then
                Cells(iRow, 4).Value = strTextLine
            Else
                iRow = iRow + 1
                Cells(iRow, 1).Value = strTextLine
            End If

        End If
    End If
  Loop
  Close #iFile

End Sub
    
por Wasi Ch 20.10.2016 / 09:22

0 respostas