How to use comma separated inputs in macro
 

Hello

I have strange requirement.
I have a column with hundreds of entries in it. There is one other columns
associated eith it. For an example consider that following are the entries in
column A and B

Column A Column B

PI03590 bar
ZT05693 %
PIC098734 N
TT09823 C

Now I have to provide one userform input box to user. The user will enter
entries separated by comma (or any other character). I have to filter entres
in column B as per these inputs.
e.g. If user enters (bar, N) in the input box, then macro should filter
entries in column B and copy "unmatched" result to new sheet. In this case,
Macro should copy following information in new sheet:


ZT05693 %
TT09823 C.

(I do not want the user to manually filter it in the sheet as this needs to
be incorporated in the already existing macro which performs several actions
on the sheet.)

Thank You in advance

How to use comma separated inputs in macro
 

I think this macro will do what you want, just assign the appropriate sheet
names to the two Const (constant) statements (and rename the macro to
something more appropriate)...

Sub CopyNonSpecifiedColumnBItemRows()
Dim UserInput As String, UserList() As String, Cell As Range
Dim SourceLastRow As Long, DestinationLastRow As Long
Const Source As String = "Sheet4"
Const Destination As String = "NewSheet"
SourceLastRow = Worksheets(Source).Cells(Rows.Count, "B").End(xlUp).Row
DestinationLastRow = Worksheets(Destination).Cells( _
Rows.Count, "B").End(xlUp).Row
UserInput = InputBox("List items separated by commas", "Get User List")
If Len(UserInput) Then
UserInput = "," & Replace(UserInput, " ", "") & ","
For Each Cell In Worksheets(Source).Range("B1:B" & SourceLastRow)
If InStr(1, UserInput, "," & Cell.Value & ",", vbTextCompare) = 0 Then
Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")
DestinationLastRow = DestinationLastRow + 1
End If
Next
End If
End Sub

--
Rick (MVP - Excel)


"Pawan" wrote in message
...
Hello

I have strange requirement.
I have a column with hundreds of entries in it. There is one other columns
associated eith it. For an example consider that following are the entries
in
column A and B

Column A Column B

PI03590 bar
ZT05693 %
PIC098734 N
TT09823 C

Now I have to provide one userform input box to user. The user will enter
entries separated by comma (or any other character). I have to filter
entres
in column B as per these inputs.
e.g. If user enters (bar, N) in the input box, then macro should filter
entries in column B and copy "unmatched" result to new sheet. In this
case,
Macro should copy following information in new sheet:


ZT05693 %
TT09823 C.

(I do not want the user to manually filter it in the sheet as this needs
to
be incorporated in the already existing macro which performs several
actions
on the sheet.)

Thank You in advance

How to use comma separated inputs in macro
 

Thanks Rick.

However I am getting error at the line

Worksheets(Destination).Cells(DestinationLastRow, "A")



"Rick Rothstein" wrote:

I think this macro will do what you want, just assign the appropriate sheet
names to the two Const (constant) statements (and rename the macro to
something more appropriate)...

Sub CopyNonSpecifiedColumnBItemRows()
Dim UserInput As String, UserList() As String, Cell As Range
Dim SourceLastRow As Long, DestinationLastRow As Long
Const Source As String = "Sheet4"
Const Destination As String = "NewSheet"
SourceLastRow = Worksheets(Source).Cells(Rows.Count, "B").End(xlUp).Row
DestinationLastRow = Worksheets(Destination).Cells( _
Rows.Count, "B").End(xlUp).Row
UserInput = InputBox("List items separated by commas", "Get User List")
If Len(UserInput) Then
UserInput = "," & Replace(UserInput, " ", "") & ","
For Each Cell In Worksheets(Source).Range("B1:B" & SourceLastRow)
If InStr(1, UserInput, "," & Cell.Value & ",", vbTextCompare) = 0 Then
Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")
DestinationLastRow = DestinationLastRow + 1
End If
Next
End If
End Sub

--
Rick (MVP - Excel)


"Pawan" wrote in message
...
Hello

I have strange requirement.
I have a column with hundreds of entries in it. There is one other columns
associated eith it. For an example consider that following are the entries
in
column A and B

Column A Column B

PI03590 bar
ZT05693 %
PIC098734 N
TT09823 C

Now I have to provide one userform input box to user. The user will enter
entries separated by comma (or any other character). I have to filter
entres
in column B as per these inputs.
e.g. If user enters (bar, N) in the input box, then macro should filter
entries in column B and copy "unmatched" result to new sheet. In this
case,
Macro should copy following information in new sheet:


ZT05693 %
TT09823 C.

(I do not want the user to manually filter it in the sheet as this needs
to
be incorporated in the already existing macro which performs several
actions
on the sheet.)

Thank You in advance


.

How to use comma separated inputs in macro
 

