?
Solved

Find all data from text file and delete the whole row which is not found

Posted on 2007-07-30
4
Medium Priority
?
248 Views
Last Modified: 2010-03-05
Hi,

I want a macro to search all the machine names which i have in a txt file and cut paste anything else which is not there in the sheet to a different sheet.
In the text file i have
Machine1
Machine2
In the excel i have
Machine1
machine2
Machine3
What i need is machine 3 should be cut pasted to a new sheet

Regards
Sharath
0
Comment
Question by:bsharath
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590903
Sub Macro1()
Dim FF As Integer, str1 As String, j as Integer, idfound as Boolean
s2row=1
j=1
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = false
       While Not EOF(FF)
           Line Input #FF, str1
           If  UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = true
            End If
        Wend
       if idfound = false then
             Sheet1.Rows(i).Cut Destination=Sheet2.Rows(s2row)
            s2row=s2row+1
       End if
 Next
Close FF
End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590913
I get this.

Run time error '13'
0
 
LVL 13

Accepted Solution

by:
hiteshgoldeneye earned 2000 total points
ID: 19590935
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 1
j = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = False
      FF = FreeFile
      Open "C:\filename.txt" For Input As #FF

       While Not EOF(FF)
           Line Input #FF, str1
           If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = True
            End If
        Wend
        Close FF
       If idfound = False Then
             Sheet1.Rows(i).Cut Destination:=Sheet2.Rows(s2row)
            s2row = s2row + 1
       End If
 Next

End Sub


0
 
LVL 11

Author Comment

by:bsharath
ID: 19614743
Hitesh small dout in this.What will this macro do.

Where should i have the search data in the excel.Which colum
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Understanding the various editions available is vital when you decide to purchase Windows Server 2012. You need to have a basic understanding of the features and limitations in each edition in order to make a well-informed decision that best suits …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question