Newbee needs HELP-Small Macro

[tbobo]

Col-1 Col-2 Col-3 Col-4
1 6 a b
1 6 a b
1 5 a b
2 7 a b
3 3 a b
3 9 a b
4 6 a b
4 4 a b
4 3 a b
5 3 a b
6 8 a b
7 8 a b
8 8 a b
8 8 a b
9 4 a b
9 4 a b
10 4 a b

I am interested to create a macro that we allow me to retrieve the data 1,4,and 8 from Col-1 and add the fifth Col with with "I want". My ouput result should be :

Col-1 Col-2 Col-3 Col-4 Col-5
1 6 a b I want
1 6 a b I want
1 5 a b I want
4 6 a b I want
4 4 a b I want
4 3 a b I want
8 8 a b I want
8 8 a b I want


My data file contains hundred of lines. I need to retrive data by user-define in this case it is 1,4,8!

Many thanks!

[Rich Mcc]

dont need a macro , in col 5 put

=IF(OR(A1=1,A1=4,A1=8),"I want","")

paste it down all of col 5

Rich


"tbobo" wrote:

>
> Col-1 Col-2 Col-3 Col-4
> 1 6 a b
> 1 6 a b
> 1 5 a b
> 2 7 a b
> 3 3 a b
> 3 9 a b
> 4 6 a b
> 4 4 a b
> 4 3 a b
> 5 3 a b
> 6 8 a b
> 7 8 a b
> 8 8 a b
> 8 8 a b
> 9 4 a b
> 9 4 a b
> 10 4 a b
>
> I am interested to create a macro that we allow me to retrieve the data
> 1,4,and 8 from Col-1 and add the fifth Col with with "I want". My ouput
> result should be :
>
> Col-1 Col-2 Col-3 Col-4 Col-5
> 1 6 a b I want
> 1 6 a b I want
> 1 5 a b I want
> 4 6 a b I want
> 4 4 a b I want
> 4 3 a b I want
> 8 8 a b I want
> 8 8 a b I want
>
>
> My data file contains hundred of lines. I need to retrive data by
> user-define in this case it is 1,4,8!
>
> Many thanks!
>
>
> --
> tbobo
> ------------------------------------------------------------------------
> tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
> View this thread: http://www.excelforum.com/showthread...hreadid=519777
>
>

[Toppers]

Hi,
A macro solution:

Enter data as 1,4,8


Dim lastrow As Long, r As Long, i As Integer
Dim v As Variant
Dim ws1 As Worksheet, ws2 As Worksheet

Myvals = Application.InputBox("Enter selection criteria (n,n,n)", Type:=2)
If Myvals = o Then Exit Sub

v = Split(Myvals, ",")
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

With ws1
For r = 1 To .Cells(Rows.Count, "a").End(xlUp).Row
For i = LBound(v) To UBound(v)
If .Cells(r, 1) = CInt(v(i)) Then
.Rows(r).Copy ws2.Cells(Rows.Count, "a").End(xlUp)(2)
ws2.Cells(ws2.Cells(Rows.Count, "a").End(xlUp).Row, 5) = "I
want"
Exit For
End If
Next i
Next r
End With
End Sub


"Rich Mcc" wrote:

> dont need a macro , in col 5 put
>
> =IF(OR(A1=1,A1=4,A1=8),"I want","")
>
> paste it down all of col 5
>
> Rich
>
>
> "tbobo" wrote:
>
> >
> > Col-1 Col-2 Col-3 Col-4
> > 1 6 a b
> > 1 6 a b
> > 1 5 a b
> > 2 7 a b
> > 3 3 a b
> > 3 9 a b
> > 4 6 a b
> > 4 4 a b
> > 4 3 a b
> > 5 3 a b
> > 6 8 a b
> > 7 8 a b
> > 8 8 a b
> > 8 8 a b
> > 9 4 a b
> > 9 4 a b
> > 10 4 a b
> >
> > I am interested to create a macro that we allow me to retrieve the data
> > 1,4,and 8 from Col-1 and add the fifth Col with with "I want". My ouput
> > result should be :
> >
> > Col-1 Col-2 Col-3 Col-4 Col-5
> > 1 6 a b I want
> > 1 6 a b I want
> > 1 5 a b I want
> > 4 6 a b I want
> > 4 4 a b I want
> > 4 3 a b I want
> > 8 8 a b I want
> > 8 8 a b I want
> >
> >
> > My data file contains hundred of lines. I need to retrive data by
> > user-define in this case it is 1,4,8!
> >
> > Many thanks!
> >
> >
> > --
> > tbobo
> > ------------------------------------------------------------------------
> > tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
> > View this thread: http://www.excelforum.com/showthread...hreadid=519777
> >
> >

[tbobo]

Let's say I enter 1,4,8. I have the data follow: "it starts with row 2 and row 1 empty."

How can I have row 1 has the colum header to be displayed witht the data below:

1 6 a b I want
1 6 a b I want
1 5 a b I want
4 6 a b I want
4 4 a b I want
4 3 a b I want
8 8 a b I want
8 8 a b I want

It works great! Thank you much for the quick reply. I need the column header for row 1. Please help!

[Toppers]

Sub a()
Dim lastrow As Long, r As Long, i As Integer
Dim v As Variant
Dim ws1 As Worksheet, ws2 As Worksheet

Myvals = Application.InputBox("Enter selection criteria (n,n,n)", Type:=2)
If Myvals = o Then Exit Sub

v = Split(Myvals, ",")
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
' Change array below if you want different headings .....
ws2.Cells(1, 1).Resize(1, 5) = Array("Col-1", "Col-2", "Col-3", "Col-4",
"Col-5")
With ws1
For r = 2 To .Cells(Rows.Count, "a").End(xlUp).Row
For i = LBound(v) To UBound(v)
If .Cells(r, 1) = CInt(v(i)) Then
.Rows(r).Copy ws2.Cells(Rows.Count, "a").End(xlUp)(2)
ws2.Cells(ws2.Cells(Rows.Count, "a").End(xlUp).Row, 5) = "I
want"
Exit For
End If
Next i
Next r
End With
End Sub

"tbobo" wrote:

>
> Let's say I enter 1,4,8. I have the data follow: "it starts with row 2
> and row 1 empty."
>
> How can I have row 1 has the colum header to be displayed witht the
> data below:
>
> 1 6 a b I want
> 1 6 a b I want
> 1 5 a b I want
> 4 6 a b I want
> 4 4 a b I want
> 4 3 a b I want
> 8 8 a b I want
> 8 8 a b I want
>
> It works great! Thank you much for the quick reply. I need the column
> header for row 1. Please help!
>
>
> --
> tbobo
> ------------------------------------------------------------------------
> tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
> View this thread: http://www.excelforum.com/showthread...hreadid=519777
>
>