It looks like the newsreader word-wrapped that line from the line above it.
What you are seeing as this...

Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")

should all be on one line with a space character between them, like this...

Cell.EntireRow.Copy Worksheets(Destination).Cells(DestinationLastRow, "A")

--
Rick (MVP - Excel)


"Pawan" wrote in message
...
Thanks Rick.

However I am getting error at the line

Worksheets(Destination).Cells(DestinationLastRow, "A")



"Rick Rothstein" wrote:

I think this macro will do what you want, just assign the appropriate
sheet
names to the two Const (constant) statements (and rename the macro to
something more appropriate)...

Sub CopyNonSpecifiedColumnBItemRows()
Dim UserInput As String, UserList() As String, Cell As Range
Dim SourceLastRow As Long, DestinationLastRow As Long
Const Source As String = "Sheet4"
Const Destination As String = "NewSheet"
SourceLastRow = Worksheets(Source).Cells(Rows.Count, "B").End(xlUp).Row
DestinationLastRow = Worksheets(Destination).Cells( _
Rows.Count, "B").End(xlUp).Row
UserInput = InputBox("List items separated by commas", "Get User List")
If Len(UserInput) Then
UserInput = "," & Replace(UserInput, " ", "") & ","
For Each Cell In Worksheets(Source).Range("B1:B" & SourceLastRow)
If InStr(1, UserInput, "," & Cell.Value & ",", vbTextCompare) = 0
Then
Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")
DestinationLastRow = DestinationLastRow + 1
End If
Next
End If
End Sub

--
Rick (MVP - Excel)


"Pawan" wrote in message
...
Hello

I have strange requirement.
I have a column with hundreds of entries in it. There is one other
columns
associated eith it. For an example consider that following are the
entries
in
column A and B

Column A Column B

PI03590 bar
ZT05693 %
PIC098734 N
TT09823 C

Now I have to provide one userform input box to user. The user will
enter
entries separated by comma (or any other character). I have to filter
entres
in column B as per these inputs.
e.g. If user enters (bar, N) in the input box, then macro should filter
entries in column B and copy "unmatched" result to new sheet. In this
case,
Macro should copy following information in new sheet:


ZT05693 %
TT09823 C.

(I do not want the user to manually filter it in the sheet as this
needs
to
be incorporated in the already existing macro which performs several
actions
on the sheet.)

Thank You in advance


.

How to use comma separated inputs in macro
 

Thanks Rick. Its perfect. :)

"Rick Rothstein" wrote:

It looks like the newsreader word-wrapped that line from the line above it.
What you are seeing as this...

Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")

should all be on one line with a space character between them, like this...

Cell.EntireRow.Copy Worksheets(Destination).Cells(DestinationLastRow, "A")

--
Rick (MVP - Excel)


"Pawan" wrote in message
...
Thanks Rick.

However I am getting error at the line

Worksheets(Destination).Cells(DestinationLastRow, "A")



"Rick Rothstein" wrote:

I think this macro will do what you want, just assign the appropriate
sheet
names to the two Const (constant) statements (and rename the macro to
something more appropriate)...

Sub CopyNonSpecifiedColumnBItemRows()
Dim UserInput As String, UserList() As String, Cell As Range
Dim SourceLastRow As Long, DestinationLastRow As Long
Const Source As String = "Sheet4"
Const Destination As String = "NewSheet"
SourceLastRow = Worksheets(Source).Cells(Rows.Count, "B").End(xlUp).Row
DestinationLastRow = Worksheets(Destination).Cells( _
Rows.Count, "B").End(xlUp).Row
UserInput = InputBox("List items separated by commas", "Get User List")
If Len(UserInput) Then
UserInput = "," & Replace(UserInput, " ", "") & ","
For Each Cell In Worksheets(Source).Range("B1:B" & SourceLastRow)
If InStr(1, UserInput, "," & Cell.Value & ",", vbTextCompare) = 0
Then
Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")
DestinationLastRow = DestinationLastRow + 1
End If
Next
End If
End Sub

--
Rick (MVP - Excel)


"Pawan" wrote in message
...
Hello

I have strange requirement.
I have a column with hundreds of entries in it. There is one other
columns
associated eith it. For an example consider that following are the
entries
in
column A and B

Column A Column B

PI03590 bar
ZT05693 %
PIC098734 N
TT09823 C

Now I have to provide one userform input box to user. The user will
enter
entries separated by comma (or any other character). I have to filter
entres
in column B as per these inputs.
e.g. If user enters (bar, N) in the input box, then macro should filter
entries in column B and copy "unmatched" result to new sheet. In this
case,
Macro should copy following information in new sheet:


ZT05693 %
TT09823 C.

(I do not want the user to manually filter it in the sheet as this
needs
to
be incorporated in the already existing macro which performs several
actions
on the sheet.)

Thank You in advance


.


.