+ Reply to Thread
Results 1 to 9 of 9

Moving between a column's cells

  1. #1
    Forum Contributor
    Join Date
    11-07-2005
    Posts
    280

    Moving between a column's cells

    Hi everybody,

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

    Please Login or Register  to view this content.
    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.

  2. #2
    Bob Phillips
    Guest

    Re: Moving between a column's cells

    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" <LoveCandle.1y63ga_1131435303.0163@excelforum-nospam.com> wrote
    in message news:LoveCandle.1y63ga_1131435303.0163@excelforum-nospam.com...
    >
    > 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.
    >
    >
    > --
    > LoveCandle
    > ------------------------------------------------------------------------
    > LoveCandle's Profile:

    http://www.excelforum.com/member.php...o&userid=28612
    > View this thread: http://www.excelforum.com/showthread...hreadid=483046
    >




  3. #3
    Forum Contributor
    Join Date
    11-07-2005
    Posts
    280
    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

  4. #4
    Forum Contributor
    Join Date
    11-07-2005
    Posts
    280
    I have created a simple code to reach my goal >>

    Please Login or Register  to view this content.
    but the code will be soooooooo long and it will be very tiring and 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 available in column C >> I want to get a msgbox tells that the number is not available.

    I hope that my question is more obvious now.

    Thank you for all in advance
    Last edited by LoveCandle; 11-09-2005 at 12:37 AM.

  5. #5
    Rowan Drummond
    Guest

    Re: Moving between a column's cells

    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 wrote:
    > 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 Sub
    > --------------------
    >
    > but the code will be soooooooo long and it will be very tiring and
    > 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 available
    > in column C >> I want to get a msgbox tells that the number is not
    > available.
    >
    > I hope that my question is more obvious now.
    >
    > Thank you for all in advance
    >
    >


  6. #6
    Forum Contributor
    Join Date
    11-07-2005
    Posts
    280
    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 I have the custumer names >> I column B I have the Price >> In column C I have the romm number and in column D I have a special code for every custumer >> What I want is when I type any number greater than 0 in any cell in column D >> an inputbox appears to enter a room number available in column C >> and when I press OK it takes me to the custumer name cell in column A for the room number I type in the inputbox >> and when the room number is not available in column C >> it give me a msgbox tells that the number 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.

    Thank you for all
    Last edited by LoveCandle; 11-09-2005 at 11:57 AM.

  7. #7
    Rowan Drummond
    Guest

    Re: Moving between a column's cells

    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 wrote:
    > 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 I
    > have the custumer names >> I column B I have the Price >> In column C I
    > have the romm number and in column D I have a special code for every
    > custumer >> What I want is when I type any number greater than 0 in any
    > cell in column D >> an inputbox appears to enter a room number available
    > in column C >> and when I press OK it takes me to the custumer name for
    > the room number I type in the inputbox >> and when the room number is
    > not available in column C >> it give me a msgbox tells that the number
    > 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.
    >
    >


  8. #8
    Forum Contributor
    Join Date
    11-07-2005
    Posts
    280
    Thank you sooo much Rowan for your fast and useful help >> That is really what I want.

  9. #9
    Rowan Drummond
    Guest

    Re: Moving between a column's cells

    You're welcome.

    LoveCandle wrote:
    > Thank you sooo much Rowan for your fast and useful help >> That is
    > really what I want.
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1