F
firstcasualty
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
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