HOW DO I DO A FIND METHOD IN EXCEL VBA

Closed
JAY - Nov 4, 2009 at 07:40 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 5, 2009 at 07:27 PM
Hello,
I am trying to create a macro in excel and I am new to it. I am trying to have it where I search through a excel worksheet and I try to find a word in the sheet. if the word is found I cut the whole row and paste it to a brand new work sheet. then go back and search the original worksheet again and start the whole process over. If it does not find what I am looking for it will go to the next word find search. So far I got it finding the first word i was searching for but when it can not find that word anymore it asks to debug because of an object failure.

The code I used is below

Global X As Long
Global Y As String
Global A As String
Global N As String
Global V As Long

Global Find As Boolean

Sub SORT()
X = 2
Y = X
A = "A"
N = A & Y

'Adds New Workbook
Workbooks.Add

'Window 1 Activated
Windows("TRY1.xls").Activate
Rows("1:1").Select
Selection.Copy

' Activate new Workbook
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Paste

Range("A2").Select
ActiveCell.FormulaR1C1 = "TEST1"
Find = True

Windows("TRY1.xls").Activate

Do While Find = True
Find = Cells.Find(What:="IC", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate


If Find = True Then

Rows(ActiveCell.Row).Select

Selection.Cut

Windows("Book1").Activate

X = X + 1
Y = X
N = A & Y


Range(N).Select

ActiveSheet.Paste

Windows("TRY1.xls").Activate
Range("E1").Select

Else
Find = Cells.Find(What:="LIC", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

If Find = True Then

Rows(ActiveCell.Row).Select

Selection.Cut

Windows("Book1").Activate

X = X + 1
Y = X
N = A & Y

Range(N).Select

ActiveSheet.Paste

Windows("TRY1.xls").Activate
Range("E1").Select

End If
End If
Loop


End Sub

Thank You for any help

JAY

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 5, 2009 at 07:27 PM
put all the unique names in an empty column/range for e.g. S2 down. row 1 is always for heading .
In the main data base the names are in column A from A2 down.A1 is having column heading.

try this macro and CONFIRM WHETHER IT SERVES YOUR PURPOSE

Sub test()
Dim r As Range, r1 As Range, c As Range, x As String
Dim cfind As Range, add As String
On Error Resume Next
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
'this gets the unique name in column A and park it in column S
r.AdvancedFilter xlFilterCopy, copytorange:=Range("s1"), unique:=True
Set r1 = Range(Range("S2"), Range("s2").End(xlDown))
'MsgBox r1.Address
For Each c In r1
x = c.Value
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
add = cfind.Address
cfind.EntireRow.Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Do
Set cfind = r.Cells.FindNext(cfind)
If cfind Is Nothing Or cfind.Address = add Then Exit Do
cfind.EntireRow.Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Loop
Next c

End Sub


1.see the listing of unique names in column S using advanced filter.
2.loop over each name in column S
3.find the name available in column A
4. if found copy row to sheet 2
5.then loop to find same name somewhere else in column A
see help under ADCVANCEDFILTER, FIND,FINDNEXT
Preferably do not use variable names same as object or method names.
Instead of find as variable use "cfin" or "found" etc.
for advanced filter or autofilter column heading is necessary.
6
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 4, 2009 at 07:58 PM
1. How many names are there to be found out. Are they listed in any place in the sheet
2. once you find the first "IC" to find other "IC"s you have to use a do loop with
FINDNEXT. see help on this and the example.


please clarify the no1. above
0
Thank You for the help.

There is multiple names I am trying to find, for instance there is 5 "IC" one day and 10 "IC" the next day in the worksheet. Unfortunetly it is a daily changing worksheet. I need to be able to find all "IC" then when it can not find anymore of "IC" it starts looking for "LIC". The "LIC" keeps changing daily too. That is actually what I am having a problem with. The program I have above will find all the "IC" but then once it finds all and there is none left the program actually goes into fault because of object error.

Thank You
JAY
0