Does input box value = cell value? 
Author Message
 Does input box value = cell value?

How can I write this in VBA, xl97:  Whatever is entered into the Input Box, If
= to any cell within "MyRange" Then Goto that Cell.

Thanks for your suggestions



Tue, 10 Jul 2001 03:00:00 GMT  
 Does input box value = cell value?
If there may be multiple instances, this will find the first.

Brian,
Sub findinmyrange()
searchvalue = InputBox("Enter Search value")
If Len(searchvalue) = 0 Then
  MsgBox "Nothing to do"
Else
  Set rng1 = Range("MyRange").Find(What:=searchvalue, _
  After:=Range("MyRange")(Range("myrange").Cells.Count), _
  LookIn:=xlFormulas, LookAt:=xlPart)
  If Not rng1 Is Nothing Then  'cell found
   rng1.Select
  Else
    MsgBox searchvalue & " was not found"
  End If
End If
End Sub

hth,
Tom Ogilvy

Quote:
End Sub-----Original Message-----

Posted At: Friday, January 22, 1999 2:09 PM
Posted To: programming
Conversation: Does input box value = cell value?
Subject: Does input box value = cell value?

How can I write this in VBA, xl97:  Whatever is entered into the Input
Box, If
= to any cell within "MyRange" Then Goto that Cell.

Thanks for your suggestions



Tue, 10 Jul 2001 03:00:00 GMT  
 Does input box value = cell value?
Hi Tom,

I modified your code slightly for my use. I haved defined myrange as
A:A and added an offset at the end. But if the found cell is off the
visible screen how can I display the active cell so the user can see
where they are? I am using a hot area, that's always visible to invoke
this macro.

Sub findinmyrange()
Application.ScreenUpdating = False
searchvalue = InputBox("Enter Ticket Number to Update")
If Len(searchvalue) = 0 Then
  MsgBox "You didn't enter a ticket number"
Else
  Set rng1 = Range("MyRange").Find(What:=searchvalue, _
  After:=Range("MyRange")(Range("myrange").Cells.Count), _
  LookIn:=xlValues, LookAt:=xlPart)
  If Not rng1 Is Nothing Then  'cell found
   rng1.Select
  Else
    MsgBox searchvalue & " -- this ticket number could not be found,
please check and Re-enter"
  End If
End If
    ActiveCell.Offset(0, 6).Range("A1").Select
Application.ScreenUpdating = True
End Sub

Thanks for this already, if you can add to it that would be wonderful.

Norm

On Fri, 22 Jan 1999 14:38:16 -0500, "Ogilvy, Thomas, W., Mr., ODCSLOG"

Quote:

>If there may be multiple instances, this will find the first.

>Brian,
>Sub findinmyrange()
>searchvalue = InputBox("Enter Search value")
>If Len(searchvalue) = 0 Then
>  MsgBox "Nothing to do"
>Else
>  Set rng1 = Range("MyRange").Find(What:=searchvalue, _
>  After:=Range("MyRange")(Range("myrange").Cells.Count), _
>  LookIn:=xlFormulas, LookAt:=xlPart)
>  If Not rng1 Is Nothing Then  'cell found
>   rng1.Select
>  Else
>    MsgBox searchvalue & " was not found"
>  End If
>End If
>End Sub

>hth,
>Tom Ogilvy



Tue, 10 Jul 2001 03:00:00 GMT  
 Does input box value = cell value?
Thomas Ogilvy's code should put anyone on the road to a solution
of the posted problem, but a couple of comments may be useful.

First, in my never-ending quest to reduce the instances of redundant
use of the Cells Method/Property,  I mention that since a named
range is a collection of cells, Range("myrange").Cells.Count doesn't
add anything by way of execution (though perhaps it may be
preferred by users for other reasons) to Range("myrange").Count

Second, a couple of comments about the arguments to the Find Method.
One needs to be careful about the LookIn and LookAt arguments. If
LookAt = xlPart, then if you are looking for the value of, e.g., 10
in a cell, then 100 or 610 or whatever will satisfy that parameter,
because
it contains 10. And if in addition LookIn = xlFormulas, then a cell whose
value is, e.g., 23, will satisfy the parameters if its formula is, e.g.,
=H10,
because the formula contains 10 even though the cell value doesn't.



Quote:
> If there may be multiple instances, this will find the first.

> Brian,
> Sub findinmyrange()
> searchvalue = InputBox("Enter Search value")
> If Len(searchvalue) = 0 Then
>   MsgBox "Nothing to do"
> Else
>   Set rng1 = Range("MyRange").Find(What:=searchvalue, _
>   After:=Range("MyRange")(Range("myrange").Cells.Count), _
>   LookIn:=xlFormulas, LookAt:=xlPart)
>   If Not rng1 Is Nothing Then  'cell found
>    rng1.Select
>   Else
>     MsgBox searchvalue & " was not found"
>   End If
> End If
> End Sub

> hth,
> Tom Ogilvy
> End Sub-----Original Message-----

> Posted At: Friday, January 22, 1999 2:09 PM
> Posted To: programming
> Conversation: Does input box value = cell value?
> Subject: Does input box value = cell value?

> How can I write this in VBA, xl97:  Whatever is entered into the Input
> Box, If
> = to any cell within "MyRange" Then Goto that Cell.

> Thanks for your suggestions



