Solved How can I get macro for this

May 26, 2015 at 22:35:47
Specs: Windows XP
In sheet1 I have range of numbers from E1:E3000 (4 digit cabin numbers). In column A I have name and in Column B I have last name. So I want to make a search for cabin numbers and if search finds that to copy whole row to sheet2. Can somebody help me with that because I tried many things but still struggling with result.
Query is for Microsoft excel

message edited by worker12


See More: How can I get macro for this

Report •


✔ Best Answer
May 27, 2015 at 18:05:53
I find it very interesting that not one of the Cabin Numbers shown in the list that you posted in Response #6 can be found in the example data that you posted in Response #4. That makes it kind of difficult to test the code against your example data since not one single cabin number will be found. That forces me to make up my own data for testing. Don't blame me if the code doesn't work for your data, since your example was unusable as posted.

With that said, try this code:

Sub Cabin_Fever()
'Build Array with cabin numbers
 cab_Array = Array(1030, 1032, 1034, 1036, 1048, 1050, 1052, 1058, _
                1060, 1530, 1532, 1534, 1536, 1550, 1552, 1554, _
                1556, 1560, 1562, 1568, 1600, 9656, 8668, 7672)
                
'Loop through each element in Array
  For cab_Num = 0 To UBound(cab_Array)
   With Range("E1:E3000")
    Set cab = .Find(cab_Array(cab_Num))
'If a Cabin number is found, copy Row from Sheet 1 to next empty row on Sheet 2
     If Not cab Is Nothing Then
        firstAddress = cab.Address
        Do
            nxt_Sht2Rw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
             Sheets(1).Rows(cab.Row).Copy _
              Destination:=Sheets(2).Cells(nxt_Sht2Rw, 1)
            Set cab = .FindNext(cab)
        Loop While Not cab Is Nothing And cab.Address <> firstAddress
    End If
   End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 27, 2015 at 04:09:07
The macro part is fairly easy, however the first question is: How do you want to perform the search?

Do you want a pop up input box so you can enter a single cabin number? Do you want enter a cabin number in a cell and then run the macro? Do you want enter a cabin number in a cell and then have the macro run automatically? Do you have a list of cabin numbers in another column that you want to search for in bulk? Something else?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
May 27, 2015 at 08:21:19
I have data already. I have first name, last name, cabin number etc. because every 7 days I get new entries. So when I paste data into excel (always same format and same order) I need rows where are those certain cabin numbers to be copied into another sheet. I have around 3000 entries and around 20 cabin numbers I need to extract ( always same cabin number). So lets say I need people from cabins 1252, 3254, 3656..(complete row with names, last names..) from sheet1 to be extracted into Sheet2. So as soon as I copy new data into sheet1 macro will extract this rows into sheet2

message edited by worker12


Report •

#3
May 27, 2015 at 11:03:40
You haven't really answered my question. In order for the macro to find and copy the rows, it needs to know where to find the data that it is going to search for.

In other words, we know that you have cabin numbers in E1:E3000, so we know where to find the list that will be searched through. However, we also need to tell the VBA code what to search for.

In other, other words, you said "So lets say I need people from cabins 1252, 3254, 3656...". Now we need to tell the VBA code where to find that list (1252, 3254, 3656) so that it can search E1:E3000 for those exact values and copy the found rows.

One more question: Let's say we are searching for 1252. Will there be more than one occurrence of 1252 in E1:E3000? The reason I ask is that we have to tell the VBA code to either keep searching until it has found all occurrence of 1252 or that it can stop searching as soon as it finds a single occurrence of 1252.

Keep in mind that we can't see your worksheet from where we are sitting, so you need to supply enough details so that we know what we are working with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 27, 2015 at 11:37:51
So this is how my sheet looks like
Sheet1

