Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
User-defined search term in macro
Great Roger thanks
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can second, third user sign excel file containing macro | Excel Discussion (Misc queries) | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
Writing macro results to user defined areas within excel | Excel Discussion (Misc queries) | |||
How to stop Excel remembering/loading macro from previously opened Workbook | Excel Discussion (Misc queries) | |||
user defined function row number | Excel Worksheet Functions |