Comparing column cell contents to a text file and copying matching rows to another workbook VBA

Question

I have no idea what I am doing and if you feel like yelling at me that's cool.

I am wondering how I would go about checking values of each cell in column D on a worksheet called PriceList against values in a text file ItemNumber.txt.

If the content of the cells in the column is equal to one of the values in said text file I want it to copy the row and paste it into sheet1....

 Option Explicit



 Sub CompareValue()

 Dim FileNum As Integer
 Dim DataLine As String
 Dim cel As Range
 Dim celString As String



' Select file to be opened
    FileNum = FreeFile()
    Open "C:\Users\jreinhold\Documents\ItemNumbers.txt" For Input As #FileNum



     Set myRange = Range("D:D")


           For i = 1 To myRange.Rows.Count 'loop through rows by using i as a cell reference
            Do While Not EOF(FileNum) 'run input from file while not end of file
            Line Input #FileNum, DataLine   'input line data into DataLine


            ' Check value of cell against read in data
            If InStr(DataLine, myRange.Cells("D", i).Value) = 0 Then 'compare DataLine to cell i
                ' Copy Row Where match resides
                 DataLine = DataLine + 1 'if value of comparison is 0 add 1 to data line and get next line in text file

            Loop 'Loop back around and plus next line for the data from the file in and check values against cell i again
            End If 'end If once value for comparison is true
             Source.Rows(c.Row).Copy Target.Rows(i) ' Copy row
             Sheets("Sheet1").Paste  ' Paste row into Sheet1
               i = i + 1            ' add 1 to i in order to continue to next cell in column
         Next i 'check next cell for the data inputs using the same code.



Wend

End Sub