A ------- B --------- C ---- D - E
ABEEL CYNTHIA 08/10/1946 F 8636
ABELLA MARISSA 08/30/1956 F 7583
ABELLA MELISSA 04/23/1989 F 7583
ABELLA RALPH 07/15/1956 F 7583
AGARWAL KHUSHBOO 05/12/1993 F 3532
AGARWAL MANOJ 11/24/1967 M 3532
AGARWAL MUKUND 05/15/1943 M 9002
AGARWAL RITA 03/30/1972 F 3532
AGARWAL ROHAN 04/14/2007 M 3534
AGARWAL ROHIT 12/12/1975 M 3534
AGARWAL SANJITH 03/23/2010 M 3534
AGUILAR USTARAN ROCIO 06/22/1960 F 9018
AKE RONDA 06/05/1956 F 7652
ALAPPAT KURIAN 12/19/1952 M 3072
ALCURI CAMPOS SERGIO 10/01/1955 M 4056
ALI AMIR 12/31/1953 M 1592
ALLAN RASEM 11/13/1971 M 4557
ALLAN TAREQ 04/08/2001 M 4559

Sheet2

AGARWAL KHUSHBOO 05/12/1993 F 3532
AGARWAL MANOJ 11/24/1967 M 3532
AGARWAL RITA 03/30/1972 F 3532
AGARWAL ROHAN 04/14/2007 M 3534
AGARWAL ROHIT 12/12/1975 M 3534
AGARWAL SANJITH 03/23/2010 M 3534

This first list is in sheet 1. Now I need people (whole row) from this sheet1 which are in cabin 3532,3534 to be copied into sheet2 which is empty(it should look like list I made you for sheet2). Maximum rows that can be copied is around 70 ( its 20 cabins with capacity of 3 max 4 persons). I hope its more specified now and that you understand what I want to say.

message edited by worker12


Report •

#5
May 27, 2015 at 11:57:48
I'll ask one more time and then I'll have to give up.

You still have not told me how the macro will know to search for 3532, 3534 or any other number.

VBA code cannot read minds. It needs to be specifically told what to search for, either by referencing a list in a range of cells or by getting the input from the user or by having it hardcoded into the macro.

I completely understand that you want to copy rows from Sheet 1 to Sheet 2

What I do not know is this:

How will the macro know that you want to search for 3532?

Until that question is answered, there is nothing else that we can do for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 27, 2015 at 12:14:56
Sorry, now I understand what you mean. I would like to have them hardcoded into macro. These are cabin numbers and those numbers are always the same. We can make a button so when I copy new list just to press button and then have listed names by this numbers below. So this numbers will be inside of macro always.
1030
1032
1034
1036
1048
1050
1052
1058
1060
1530
1532
1534
1536
1550
1552
1554
1556
1560
1562
1568
1600
9656
8668
7672

I hope this is answer you need :/


Report •

#7
May 27, 2015 at 18:05:53
✔ Best Answer
I find it very interesting that not one of the Cabin Numbers shown in the list that you posted in Response #6 can be found in the example data that you posted in Response #4. That makes it kind of difficult to test the code against your example data since not one single cabin number will be found. That forces me to make up my own data for testing. Don't blame me if the code doesn't work for your data, since your example was unusable as posted.

With that said, try this code:

Sub Cabin_Fever()
'Build Array with cabin numbers
 cab_Array = Array(1030, 1032, 1034, 1036, 1048, 1050, 1052, 1058, _
                1060, 1530, 1532, 1534, 1536, 1550, 1552, 1554, _
                1556, 1560, 1562, 1568, 1600, 9656, 8668, 7672)
                
'Loop through each element in Array
  For cab_Num = 0 To UBound(cab_Array)
   With Range("E1:E3000")
    Set cab = .Find(cab_Array(cab_Num))
'If a Cabin number is found, copy Row from Sheet 1 to next empty row on Sheet 2
     If Not cab Is Nothing Then
        firstAddress = cab.Address
        Do
            nxt_Sht2Rw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
             Sheets(1).Rows(cab.Row).Copy _
              Destination:=Sheets(2).Cells(nxt_Sht2Rw, 1)
            Set cab = .FindNext(cab)
        Loop While Not cab Is Nothing And cab.Address <> firstAddress
    End If
   End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question