Macro Help: If not found, then; if found, then

[mblucsok]

Hi All:

I'm in the process of creating a macro that, among other things, copies a line of data (found by searching for a row header, in this case "Depreciation - mach and equip impairments") from one tab ("Depreciation Macro"), finds the matching line on a different tab ("Variance"), and pastes the data line into the corresponding line on the "Variance" tab. This works fine when there is an already existing "Deprec exp mach and equip impairments" line on the "Variance" tab. However, due to the nature of the workbooks I'm using, there will not always be a matching line on the "Variance" tab. What I want to do is to have the macro run like the code below when the matching line is found on the "Variance" tab. And when there is NO matching line found, I want to insert and create said matching line on the "Variance" tab, and proceed to paste the data. Is there a simple way to do this?

I know how to create the code that will insert the line; what I'm really looking for is a code that will say, "If the corresponding line on "Variance" is found, paste the data; if the line is not found on "Variance," insert a line and then paste the data." Thanks in advance for any help received!

Sheets("Depreciation Macro").Select
ActiveWorkbook.Worksheets("Depreciation Macro").Columns(11).Find(" Depreciation - mach and equip impairments ").Select
ActiveCell.Offset(, 1).Resize(1, 23).Copy
Sheets("Variance").Select
Cells.Find(What:="Deprec exp mach and equip impairments", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[RCBricker]

Hi All:

I'm in the process of creating a macro that, among other things, copies a line of data (found by searching for a row header, in this case "Depreciation - mach and equip impairments") from one tab ("Depreciation Macro"), finds the matching line on a different tab ("Variance"), and pastes the data line into the corresponding line on the "Variance" tab. This works fine when there is an already existing "Deprec exp mach and equip impairments" line on the "Variance" tab. However, due to the nature of the workbooks I'm using, there will not always be a matching line on the "Variance" tab. What I want to do is to have the macro run like the code below when the matching line is found on the "Variance" tab. And when there is NO matching line found, I want to insert and create said matching line on the "Variance" tab, and proceed to paste the data. Is there a simple way to do this?

I know how to create the code that will insert the line; what I'm really looking for is a code that will say, "If the corresponding line on "Variance" is found, paste the data; if the line is not found on "Variance," insert a line and then paste the data." Thanks in advance for any help received!

Sheets("Depreciation Macro").Select
ActiveWorkbook.Worksheets("Depreciation Macro").Columns(11).Find(" Depreciation - mach and equip impairments ").Select
ActiveCell.Offset(, 1).Resize(1, 23).Copy
Sheets("Variance").Select
Cells.Find(What:="Deprec exp mach and equip impairments", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

wrap your code in an IF statement that checks to see if there is an error. Here is a little snip of code I wrote to show you what I mean. Now all you have to do is write the code to add any missing row headers.

Sub rng_FIND()
Dim rng As Range, rngFND As Range
Dim ws As Worksheet

Set ws = ActiveSheet
With ws
    Set rng = Range(.Cells(2, 1), .Cells(10, 1))
    On Error Resume Next
    Set rngFND = rng.Find("apples")
    'Set rngFND = rng.Find("grapes")
    rngFND.Select
    If Err.Number > 0 Then
        MsgBox "rng not found"
    Else
        MsgBox "good to go"
    End If
End With
End Sub

basically I made a list of three fruits in column A starting in cell 2

I check for apples and then select that cell

I change my search with a comment tag and search for grapes, but since that was not in the list trying to select it will throw an error.

The if statement checks if the error number is higher than 0 if so do one thing. if not do another.

So for you, your code that works goes in the else section. you just have to write your code for the true portion of the if statement

 

[mblucsok]

wrap your code in an IF statement that checks to see if there is an error. Here is a little snip of code I wrote to show you what I mean. Now all you have to do is write the code to add any missing row headers.

Sub rng_FIND()
Dim rng As Range, rngFND As Range
Dim ws As Worksheet

Set ws = ActiveSheet
With ws
    Set rng = Range(.Cells(2, 1), .Cells(10, 1))
    On Error Resume Next
    Set rngFND = rng.Find("apples")
    'Set rngFND = rng.Find("grapes")
    rngFND.Select
    If Err.Number > 0 Then
        MsgBox "rng not found"
    Else
        MsgBox "good to go"
    End If
End With
End Sub

basically I made a list of three fruits in column A starting in cell 2

I check for apples and then select that cell

I change my search with a comment tag and search for grapes, but since that was not in the list trying to select it will throw an error.

The if statement checks if the error number is higher than 0 if so do one thing. if not do another.

So for you, your code that works goes in the else section. you just have to write your code for the true portion of the if statement

Thanks so much for your response. I still can't seem to get it to work. I receive Run-Time Error '91 when I run the macro with the code below now added to it. The debugger highlights "rngFND.Select" as the cause of the problem. On the worksheet I'm testing, "Deprec exp mach and equip impairments" is not one of the lines. So, it appears it cannot find it, which makes sense. Now how do I get it to recognize this error and move on to the if Err.Number statement that follows? Again, thanks for your help!

Dim rng As Range, rngFND As Range
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
Set rng = Range(.Cells(2, 3), .Cells(400, 3))
On Error Resume Next
Set rngFND = rng.Find("Deprec exp mach and equip impairments")
rngFND.Select
If Err.Number > 0 Then
Cells.Find(What:="Deprec exp machinery", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End With

 

[RCBricker]

try this. remember to change your sheet names, ranges and cell references to match your needs.

I re-wrote this to try and do everything you are trying to do. I wrapped the code in a FOR EACH loop which will now cycle through all of the items in the first sheet and move the data to the second sheet.

Sub rng_FIND()
Dim rngMACRO As Range, rngFNDM As Range, rngVAR As Range, rngFNDV As Range, _
    rngCOPY As Range, rngDEST As Range, rngINSERT As Range, cell As Range
Dim wsMACRO As Worksheet, wsVar As Worksheet
Dim lngROWM As Long, lngROWV As Long, lngCOL As Long

    Set wsMACRO = Sheets("Depreciation Macro")
    wsMACRO.Select
    With wsMACRO
        lngROWM = Range("A" & .ROWS.Count).End(xlUp).Row
        lngCOL = Cells(2, .Columns.Count).End(xlToLeft).Column
        Set rngMACRO = Range(.Cells(2, 1), .Cells(lngROWM, 1))
    End With
    
    Set wsVar = Sheets("Variance")
    wsVar.Select
    With wsVar
        lngROWV = Range("A" & .ROWS.Count).End(xlUp).Row
        Set rngVAR = Range(.Cells(1, 1), .Cells(lngROWV, 1))
    End With
    
    For Each cell In rngMACRO
        wsMACRO.Select
        With wsMACRO
            Set rngFNDM = rngMACRO.Find(cell.Value)
            Set rngFNDV = rngVAR.Find(cell.Value)
            If rngFNDV Is Nothing Then
                wsVar.Select
                With wsVar
                    Set rngINSERT = rngVAR.Find("fruit")
                    rngINSERT.EntireRow.Offset(1).Insert shift:=xlDown
                    rngINSERT.Offset(1).Value = rngFNDM.Value
                End With
                Set rngCOPY = Range(wsMACRO.Cells(rngFNDM.Row, 2), _
                    wsMACRO.Cells(rngFNDM.Row, lngCOL))
                Set rngDEST = rngINSERT.Offset(1, 1)
                rngCOPY.Copy
                rngDEST.PasteSpecial xlPasteAll
            Else
                Set rngCOPY = Range(wsMACRO.Cells(rngFNDM.Row, 2), _
                    wsMACRO.Cells(rngFNDM.Row, lngCOL))
                Set rngDEST = rngFNDV.Offset(, 1)
                rngCOPY.Copy
                rngDEST.PasteSpecial xlPasteAll
            End If
        End With
    Next cell
End Sub