none
VBA - IsError RRS feed

  • Question

  • Hello All!

    I have this macro:

    Dim n As Long
    Dim m As Integer

    n = ActiveCell.End(xlDown).Rows.Row
    m = ActiveCell.Columns.Column

    Do While Application.WorksheetFunction.IsError(Cells(n, m)) = True Or ActiveCell.Value <> Cells(n, m).Value

    If n >= 1 Then
    n = n - 1
    Else
    Exit Sub
    End If
    Loop

    Cells(n, m).Select

    ------------------------------------------------------------

    My aim was to make a macro that I could keybind to a Shortcut key that would look at the current cell i am in....

    Get the value and find the lowest row within the column that contains that same value.

    This macro works fine until cells with "#DIV/0!" ect started to looked at. I added in "Application.WorksheetFunction.IsError(Cells(n, m)) = True" but this did not fix it. Please could anyone advise on the code I would need to make the macro skip over the cells that have an error.

    Many Thanks,

    Soliddrew

    Thursday, March 17, 2016 10:29 AM

Answers

  • Hi Soliddrew,

    it's just the other direction. You said "lowest row" which I had interpret as the row above.

    Andreas.

    Sub Test()
      Dim Where As Range, Here As Range
      
      'Refer to the cells to search
      Set Where = ActiveCell.EntireColumn
      'Search for the value
      Set Here = Where.Find(ActiveCell.Value, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchDirection:=xlPrevious)
      'Go to that cell
      Here.Select
    End Sub

    • Proposed as answer by David_JunFeng Friday, March 18, 2016 1:58 AM
    • Marked as answer by Soliddrew Friday, March 18, 2016 9:06 AM
    Thursday, March 17, 2016 4:48 PM

All replies


  • My aim was to make a macro that I could keybind to a Shortcut key that would look at the current cell i am in....

    Get the value and find the lowest row within the column that contains that same value.

    Totally wrong way, use RANGE.FIND

    Andreas.

    Sub Test()
      Dim Where As Range, Here As Range
      
      'Refer to the cells to search
      Set Where = ActiveCell.EntireColumn
      'Search for the value
      Set Here = Where.Find(ActiveCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
      'Go to that cell
      Here.Select
    End Sub
    

    Thursday, March 17, 2016 11:19 AM
  • Hello Andreas,

    I have just tried this macro and the same cell just stays selected.

    for example:

    0.05

    0.06 < Your Macro started here & Finished Here

    0.05

    0.05

    0.05

    0.06 < This is where I would like macro to put me.

    also if it was sorted:

    0.8

    0.8

    0.8

    0.9 < Your Macro started here & Finished Here

    0.9

    0.9

    0.9

    0.9 < This is where I would like macro to put me.

    Kind Regards,

    Soliddrew

    Thursday, March 17, 2016 11:30 AM
  • Hi Soliddrew,

    it's just the other direction. You said "lowest row" which I had interpret as the row above.

    Andreas.

    Sub Test()
      Dim Where As Range, Here As Range
      
      'Refer to the cells to search
      Set Where = ActiveCell.EntireColumn
      'Search for the value
      Set Here = Where.Find(ActiveCell.Value, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchDirection:=xlPrevious)
      'Go to that cell
      Here.Select
    End Sub

    • Proposed as answer by David_JunFeng Friday, March 18, 2016 1:58 AM
    • Marked as answer by Soliddrew Friday, March 18, 2016 9:06 AM
    Thursday, March 17, 2016 4:48 PM
  • Thankyou For your Help Andreas, this is spot on =)

    The ".Find(ActiveCell.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)" is very cool, I never knew about those lookin & lookat code before. They will be helpful for future VBA i write. I've only been writing VBA for a few weeks now so still learning stuff like that. Code like this doesnt come up on the sugested list when I type code so until someone types it on this forum i never really know it exists lol ^^ 

    Thanks Again,

    Soliddrew

    Friday, March 18, 2016 9:13 AM