Stop Macro From Falling Over

You need to define a variable as a range, set the value of that variable to equal the results of the .Find operation (rather than trying to select the results directly) and then test for that variable being Nothing, e.g.

Dim rngResult As Range

Set rngResult=Sheets("Link To Cinc").Columns(1).Find(Alpha1, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)

If rngResult Is Nothing Then
  'No match found
Else
  'Do something
End If

You could really do with revisiting your code - in VBA it's rarely necessary to select or activate anything, you can just tell Excel which object you're referring to - i.e.

Alpha1=Workbooks("Master_Schedule.xls").Sheets("Master").Range("X5").Value

Thanks Andrew-R,

A great help.

Hi Andrew-R,

You helped me out a couple of days ago and I was wondering if you could help again today?

I am using this code to delete all rows that don't have the letter "A" in column AN. How can I expand this to enable me to delete all rows that don't have the letter "A" or "n/a" in column AN.

Many thanks

    Firstrow = 5
    LastRow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
    With ActiveSheet
        .DisplayPageBreaks = False
        For lRow = LastRow To Firstrow Step -1
            If .Cells(lRow, "AN").Value = "A" Then
                'Do nothing, This avoid a error if there is a error in the cell
 
            ElseIf .Cells(lRow, "AN").Value <> "A" Then .Rows(lRow).Delete
                'This will delete each row with the Value "ron" in Column A, case sensitive.
 
            End If
        Next
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

Hi Roy,

Yes but I have never used Autoformat in a macro.

Basically what I am trying to do is -

Wkbk1 if the value in column AN is "A" or "n/a" then go to the reference number in column X.
offset that cell by one column to the left (W) and copy that value

Switch to Wkbk2 Select column A and find the reference number from Column X in Wkbk1
when the macro finds the ref no paste the copied cell offset 2 cells to the right

The macro does that okay but I have the anomaly of what to do if the ref number in wkbk1 doesn't exist in wkbk2 - I'd like it copied to the bottom of the data in row A.

Any help appreciated.

Hi Roy,

This works down to the bit in bold where it needs adapting to allow rows containging "n/a" to remain and not be deleted.

    Dim Firstrow As Long
    Dim LastRow As Long
    Dim lRow As Long
    Dim CalcMode As Long
 
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    Firstrow = 5
    LastRow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
    With ActiveSheet
        .DisplayPageBreaks = False
        For lRow = LastRow To Firstrow Step -1
            If .Cells(lRow, "AN").Value = "A" Then
                'Do nothing, This avoid a error if there is a error in the cell
 
            ElseIf .Cells(lRow, "AN").Value <> "A" Then .Rows(lRow).Delete
                'This will delete each row with the Value "ron" in Column A, case sensitive.
 
            End If
        Next
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

Hi snb,

Can you explain where I should put this in the current macro and how it works please?

Thanks

Hi Roy,

I have cut out all of the info except column AN where I want to delete all rows except those with either "A" or "n/a".

Tim.