Welcome to SO! Please read through [How to Ask](http://stackoverflow.com/help/how-to-ask) for suggestions on how to ask a question, and what we expect. That being said, what you'll want to do is search for a few methods, and combine: probably start with "Check text file for string VBA", "copy from text file to Excel VBA". There are actually a few ways to accomplish your task, but we like to see some effort on your part. Give it a whirl, and let us know what's working and what's not for you. — BruceWayne, Apr 18 '16 at 22:55
Thank you for the suggestion. I have tried to store the item numbers in an array and had disasterous results. — Jeremy Reinhold, Apr 18 '16 at 22:57
Perhaps even open Excel, start the macro recorder, then open your Text Files from Excel, do your search (CTRL+F probably), when found, do your copying/pasting. Then stop the recorder, take a look at the code. It'll be riddled with `.Select` statements, so check out [How to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to cut it down to some more manageable code. If you have **any** code, or attempts, please edit into your OP so we can guide you. — BruceWayne, Apr 18 '16 at 22:57
Okay, so I tried to read the value in from the file and while that value was read in I checked it versus all cells in the column. I do not think this is the right way, I think I need to cell by cell check all values in the test file before moving on to the next cell. However I get turned around. I have never programmed VBA, only C++, Java and HTML. I apologize for sucking :( — Jeremy Reinhold, Apr 19 '16 at 00:02
(If you know those languages, you know you don't suck, you just aren't familiar with VBA...yet!) What does your text file look like, is it a CSV, or just rows of info? If you can show some sample data that's in your text doc, I can guide you some more. You described a loop which is what you'll want. — BruceWayne, Apr 19 '16 at 00:06
They are all arranged line by line, 1 string per line, no commas, in a plain text file. There is no other data except the item numbers I want to check for. — Jeremy Reinhold, Apr 19 '16 at 00:13
Instead of `If myRange.Cells(i, j).Value = DataLine Then`, read up on the function `InStr`. You may end up with something like `If InStr(DataLine, myRange.Cells(i,j).Value) > 0 Then`. Also, since you are searching through a single column, you really don't need the loop with the `j` counter - you only need to loop through the rows. — OldUgly, Apr 19 '16 at 06:52
Although I indicated you could remove the loop using `j` as a counter, you also have a line where you increment `j` that is inside that loop - that's not good practice. You use `j` to as a pointer into Target.Rows. I don't see Target defined anywhere. Consider using `Option Explicit` at the top of your module, and ensuring all of your variables are declared and set. — OldUgly, Apr 19 '16 at 06:58

score 0 · Accepted Answer · answered Apr 19 '16 at 17:19

Try this:

Sub CompareValue()
Dim mainWS As Worksheet, dataWS As Worksheet, txtWS As Worksheet
  Dim FileNum&, i&, j&
  Dim DataLine As String, celString$
  Dim cel As Range, myRange As Range
  Dim ranOnce As Boolean

  ranOnce = False ' Check if we've added a line to your new sheet


  Dim fileName$, filePath$, fullFile$
  filePath = "C:\Users\bWayne\"
  fileName = "myTextDoc.txt"
  fullFile = filePath & fileName

    Set dataWS = Sheets("Data") ' Rename this, this sheet has your column D with the values to check
    Set mainWS = Sheets("Sheet1") ' This is where the row from DATA will be copied to, if a match is found in the text file.

    ' This will call a sub that will put the text into the temp sheet
    TextFile_PullData fullFile, mainWS
    Set txtWS = Sheets(Left(fileName, WorksheetFunction.Search(".", fileName) - 1))

' Now we have the text file informaiton in a sheet. So just loop through the cells in "Data" and check if there's a match in the text
Dim lastRow&
lastRow = dataWS.Cells(dataWS.Rows.Count, 4).End(xlUp).Row
Set myRange = dataWS.Range("D1:D" & lastRow) ' edit this as necessary
For Each cel In myRange
    If WorksheetFunction.CountIf(txtWS.Range("A1:A" & txtWS.UsedRange.Rows.Count), cel.Value) > 0 Then
        ' Since we found a match, copy the entire row to "Sheet1"
        Dim newLastRow&
        newLastRow = mainWS.Cells(mainWS.Rows.Count, 4).End(xlUp).Row

        If ranOnce Then newLastRow = newLastRow + 1
        ranOnce = True
        mainWS.Rows(newLastRow).EntireRow.Value = cel.EntireRow.Value
    End If
Next cel

End Sub
Sub TextFile_PullData(fileName As String, mySheet As Worksheet)

Workbooks.OpenText fileName:=fileName, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

ActiveSheet.Copy after:=mySheet

End Sub

Instead of going line by line, I just imported the Text file into Excel, and am just doing a CountIf() to see if there's a match. If so, copy that row to your new sheet. Please note you will probably want to change the Sheets, as it's not clear to me where you want the data to go. This should help get you going though! I recommend stepping through with F8 just to make sure it works.

Edit: You had some loops in there that I may have not considered, so let me know if I'm missing something.

Thanks Bruce, I apologize for not really being able to grasp this much. — Jeremy Reinhold, Apr 19 '16 at 18:52
@JeremyReinhold - No worries, does the macro work/make sense? I can explain more if it's not quite doing what you need it to. — BruceWayne, Apr 19 '16 at 18:58
When I get a chance to look it over and play with it Ill get back to you. Thanks for the help sir! — Jeremy Reinhold, Apr 19 '16 at 19:03
I get "Excel cannot insert the sheets into the destination workbook, because it contains fewers rows and columns than the source workbook. Run-time error 1004, I have tried to resize the document and edit the ranges within the VBA code you gave me.. I got it to work once. But after that I haven't been able to. I go into debug mode and it says the issue is with "ActiveSheet.Copy after:=mySheet" — Jeremy Reinhold, Apr 20 '16 at 00:26
I wanted to say thanks BruceWayne. The issue I was having was due to the output worksheet not being pasted in the right position. I replaced the ActiveSheet.Copy after:=mySheet with mySheet.Copy After:=Sheets(Sheets.Count) and it works like a charm now isolating only the item numbers... You are awesome, and I apologize if I didn't seem interested in trying to learn. I work a fulltime, goto school fulltime and am running a business on the side. I'm just really busy and you saved me a great deal of frustration. For that you have my eternal gratitude — Jeremy Reinhold, Apr 21 '16 at 00:54
@JeremyReinhold - Ah, sorry I didn't get to this before, but I'm glad you were able to see what change was needed! If this works as the answer, do you mind marking it as such (click the check mark left of the post)? — BruceWayne, Apr 21 '16 at 13:57

Comparing column cell contents to a text file and copying matching rows to another workbook VBA

1 Answers1