Hi...

I have a sheet with two types of inputs from the user...

its either a "Y" or a number...

the cells are not all side by side or top to botom...

I would like to take all the letter cells and make them into a range... give
it a name and then on a specific keystrike (alt-Q or something) have a macro
change only the named range cell..

right now Im using the create macro tool and everytime I add something I
have to add more stuff to the macro...

looking for an easier way to do this....

any suggestions
--
Thanks JT

Here is some code to create the named range. It looks through the entire
sheet for cells taht are "Y" and makes a named range (YCells) with them...
Not too sure what you want after this...

Sub FindYCell()
Dim rngFound As Range
Dim rngFoundall As Range
Dim rngToSearch As Range
Dim strFirstAddress As String

Set rngToSearch = ActiveSheet.Cells
Set rngFound = rngToSearch.Find(What:="Y", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundall = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundall = Union(rngFound, rngFoundall)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ActiveSheet.Names.Add "YCells", rngFoundall
End If
End Sub

--
HTH...

Jim Thomlinson


"JT" wrote:

Hi...

I have a sheet with two types of inputs from the user...

its either a "Y" or a number...

the cells are not all side by side or top to botom...

I would like to take all the letter cells and make them into a range... give
it a name and then on a specific keystrike (alt-Q or something) have a macro
change only the named range cell..

right now Im using the create macro tool and everytime I add something I
have to add more stuff to the macro...

looking for an easier way to do this....

any suggestions
--
Thanks JT

Thanks... seems to be working great....
--
Thanks JT


"Jim Thomlinson" wrote:

Here is some code to create the named range. It looks through the entire
sheet for cells taht are "Y" and makes a named range (YCells) with them...
Not too sure what you want after this...

Sub FindYCell()
Dim rngFound As Range
Dim rngFoundall As Range
Dim rngToSearch As Range
Dim strFirstAddress As String

Set rngToSearch = ActiveSheet.Cells
Set rngFound = rngToSearch.Find(What:="Y", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundall = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundall = Union(rngFound, rngFoundall)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ActiveSheet.Names.Add "YCells", rngFoundall
End If
End Sub

--
HTH...

Jim Thomlinson


"JT" wrote:

Hi...

I have a sheet with two types of inputs from the user...

its either a "Y" or a number...

the cells are not all side by side or top to botom...

I would like to take all the letter cells and make them into a range... give
it a name and then on a specific keystrike (alt-Q or something) have a macro
change only the named range cell..

right now Im using the create macro tool and everytime I add something I
have to add more stuff to the macro...

looking for an easier way to do this....

any suggestions
--
Thanks JT