I give up... VBA help needed

[Mr_Ragweed]

Hi,
I'm trying very hard to solve my own code issues but hours have turned to days on what should probably only be a few lines of script.
My Code is below:

Sub Edit_Dropdown_Lists()
'Hopefully this will be a worksheet event that when data is populated to the ProductFormulas_
'sheet this macro will delete the matching product from the ProdXDept sheet
Dim FinalRow As Long
Dim FinalCol As Long
Dim rngFnd As Range
Dim LRow As Long
FinalRow = ActiveSheet.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column

 If Sheets("ProductFormulas").Select = True Then
    Cells(FinalRow, 1).Select
    
    With Sheets("PrdXDept")
        LRow = Cells(Rows.Count, "A").End(xlUp).Row
        Set rngFnd = Sheets("PrdXDept").Range("A2:J2" & FinalRow)
                If rngFnd.Value = Sheets("ProductFormulas").Range(FinalRow, 1).Value Then
               [COLOR=#FF0000] rngFnd.Select[/COLOR]
                Selection.Delete Shift:=xlUp
                    ElseIf rngFnd.Value <> Sheets("ProductFormulas").Range(FinalRow, 1).Value Then
                    Exit Sub
                    
                End If
        End With
 End If
 
End Sub

The part in red is the obvious error (or at least 1 of them).

The goal of this macro is to find the last cell in Column A on 1 worksheet, Find its match somewhere on a second worksheet, Delete the match and shift cells up so there are no blanks left behind.

I really need help. I've read many posts and tried to adapt them but apparently i'm not smart enough. Most posts seem to be related to using a formula instead of VBA.

Thanks!

 

[VoG]

Perhaps

If rngFnd.Value = Sheets("ProductFormulas").Cells(FinalRow, 1).Value Then
                rngFnd.Delete Shift:=xlUp

 

[Mr_Ragweed]

Thank you for responding. I changed range to cells (oversight on my part).
Now I get a "type mismatch" on that line.

...Also took out the LRow Statement as it wasn't being used in this attempt

 

[VoG]

Could that "value" be an error like #N/A?

 

[Brad Birdman]

I am new to VBA but try and read as much as I can on this site to learn more. On the red text in your example would you just need to add activeworksheet.rngFind.select to make that work?

 

[Mr_Ragweed]

So it looks like i set the rngFnd to a range but then I try to compare the entire range to the cell value i selected on my first sheet - and i know that wont work.
I dont see where im actually asking the second sheet to compare each cell to that value to see if there's a "match" or a "find". Great that i think i can see my problem, but i have no idea how to address it.

Also, when stepping through the macro, i can see it select the proper cell from the ProductFormulas sheet, bu i never see the PrdXDept sheet get activated - shouldn't see this activate and then see the range get selected?

Thanks again

 

[Mr_Ragweed]

Could that "value" be an error like #N/A?

Shouldn't be. There will always be a value in the cell i select on the first sheet and that EXACT value will always be somewhere on the second sheet. Value is a combination of text and numbers ex: Ad-Sorb FC (5G)

Thanks

 

[VoG]

There is something wrong here

Set rngFnd = Sheets("PrdXDept").Range("A2:J2" & FinalRow)

You need to specify a specific cell, not a range.

 

[Mr_Ragweed]

Yeah I noticed that. So i edited and tried this:

Sub Edit_Dropdown_Lists()
'Hopefully this will be a worksheet event that when data is populated to the ProductFormulas_
'sheet this macro will delete the matching product from the ProdXDept sheet
Dim FinalRow As Long
Dim FinalCol As Long
Dim rngFnd As Range
[COLOR=#0000CD]Dim NewData As Range[/COLOR]
FinalRow = ActiveSheet.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
[COLOR=#0000CD]Set NewData = ActiveSheet.Cells(FinalRow, 1)[/COLOR]
 If Sheets("ProductFormulas").Select = True Then
    [COLOR=#0000CD]NewData.Select[/COLOR]
    
    With Sheets("PrdXDept")
        Set rngFnd = Sheets("PrdXDept").Range("A2:J2" & FinalRow)
        
            [COLOR=#0000CD]For Each Cell In rngFnd
                If Cell.Value = NewData Then[/COLOR]
                Cell.Delete Shift:=xlUp
                    ElseIf Cell.Value <> NewData Then
                    Exit Sub
                    
                End If
            Next Cell
        End With
 End If

End Sub

I dont get any error messages now, BUT it appears that it does nothing. It selects the right cell on the first ws. When i step through it, the macro looks like it loops through each cell - though i get no visual confirmation that the second sheet was ever activated. When i just run the macro - nothing happens - it appears to just end after selecting the cell on the first sheet. I'm assuming this means that it thinks it found no matches.

 

[Mr_Ragweed]

I see that my "With" statement is also pretty much useless. So i took it out and activated the PrdXDept sheet with code. I still dont see my range being selected and have nothing alse new to report. I will keep trying and in the meantime maybe will get my eyes opened from one of the pros here....