Clear the cell contain from one cell if another cell matches starting 4 texts

[aayaanmayank]

Hi All, can any one help me on this i have two columns if column A starting 4 texts match with column B starting 4 texts then clear the contain from cell which is in column A

MIAMIFLORIDAUSA088317434 <colgroup><col><col></colgroup><tbody> </tbody>	MIAMI <tbody> </tbody>
PORTLOREGONUSA972082168 <tbody> </tbody>	PORTLAND
SUITE1500DISTRICT1018 <tbody> </tbody>	CLEVELAND

<tbody>
</tbody>

 

[Joe4]

Try this VBA code:

Sub MyClear()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows of data
    For r = 1 To lr
'       Check to see if first 4 characters of columns A and B match
        If Left(Cells(r, "A"), 4) = Left(Cells(r, "B"), 4) Then
'           Clear entry in column A
            Cells(r, "A").ClearContents
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[aayaanmayank]

Thanks for your help..

i have made this code, but every time after certain run i get Run time 6 OVERFLOW error.

lastrow1 = shgroup.Range("B" & Rows.Count).End(xlUp).Row


For U = 2 To lastrow1

Set MYNAME2 = Cells(U, "J")
Set MYNAME3 = Cells(U, "L")
MY = Left(MYNAME2, Len(MYNAME2) * 0.3)

x = Len(MY)
y = Left(MYNAME3, x)
If y = MY Then


shgroup.Cells(U, "j").Clear
Else:
End If
Next U

 

[Joe4]

The code you posted looks quite different form what you asked.

Which line gives you the error?
What are your values of MYNAME2 and MYNAME3 when you get the error?

What is "shgroup" and how it is being defined?
Why did you not include that "shgroup" reference in setting MYNAME2 and MYNAME3, but used it everywhere else?
Note that if you are on a different sheet when the code is run, MYNAME2 and MYNAME would pull from the active sheet, and not "shgroup".

 

[aayaanmayank]

Which line gives you the error? MY = Left(MYNAME2, Len(MYNAME2) * 0.3)
What are your values of MYNAME2 and MYNAME3 when you get the error? these two cell contains address so it might be text or string or both.
What is "shgroup" and how it is being defined? Sheet name Set shgroup = ThisWorkbook.Worksheets("Grouping Data_DQ")MYNAME2 & MYNAME2 are running from shgroup which is active

 

[Joe4]

Does it make any difference if you change these lines:

[COLOR=#333333]Set MYNAME2 = Cells(U, "J")[/COLOR]
[COLOR=#333333]Set MYNAME3 = Cells(U, "L")[/COLOR]

To this:

[COLOR=#333333]Set MYNAME2 = [/COLOR][COLOR=#333333]shgroup.[/COLOR][COLOR=#333333]Cells(U, "J")[/COLOR]
[COLOR=#333333]Set MYNAME3 = [/COLOR][COLOR=#333333]shgroup.[/COLOR][COLOR=#333333]Cells(U, "L")[/COLOR]

If not, try running this variation of the code, and let me know what the message box returns:

Sub MyMacro()

    Dim shgroup As Worksheet
    Dim lastrow1 As Long, U As Long
    Dim MYNAME2 As Range, MYNAME3 As Range
    Dim MY As String
    Dim x As Long
    Dim y As String
    
    Set shgroup = ThisWorkbook.Worksheets("Grouping Data_DQ")

    lastrow1 = shgroup.Range("B" & Rows.Count).End(xlUp).Row

    On Error GoTo err_chk
    
    For U = 2 To lastrow1
        Set MYNAME2 = Cells(U, "J")
        Set MYNAME3 = Cells(U, "L")
        MY = Left(MYNAME2, Len(MYNAME2) * 0.3)
        x = Len(MY)
        y = Left(MYNAME3, x)
        If y = MY Then
            shgroup.Cells(U, "j").Clear
        Else
        End If
    Next U

    On Error GoTo 0
    
    Exit Sub
    
err_chk:
    MsgBox "U = " & U & vbCrLf & "MYNAME2 = " & MYNAME2 & vbCrLf & "MYNAME3 = " & MYNAME3, vbOKOnly, "ERROR!"

End Sub