User-defined search term in macro
 

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.

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 ' The macro is going
straight from this line to the next
If HoldRng2 Is Nothing Then ' cell even though
myCell2.Value and vResponse are
Set HoldRng2 = myCell2 ' the same.
Else
Set HoldRng2 = Union(myCell2, HoldRng2)
End If
End If
Next myCell2

User-defined search term in macro
 

A bit clearer...

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

User-defined search term in macro
 

Hi

With vResponse set as a variant, if your Batchnumbers in column B are
numeric, then the comparison will return false.
Try adding a line, vResponse = vResponse * 1
before going through the loop.

--
Regards

Roger Govier


"Rokuro kubi" wrote in message
ups.com...
A bit clearer...

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

User-defined search term in macro
 

Great Roger thanks