select a cell with specific value

[Guest]

hi all, i'm stuck and need some help, please.
How do you say the following in VBA?

In range("C15:C100"), find the cell that contains the value 'IDNumber' and
select the entire row where this cell is, and extend the selection to include
the following 9 rows below the cell. If the IDNumber not found, prompt the
user that number not found.

(NB: IDNumber is a variable that the user will enter through an inputBox
earlier in the code. The IDNumber is a unique number, it will only appear
once in the range specified above, or it won't appear at all. But in anycase,
the same number won't appear twice in the range.)

i'm using Excel 2003

Many thanks
Tendresse

 

[Guest]

hi all, i'm stuck and need some help, please.
How do you say the following in VBA?

In range("C15:C100"), find the cell that contains the value 'IDNumber' and
select the entire row where this cell is, and extend the selection to include
the following 9 rows below the cell. If the IDNumber not found, prompt the
user that number not found.

Is this a homework assignment?

What have you tried? Maybe somebody can point you to the thing you should
look at.

Peter Richardson

 

[Gary Keramidas]

try this, i feel a little generous tonight.

Sub homework_assignment()
Dim rng As Range, rngfound As Range
Dim idNumber As Double

idNumber = 6
Set rng = Range("c15:c100")
With rng

Set rngfound = .Find(idNumber, LookIn:=xlValues)
If Not rngfound Is Nothing Then
rngfound.EntireRow.Offset(1).Resize(9).Select
Else
MsgBox "ID Number " & idNumber & " not found"
End If
End With

End Sub

 

[Jim Cone]

Sub FindUserNumber()
Dim IDnumber As Long
Dim rng As Range
Dim vRow As Variant

IDnumber = 1747 'value used for testing
Set rng = Range("C15:C100")
vRow = Application.Match(IDnumber, rng, 0)

If Not IsError(vRow) Then
Set rng = rng(vRow).Resize(9, 1).EntireRow
MsgBox rng.Address
Else
MsgBox "Not Found "
End If
Set rng = Nothing
End Sub
--
Do I get an "A"?

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Tendresse" <[email protected]>
wrote in message
hi all, i'm stuck and need some help, please.
How do you say the following in VBA?

In range("C15:C100"), find the cell that contains the value 'IDNumber' and
select the entire row where this cell is, and extend the selection to include
the following 9 rows below the cell. If the IDNumber not found, prompt the
user that number not found.

(NB: IDNumber is a variable that the user will enter through an inputBox
earlier in the code. The IDNumber is a unique number, it will only appear
once in the range specified above, or it won't appear at all. But in anycase,
the same number won't appear twice in the range.)
i'm using Excel 2003
Many thanks
Tendresse

 

[Guest]

Hello to all of you,
thank you very much for your help, much appreciated. I'll give it a go now
and let you know how i go.

but WHY are you assuming this is a homework assignment?!!!! where did you
get that impression from?

tendresse

 

[Guest]

Hi Gary, thank you very much for your generosity, much appreciated.

I tried your code and it works very well. However, there is still one small
bit needed in there:

The ID numbers i have in the range look like this:
FP1111
FP1112
FP1113 , etc


In the InputBox, when i entered part of a number (for example FP11), the
code selected the first cell with a value starting with FP11. I was expecting
to get the message saying that the number is not found.

How do i look for the EXACT MATCH?

 

[Guest]

Hi Jim,
Thanks a lot for your help. That's exactly what i wanted.
Much appreciated.

 

[Gary Keramidas]

since you didn't specify what the number was, i chose double. but you really
want a string.

Sub homework_assignment()
Dim rng As Range, rngfound As Range
Dim idNumber As String

idNumber = "FP1115"
Set rng = Range("c15:c100")
With rng

Set rngfound = .Find(idNumber, LookIn:=xlValues)
If Not rngfound Is Nothing Then
rngfound.EntireRow.Offset(1).Resize(9).Select
Else
MsgBox "ID Number " & idNumber & " not found"
End If
End With

End Sub

 

[Guest]

Hi Gary,
I don't think that's what is causing the problem. I had already replaced
'double' with 'string'.

What i wanted is to find the exact match of the 'whole' number entered by
user in the InputBox. However, what is happening with your code is different.
For example:

if I input an ID number such as 'FP11', the code should prompt me that such
number doesn't exist. But instead, the code selects the first cell that
starts with FP11 (eg. FP1145).

Jim's code overcame this bit. All i needed was to replace the line:

rngfound = .Find(idNumber, LookIn:=xlValues)

with

Rngfound = Application.Match(idNumber, rng, 0)

this way it looks for exact matches.

Thank you again for your help.
Have a great day
Tendresse