Range Is Nothing wording

brgipple · Jul 26th 2006

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

Bob Phillips · Jul 26th 2006

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

Dave Hawley · Jul 26th 2006

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

Range Is Nothing wording

Participate now!

Share