Moving between a column's cells

[LoveCandle]

Hi everybody,

I have this code which works on startup of the book to move to the
selected sheet:


Code:
--------------------
Private Sub Workbook_Open()
Dim MySheets As Worksheet
Dim SheetsName As String
1 SheetsName = Application.InputBox(prompt:="asdfsfd", Title:="sdfsd", Type:=2)
If SheetsName = "" Or SheetsName = "" Then
Exit Sub
Else
For Each MySheets In ActiveWorkbook.Worksheets
If MySheets.Name = SheetsName Then
Sheets(SheetsName).Select
Exit Sub
End If
Next MySheets
MsgBox ("sdfsdfsd")
GoTo 1
End If
End Sub
--------------------


I want to edit it to appear when I select any cell in column D and
press Enter >> and when I type the address of any cell in column D and
Press OK >> it takes me to the cell I typed in the field.

I hope my question is clear >>

Thank you for all.

 

[Bob Phillips]

If you type a cell address into the Name box, left of the formula bar, it
takes you to that cell.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[LoveCandle]

Thank you for ur fast reply,

I know how to use the name box feature .. but I want the code i
delivered for some other reason.

so, if you can help me in this case >> I would appreciate it for u

 

[LoveCandle]

I have created a simple code to reach my goal >>


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoRoom As String
If Range("D" & Target.Row).Value > 0 Then
NoRoom = InputBox("Enter the room number in the field")
End If
Select Case NoRoom
Case Is = 151
Range("A4").Select
Case Is = 152
Range("A5").Select
Case Is = 153
Range("A6").Select
Case Is = 154
Range("A7").Select
Case Is = 155
Range("A8").Select
Case Is = 156
Range("A9").Select
Case Is = 157
Range("A10").Select
End Select

End Su
-------------------

but the code will be soooooooo long and it will be very tiring an
time-wasting for me if I enter more than 300 number into it >>

So my questions are:

1- Is there any way to summarize the code and get the same target.
2- When I enter a number in the InputBox's filed which is not availabl
in column C >> I want to get a msgbox tells that the number is no
available.

I hope that my question is more obvious now.

Thank you for all in advanc

 

[Rowan Drummond]

Not totally sure what you are trying to achieve but try getting rid of
the case statement and using:

If NoRoom > 150 Then
Cells(CInt(NoRoom) - 147, 1).Select
End If

Hope this helps
Rowan

 

[LoveCandle]

Thank you for ur reply >> but that didn't work with me.

Anyhow, In my main file I have four columns A B C and D in column A
have the custumer names >> I column B I have the Price >> In column C
have the romm number and in column D I have a special code for ever
custumer >> What I want is when I type any number greater than 0 in an
cell in column D >> an inputbox appears to enter a room number availabl
in column C >> and when I press OK it takes me to the custumer name fo
the room number I type in the inputbox >> and when the room number i
not available in column C >> it give me a msgbox tells that the numbe
is not available.

The code I deliverd is doing this job >> but it is soooo log for me >
I want a short one please.

I hope that my question is clear now

 

[Rowan Drummond]

Does this mean that if the room number is in column C it is available
and if it is not found it is unavailable? If so then try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RoomNo As String
Dim fndRoom As Range
If Target.Count = 1 And Target.Column = 4 Then
If Target.Value > 0 Then
RoomNo = InputBox("Enter the room number in the field")
With Columns(3)
Set fndRoom = .Find(RoomNo)
End With
If Not fndRoom Is Nothing Then
fndRoom.Offset(0, -2).Select
Else
MsgBox "Room not available"
End If
End If
End If
End Sub

Hope this helps
Rowan

 

[LoveCandle]

Thank you sooo much Rowan for your fast and useful help >> That i
really what I want

 

[Rowan Drummond]

You're welcome.