Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
"Tendresse" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
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 -- Gary "Tendresse" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
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 "Jim Cone" wrote: 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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
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 the 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? "Gary Keramidas" wrote: 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 -- Gary "Tendresse" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
Hi Jim,
Thanks a lot for your help. That's exactly what i wanted. Much appreciated. :) "Jim Cone" wrote: 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" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
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 -- Gary "Tendresse" wrote in message ... 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 the 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? "Gary Keramidas" wrote: 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 -- Gary "Tendresse" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a cell with specific value
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 "Gary Keramidas" wrote: 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 -- Gary "Tendresse" wrote in message ... 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 the 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? "Gary Keramidas" wrote: 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 -- Gary "Tendresse" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select specific text in cell | Excel Discussion (Misc queries) | |||
Select specific cells in same row as active cell | Excel Programming | |||
Select specific cell | Excel Discussion (Misc queries) | |||
Select specific columns for a given cell selection | Excel Programming | |||
Select specific Data from a cell | Excel Programming |