Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Excel macro and formula

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Winnipeg, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In excel 97 I need to be able to delete a user selected row when a specified cell equals or exceeds a certain value

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following and post with problems:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DltRow As Integer
    On Error Resume Next
    If Target.Value > Range("A1").Value Then
    DltRow = InputBox("Type Row Number to Delete")
    Rows(DltRow).Delete
    End If
    End Sub

    Put this code in the module for your worksheet. Target is the cell that you are comparing to Cell A1. Edit as needed.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Winnipeg, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Al, here is where i show my ignorance. I cannot get excel to see the macro to run it. I went into VB editor and added the code as shown but how do i run it?


  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is not a macro that you run. It is an event procedure that will run, whenever your worksheet is changed. I am not sure if it is what you want. But to test it, follow the following procedure:

    1. In excel hit, Alt+F11 to go into VBA
    2. Look to the left in the project explorer and double click your worksheet where you want the fun to happen.
    3. Look to the right for the big blank space and paste in the code.
    4. Close VBA and try entering in numbers.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Winnipeg, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al, that exactly what i want it to do. I'm sorry but Assume i want to compare cell b1 to a1 and have the event happen when b1 is equal or greater than a1. I'm not sure where to edit it to make this happen. Again I'm not really used to working in VB and with other than simple ( really simple) code. Thanks for your patience.

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DltRow As Integer
    On Error Resume Next
    If Target.Address = "$B$1" And Target.Value >= Range("A1").Value Then
    DltRow = InputBox("Type Row Number to Delete")
    Rows(DltRow).Delete
    End If
    End Sub

    _________________
    Hope this helps.
    Kind regards, Al.


    [ This Message was edited by: Al Chara on 2002-04-16 10:23 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    Winnipeg, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again and sorry to keep bugging you. Entered the code as shown - typed it in by hand. No errors. Went to cell a1 and entered a number(5) and then went to b1 and entered a larger number(10) and nothing happened. What am i doing wrong? Can you email me a spread sheet with an example? Email address is wayne.wheaton@eds.com

    Thanks again

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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