none
Find first 0 in column in vba RRS feed

  • Question

  • Hi

    I want to find first 0 in column F after "F6"

    but this code not work

    where is the wrong?

    tanx

    Set c = Range("F:F").Find(0, after:=Cells.Range("F6:F100"), lookat:=xlWhole, searchdirection:=xlNext)
        If Not c Is Nothing Then
            c.Offset(1, -1) = Range("d1")
        Else
            MsgBox "0 not found"
        End If

    Monday, November 13, 2017 9:25 AM

All replies

  • Use

        Set c = Range("F7:F" & Rows.Count).Find(What:=0, LookAt:=xlWhole, SearchDirection:=xlNext)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 13, 2017 10:35 AM
  • tanx

    now I want to creat repeat

    when find first 0 copy special range in offset(1, -1)of 0

    with this copy and paste the first 0 that found is not 0 and now have new value but in range F we have 0 again and again and I want this code find first 0 again and copy special range again and repeat this loop

    can I use "For" for this problem? how?

    Set c = Range("F7:F20").Find(what:=0, lookat:=xlWhole, searchdirection:=xlNext)
        If Not c Is Nothing Then
            c.Offset(1, -1) = Range("d1")
        End If

    Monday, November 13, 2017 6:18 PM
  • Like this:

    Sub RepeatFind()
        Dim c As Range
        Dim s As String
        With Range("F7:F20")
            Set c = .Find(What:=0, Lookat:=xlWhole, Searchdirection:=xlNext)
            If Not c Is Nothing Then
                s = c.Address
                Do
                    c.Offset(1, -1) = Range("d1")
                    Set c = .FindNext(After:=c)
                Loop Until c.Address = s
            End If
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 13, 2017 7:46 PM
  • tanx but it's not work and can't find next 0

    Tuesday, November 14, 2017 8:52 PM
  • It works when I try it.

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 15, 2017 9:36 AM