Range Is Nothing wording

  • Hello all i have a question about the Setting of a range and then looking at that. I have the following code and it seems there should be a more elquent way to write it.


    i want to do the 'ELSE' statement if the string in CurrentColumn(7) is found anywhere in row 1.


    I tried:
    if Not Columnfind then, but this gives me an error.


    Code
    Set ColumnFind = ActiveSheet.Rows("1:1").Find(CurrentColumn(7), LookIn:=xlValues)
        If ColumnFind Is Nothing Then
        Else
            ColumnFind.EntireColumn.Copy
            Worksheets("Neg Bump").Select
            Columns("A:A").Select
            ActiveSheet.Paste
        End If


    any suggestions?


    Brian

    [SIZE="6"][FONT="Comic Sans MS"]Brain G.[/FONT][/SIZE] :loopain:

  • Re: Range Is Nothing wording


    Code
    Set ColumnFind = ActiveSheet.Rows("1:1").Find(CurrentColumn(7), LookIn:=xlValues)
    If Not ColumnFind Is Nothing Then
        ColumnFind.EntireColumn.Copy
        Worksheets("Neg Bump").Select
        Columns("A:A").Select
        ActiveSheet.Paste
    End If

    HTH


    Bob

  • Re: Range Is Nothing wording


    There some settings of the Find Method that you must set each time so you don't end up using the last used setings. Try the code below

    Code
    On Error Resume Next
    Set ColumnFind = ActiveSheet.Rows("1:1").Find(What:=CurrentColumn(7), _
                        LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0
    
    
    If Not ColumnFind Is Nothing Then
        ColumnFind.EntireColumn.Copy _
        Destination:=Worksheets("Neg Bump").Range("A1")
    End If

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!