HOW DO I DO A FIND METHOD IN EXCEL VBA
Closed
JAY
-
Nov 4, 2009 at 07:40 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 5, 2009 at 07:27 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 5, 2009 at 07:27 PM
Related:
- HOW DO I DO A FIND METHOD IN EXCEL VBA
- Number to words in excel formula without vba - Guide
- Vba case like - Guide
- How to open vba in excel mac - Guide
- How do i change my payment method on netflix - Guide
- How to take screenshot in excel - Guide
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 5, 2009 at 07:27 PM
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
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.
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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 4, 2009 at 07:58 PM
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
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
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