Macro to collect rows with user-specified cell value
 

I'm trying to pull a range of rows out of a sheet based on the value in

one of the columns being the same. As the value itself will change for

each time I run the macro, I need to be able to define a different
value each time - but I don't want to have to go in and amend the macro

every time.

So the macro starts with an input box where I enter the value the
routine should search for. However although vResponse is being set as
the value I enter and on the face of it the cell value and the inputted

variable match, the lines are not being pulled out and it seems it's
because Excel is not convinced they are the same. How can I correct
this?

Sub USPaymentsListExecute()

Dim vResponse As Variant
vResponse = Application.InputBox( _
Prompt:="Enter Batch Number", _
Default:=Format(Number, 0), _
Type:=2)
'If vResponse = False Then 'User cancelled

Dim myRng2 As Range
Dim myCell2 As Range
Dim HoldRng2 As Range

Set myRng2 = Range("B1:B65536")

For Each myCell2 In myRng2.Cells
If myCell2.Value = vResponse Then
If HoldRng2 Is Nothing Then
Set HoldRng2 = myCell2
Else
Set HoldRng2 = Union(myCell2, HoldRng2)
End If
End If
Next myCell2

If HoldRng2 Is Nothing Then
'do nothing
Else: HoldRng2.EntireRow.Copy
Workbooks.Add
ActiveSheet.Paste

Macro to collect rows with user-specified cell value
 

Your code works fine for me: is column B formatted as text?

J

On 29 Nov, 12:37, wrote:
I'm trying to pull a range of rows out of a sheet based on the value in

one of the columns being the same. As the value itself will change for

each time I run the macro, I need to be able to define a different
value each time - but I don't want to have to go in and amend the macro

every time.

So the macro starts with an input box where I enter the value the
routine should search for. However although vResponse is being set as
the value I enter and on the face of it the cell value and the inputted

variable match, the lines are not being pulled out and it seems it's
because Excel is not convinced they are the same. How can I correct
this?

Sub USPaymentsListExecute()

Dim vResponse As Variant
vResponse = Application.InputBox( _
Prompt:="Enter Batch Number", _
Default:=Format(Number, 0), _
Type:=2)
'If vResponse = False Then 'User cancelled

Dim myRng2 As Range
Dim myCell2 As Range
Dim HoldRng2 As Range

Set myRng2 = Range("B1:B65536")

For Each myCell2 In myRng2.Cells
If myCell2.Value = vResponse Then
If HoldRng2 Is Nothing Then
Set HoldRng2 = myCell2
Else
Set HoldRng2 = Union(myCell2, HoldRng2)
End If
End If
Next myCell2

If HoldRng2 Is Nothing Then
'do nothing
Else: HoldRng2.EntireRow.Copy
Workbooks.Add
ActiveSheet.Paste

Macro to collect rows with user-specified cell value
 

WhytheQ wrote:
Your code works fine for me: is column B formatted as text?

J


Hi, column B is always a numeric which was the problem apparently as it
wasn't matching with the vResponse. Adding vResponse = vResponse * 1
before the loop was the solution - thanks for replying though.