Tue, 10 Jul 2001 03:00:00 GMT  
 Does input box value = cell value?
Alan,
I agree wholeheartedly with your comments.  My thinking in using the cells
was the possibility of the situation:

? Range("A1").EntireRow.Count
 1
? Range("A1").EntireRow.Cells.Count
 256

But I see that  (counterintutively)

Range("A1").EntireRow.Name = "MyRange"
? Range("MyRange").Count
 256

So my concern for generalization was unjustified in this instance.  Thanks
for pointing this out.  (always eager to learn!)

Regards,
Tom Ogilvy

Quote:

>Thomas Ogilvy's code should put anyone on the road to a solution
>of the posted problem, but a couple of comments may be useful.

>First, in my never-ending quest to reduce the instances of redundant
>use of the Cells Method/Property,  I mention that since a named
>range is a collection of cells, Range("myrange").Cells.Count doesn't
>add anything by way of execution (though perhaps it may be
>preferred by users for other reasons) to Range("myrange").Count

>Second, a couple of comments about the arguments to the Find Method.
>One needs to be careful about the LookIn and LookAt arguments. If
>LookAt = xlPart, then if you are looking for the value of, e.g., 10
>in a cell, then 100 or 610 or whatever will satisfy that parameter,
>because
>it contains 10. And if in addition LookIn = xlFormulas, then a cell whose
>value is, e.g., 23, will satisfy the parameters if its formula is, e.g.,
>=H10,
>because the formula contains 10 even though the cell value doesn't.



<snip>


Wed, 11 Jul 2001 03:00:00 GMT  
 Does input box value = cell value?
Norm
Add

Application.Goto selection,True

after
ActiveCell.Offset(0, 6).Range("A1").Select

This will put the selection in the upper left corner of the screen.
If you want to maintain the the searched column in the picture then you
could put this after

rng1.Select
Application.Goto selection,True

Or, you could reduce the number of selections with:

If Len(searchvalue) = 0 Then
  MsgBox "You didn't enter a ticket number"
Else
  Set rng1 = Range("MyRange").Find(What:=searchvalue, _
  After:=Range("MyRange")(Range("myrange").Count), _
  LookIn:=xlValues, LookAt:=xlPart)
  If Not rng1 Is Nothing Then  'cell found
   Application.Goto rng1, True
   rng1.offset(0,6).select
  Else
    MsgBox searchvalue & " -- this ticket number could not be found, please
check and Re-enter"
  End If
End If
Application.ScreenUpdating = True
End Sub

Recognizing Alan Beban's sage advice, I deleted the Cells and you might want
to go with the xlWhole (vice xlpart) since you appear to be looking for the
whole value of the cell.

Regards,
Tom Ogilvy

Quote:

>Hi Tom,

>I modified your code slightly for my use. I haved defined myrange as
>A:A and added an offset at the end. But if the found cell is off the
>visible screen how can I display the active cell so the user can see
>where they are? I am using a hot area, that's always visible to invoke
>this macro.

>Sub findinmyrange()
>Application.ScreenUpdating = False
>searchvalue = InputBox("Enter Ticket Number to Update")
>If Len(searchvalue) = 0 Then
>  MsgBox "You didn't enter a ticket number"
>Else
>  Set rng1 = Range("MyRange").Find(What:=searchvalue, _
>  After:=Range("MyRange")(Range("myrange").Cells.Count), _
>  LookIn:=xlValues, LookAt:=xlPart)
>  If Not rng1 Is Nothing Then  'cell found
>   rng1.Select
>  Else
>    MsgBox searchvalue & " -- this ticket number could not be found,
>please check and Re-enter"
>  End If
>End If
>    ActiveCell.Offset(0, 6).Range("A1").Select
>Application.ScreenUpdating = True
>End Sub

>Thanks for this already, if you can add to it that would be wonderful.

>Norm

>On Fri, 22 Jan 1999 14:38:16 -0500, "Ogilvy, Thomas, W., Mr., ODCSLOG"

>>If there may be multiple instances, this will find the first.

>>Brian,
>>Sub findinmyrange()
>>searchvalue = InputBox("Enter Search value")
>>If Len(searchvalue) = 0 Then
>>  MsgBox "Nothing to do"
>>Else
>>  Set rng1 = Range("MyRange").Find(What:=searchvalue, _
>>  After:=Range("MyRange")(Range("myrange").Cells.Count), _
>>  LookIn:=xlFormulas, LookAt:=xlPart)
>>  If Not rng1 Is Nothing Then  'cell found
>>   rng1.Select
>>  Else
>>    MsgBox searchvalue & " was not found"
>>  End If
>>End If
>>End Sub

>>hth,
>>Tom Ogilvy



Wed, 11 Jul 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Assign TEXT box VALUE (=) to another TEXT box value which has dynamic value

2. how to get value of an input box into a cell

3. Input box method - value for input of date

4. Text Box value different to Linked cell value - Help

5. Text Box value different to Linked cell value - Help

6. Print PDF in Excel X, Search a specific value in cells/Replace value in other cells

7. syntax for cell value on sheet A = cell value workbook B

8. cells.value = cells.value

9. The list of a cell depends on the value of another cell's value

10. Finding cell by it's value and taking the value of its relative cell


 
Powered by phpBB® Forum Software © phpBB Group