HOW DO I DO A FIND METHOD IN EXCEL VBA [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
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
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!