+ Reply to Thread
Results 1 to 4 of 4

Cannot find Cell

  1. #1
    Kou Vang
    Guest

    Cannot find Cell

    Sorry for this easy question, but the months of Access Programming has warped
    my mind of the many things I remembered in Excel. If I want to find the word
    "Date", what do I need to add to my VB. Thanks!

    With ActiveSheet.Range("a1:a50")

    Set C = .Find("Date", LookIn:=xlValues)

    ActiveCell.EntireRow.FillUp.Select
    Selection.Delete shift:=xlUp

    Secondly, will the second part of my code delete all the rows above the
    "Date" row?

  2. #2
    Dave Peterson
    Guest

    Re: Cannot find Cell

    One way:

    Option Explicit
    Sub testme01()

    Dim RngToSearch As Range
    Dim LookForWhat As String
    Dim FoundCell As Range

    LookForWhat = "Date"

    With Worksheets("sheet1")
    Set RngToSearch = .Range("a1:a50")
    With RngToSearch
    Set FoundCell = .Cells.Find(what:=LookForWhat, _
    after:=.Cells(.Cells.Count), _
    LookIn:=xlValues, _
    lookat:=xlWhole, _
    searchorder:=xlByRows, _
    searchdirection:=xlNext, _
    MatchCase:=False)
    End With

    If FoundCell Is Nothing Then
    MsgBox "Not Found"
    Else
    If FoundCell.Row = 1 Then
    MsgBox "Found in row 1--not deleted!"
    Else
    .Range("A1:A" & FoundCell.Row - 1).EntireRow.Delete
    End If
    End If
    End With

    End Sub

    Kou Vang wrote:
    >
    > Sorry for this easy question, but the months of Access Programming has warped
    > my mind of the many things I remembered in Excel. If I want to find the word
    > "Date", what do I need to add to my VB. Thanks!
    >
    > With ActiveSheet.Range("a1:a50")
    >
    > Set C = .Find("Date", LookIn:=xlValues)
    >
    > ActiveCell.EntireRow.FillUp.Select
    > Selection.Delete shift:=xlUp
    >
    > Secondly, will the second part of my code delete all the rows above the
    > "Date" row?


    --

    Dave Peterson

  3. #3
    Jim Thomlinson
    Guest

    RE: Cannot find Cell

    You need to ensure that Date was found and then select that cell for your
    code to work...

    Set C = .Find("Date", LookIn:=xlValues)
    if C is nothing then
    msgbox "Not Found"
    else
    C.select
    'the rest of your code
    endif
    --
    HTH...

    Jim Thomlinson


    "Kou Vang" wrote:

    > Sorry for this easy question, but the months of Access Programming has warped
    > my mind of the many things I remembered in Excel. If I want to find the word
    > "Date", what do I need to add to my VB. Thanks!
    >
    > With ActiveSheet.Range("a1:a50")
    >
    > Set C = .Find("Date", LookIn:=xlValues)
    >
    > ActiveCell.EntireRow.FillUp.Select
    > Selection.Delete shift:=xlUp
    >
    > Secondly, will the second part of my code delete all the rows above the
    > "Date" row?


  4. #4
    Kou Vang
    Guest

    RE: Cannot find Cell

    OMG! Yes, how forgetful of me! Of course I was finding it in my code, but I
    wasn't even selecting it, that is why it was being selected! Thanks Jim! (I
    hate Mondays!)

    "Jim Thomlinson" wrote:

    > You need to ensure that Date was found and then select that cell for your
    > code to work...
    >
    > Set C = .Find("Date", LookIn:=xlValues)
    > if C is nothing then
    > msgbox "Not Found"
    > else
    > C.select
    > 'the rest of your code
    > endif
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "Kou Vang" wrote:
    >
    > > Sorry for this easy question, but the months of Access Programming has warped
    > > my mind of the many things I remembered in Excel. If I want to find the word
    > > "Date", what do I need to add to my VB. Thanks!
    > >
    > > With ActiveSheet.Range("a1:a50")
    > >
    > > Set C = .Find("Date", LookIn:=xlValues)
    > >
    > > ActiveCell.EntireRow.FillUp.Select
    > > Selection.Delete shift:=xlUp
    > >
    > > Secondly, will the second part of my code delete all the rows above the
    > > "Date" row?


+ 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