Link to home
Create AccountLog in
Avatar of Martin Liss
Martin LissFlag for United States of America

asked on

I'm probably in my dotage

I have this simple code

Dim lngRowW As Long
Dim lngRowT As Long
With wsTAB
    For lngRowT = 2 To lngLastRowT
        For lngRowW = 2 To lngLastRowW
            If wsTAB.Cells(lngRowT, "E").Value = wsWFD.Cells(lngRowW, "E").Value Then
                wsWFD.Cells(lngRowW, "F") = "Delete"
            End If
        Next
    Next
End With

Open in new window

wsTAB and wsWFD are two different worksheets in the same workbook and column E in each worksheet contains values like 3869 158 5/29/2023 152.5 which is a concatenation of columns A:D. After I run the code every cell in column F in the WFD worksheet says "Delete". Why?????


Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Hi,

I don't see any problems with your code, in the attached file only in one row the "Delete" appears.
A little bit surprising was the syntax of "Cells" with "E"/"F" as second parameter. I always thought here only numeric values are allowed - but it works. MS has no description about it and surprisingly also no parameter description...hmm...

Maybe it is something with your variables as you have not shown the declaration or how you fill it. Maybe you've set the worksheet variables to the same worksheet instead of different?

Cheers,

Christian

Test.xlsm
Avatar of Martin Liss

ASKER

Well in your code lngLastRowW should be 5, but other than that, even after I copied one of my E values to a row in both of your worksheets it works as expected, so I have no idea why mine doesn't.
Here are my actual worksheets.
MyWorksheets.xlsx
Hi,

hard to say without more information..:) You would need to show at least where and how you declare and set the other variables. Hope you've set "Option Explicit" and compiled your code, sometimes it's only a typo in variable names if you miss that.

But we can now say, the problem is not in THIS code... ;)

I would set a breakpoint and go through the loops step by step and see what's in the variables in the variables window.

Cheers,

Christian
Sub DeleteDuplicates()
Dim lngLastRowT As Long
Dim lngLastRowW As Long
Dim lngRow As Long
Dim strConcat As String
Dim wsTAB As Worksheet
Dim wsWFD As Worksheet
Dim lngRowW As Long
Dim lngRowT As Long

Set wsTAB = Sheet1
Set wsWFD = Sheet2

Application.ScreenUpdating = False

wsTAB.Columns("E:F").Cells.ClearContents
wsWFD.Columns("E:F").Cells.ClearContents

With wsTAB
    lngLastRowT = .Range("A1048576").End(xlUp).Row
    lngLastRowW = wsWFD.Range("A1048576").End(xlUp).Row
    For lngRow = 2 To lngLastRowT
        strConcat = Application.Trim(Join(Application.Index(.Range("A" & lngRow & ":D" & lngRow).Value, 1, 0)))
        .Cells(lngRow, "E") = strConcat
    Next
    For lngRow = 2 To lngLastRowW
        strConcat = Application.Trim(Join(Application.Index(wsWFD.Range("A" & lngRow & ":D" & lngRow).Value, 1, 0)))
        wsWFD.Cells(lngRow, "E") = strConcat
    Next
End With

With wsTAB
    For lngRowT = 2 To lngLastRowT
        For lngRowW = 2 To lngLastRowW
            If wsTAB.Cells(lngRowT, "E").Value = wsWFD.Cells(lngRowW, "E").Value Then
                wsWFD.Cells(lngRowW, "F") = "Delete"
            End If
        Next
    Next
End With

Stop
wsWFD.Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
wsTAB.Columns("E:E").Select
Selection.Delete Shift:=xlToLeft

Application.ScreenUpdating = True

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
TAB has one more row than WFD but I never thought of sorting them! And you pointing out that they are the same made me realize that my entire approach was wrong. Thanks.
2023-07-02_15-40-17.png