Problem with matching pasted cells

[Aonghus]

OK, I have a userform where a user will input values. Then the program will
copy and paste row values from another workbook and write the input values in
worksheets("Selection").Range("D4").End(xlDown) I have this macro that will
look at one column "S" in a different worksheet("System") and if it doesn't
match with values in ("D4").End(xlDown) it will delete the entire row. But it
doesn't work it deletes every row even if they are similar values, curiously
if I type in a value in Column "S" it works. This is what I have. Thanks in
advance.

Sub Redundancy()
Dim iLastRow As Long
Dim i As Long
'With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
'End With
iLastRow = worksheets("System").Cells(Rows.Count, "S").End(xlUp).Row
For i = iLastRow To 2 Step -1
If IsError(Application.Match(worksheets("System").Cells(i,
"S").Value, _
worksheets("Selection").Range("D4").End(xlDown), 0)) Then
worksheets("System").Cells(i, "S").EntireRow.Delete
Shift:=xlUp
End If
Next i
If worksheets("System").Column("S:S").Value = "" Then
worksheets("System").EntireRow.Delete
End If
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

[Dave Peterson]

Try this from immediate window in the VBE:

MsgBox Worksheets("Selection").Range("D4").End(xlDown).Address

You'll see that this is just one cell.

I'm betting that's not what you meant.

I think I'd use a variable for that range:

Dim LookupRng As Range
With Worksheets("Selection")
Set LookupRng = .Range("d4", .Range("D4").End(xlDown))
End With

Then that little portion of code changes to:

For i = iLastRow To 2 Step -1
If IsError(Application.Match(Worksheets("System").Cells(i, "S").Value, _
LookupRng, 0)) Then
Worksheets("System").Cells(i, "S").EntireRow.Delete
End If
Next i

Aonghus wrote:
>
> OK, I have a userform where a user will input values. Then the program will
> copy and paste row values from another workbook and write the input values in
> worksheets("Selection").Range("D4").End(xlDown) I have this macro that will
> look at one column "S" in a different worksheet("System") and if it doesn't
> match with values in ("D4").End(xlDown) it will delete the entire row. But it
> doesn't work it deletes every row even if they are similar values, curiously
> if I type in a value in Column "S" it works. This is what I have. Thanks in
> advance.
>
> Sub Redundancy()
> Dim iLastRow As Long
> Dim i As Long
> 'With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> 'End With
> iLastRow = worksheets("System").Cells(Rows.Count, "S").End(xlUp).Row
> For i = iLastRow To 2 Step -1
> If IsError(Application.Match(worksheets("System").Cells(i,
> "S").Value, _
> worksheets("Selection").Range("D4").End(xlDown), 0)) Then
> worksheets("System").Cells(i, "S").EntireRow.Delete
> Shift:=xlUp
> End If
> Next i
> If worksheets("System").Column("S:S").Value = "" Then
> worksheets("System").EntireRow.Delete
> End If
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
> End Sub

--

Dave Peterson

[Aonghus]

Thanks Dave for your response. I tried what you told me and the result is
still the same. It doesn't seem to match the values in the Column 'S' that
I've pasted from another workbook, I've tried copying and pasting the values
in the same column but it still doesn't match with the values in the column
in the "Selection" worksheet. The only time that the whole thing works
perfectly is when I manually type in similar values in the 'S' Column even
though they are the same values. Would it be possible that the macro is
unable to read the imported data?

"Dave Peterson" wrote:

> Try this from immediate window in the VBE:
>
> MsgBox Worksheets("Selection").Range("D4").End(xlDown).Address
>
> You'll see that this is just one cell.
>
> I'm betting that's not what you meant.
>
> I think I'd use a variable for that range:
>
> Dim LookupRng As Range
> With Worksheets("Selection")
> Set LookupRng = .Range("d4", .Range("D4").End(xlDown))
> End With
>
> Then that little portion of code changes to:
>
> For i = iLastRow To 2 Step -1
> If IsError(Application.Match(Worksheets("System").Cells(i, "S").Value, _
> LookupRng, 0)) Then
> Worksheets("System").Cells(i, "S").EntireRow.Delete
> End If
> Next i
>
> Aonghus wrote:
> >
> > OK, I have a userform where a user will input values. Then the program will
> > copy and paste row values from another workbook and write the input values in
> > worksheets("Selection").Range("D4").End(xlDown) I have this macro that will
> > look at one column "S" in a different worksheet("System") and if it doesn't
> > match with values in ("D4").End(xlDown) it will delete the entire row. But it
> > doesn't work it deletes every row even if they are similar values, curiously
> > if I type in a value in Column "S" it works. This is what I have. Thanks in
> > advance.
> >
> > Sub Redundancy()
> > Dim iLastRow As Long
> > Dim i As Long
> > 'With Application
> > CalcMode = .Calculation
> > .Calculation = xlCalculationManual
> > .ScreenUpdating = False
> > 'End With
> > iLastRow = worksheets("System").Cells(Rows.Count, "S").End(xlUp).Row
> > For i = iLastRow To 2 Step -1
> > If IsError(Application.Match(worksheets("System").Cells(i,
> > "S").Value, _
> > worksheets("Selection").Range("D4").End(xlDown), 0)) Then
> > worksheets("System").Cells(i, "S").EntireRow.Delete
> > Shift:=xlUp
> > End If
> > Next i
> > If worksheets("System").Column("S:S").Value = "" Then
> > worksheets("System").EntireRow.Delete
> > End If
> > With Application
> > .ScreenUpdating = True
> > .Calculation = CalcMode
> > End With
> > End Sub
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I didn't notice this earlier, but this looks bad, too:

If worksheets("System").Column("S:S").Value = "" Then

..value should return to a single cell (when you're comparing it to a string).

And this line:
Worksheets("System").EntireRow.Delete
shouldn't run at all.

But I don't know what you're really trying to do.

This compiled and ran for me--but try it against a test copy of your workbook:

Option Explicit

'OK, I have a userform where a user will input values. Then the program will
'copy and paste row values from another workbook and write the input values in
'worksheets("Selection").Range("D4").End(xlDown) I have this macro that will
'look at one column "S" in a different worksheet("System") and if it doesn't
'match with values in ("D4").End(xlDown) it will delete the entire row. But it
'doesn't work it deletes every row even if they are similar values, curiously
'if I type in a value in Column "S" it works. This is what I have. Thanks in
'advance.

Sub Redundancy()
Dim iLastRow As Long
Dim i As Long
Dim RngToMatch As Range
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Worksheets("selection")
Set RngToMatch = .Range("d4", .Range("D4").End(xlDown))
End With

With Worksheets("System")
iLastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
For i = iLastRow To 2 Step -1
If IsError(Application.Match(.Cells(i, "S").Value, _
RngToMatch, 0)) Then
.Cells(i, "S").EntireRow.Delete
End If
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Aonghus wrote:
>
> Thanks Dave for your response. I tried what you told me and the result is
> still the same. It doesn't seem to match the values in the Column 'S' that
> I've pasted from another workbook, I've tried copying and pasting the values
> in the same column but it still doesn't match with the values in the column
> in the "Selection" worksheet. The only time that the whole thing works
> perfectly is when I manually type in similar values in the 'S' Column even
> though they are the same values. Would it be possible that the macro is
> unable to read the imported data?
>
> "Dave Peterson" wrote:
>
> > Try this from immediate window in the VBE:
> >
> > MsgBox Worksheets("Selection").Range("D4").End(xlDown).Address
> >
> > You'll see that this is just one cell.
> >
> > I'm betting that's not what you meant.
> >
> > I think I'd use a variable for that range:
> >
> > Dim LookupRng As Range
> > With Worksheets("Selection")
> > Set LookupRng = .Range("d4", .Range("D4").End(xlDown))
> > End With
> >
> > Then that little portion of code changes to:
> >
> > For i = iLastRow To 2 Step -1
> > If IsError(Application.Match(Worksheets("System").Cells(i, "S").Value, _
> > LookupRng, 0)) Then
> > Worksheets("System").Cells(i, "S").EntireRow.Delete
> > End If
> > Next i
> >
> > Aonghus wrote:
> > >
> > > OK, I have a userform where a user will input values. Then the program will
> > > copy and paste row values from another workbook and write the input values in
> > > worksheets("Selection").Range("D4").End(xlDown) I have this macro that will
> > > look at one column "S" in a different worksheet("System") and if it doesn't
> > > match with values in ("D4").End(xlDown) it will delete the entire row. But it
> > > doesn't work it deletes every row even if they are similar values, curiously
> > > if I type in a value in Column "S" it works. This is what I have. Thanks in
> > > advance.
> > >
> > > Sub Redundancy()
> > > Dim iLastRow As Long
> > > Dim i As Long
> > > 'With Application
> > > CalcMode = .Calculation
> > > .Calculation = xlCalculationManual
> > > .ScreenUpdating = False
> > > 'End With
> > > iLastRow = worksheets("System").Cells(Rows.Count, "S").End(xlUp).Row
> > > For i = iLastRow To 2 Step -1
> > > If IsError(Application.Match(worksheets("System").Cells(i,
> > > "S").Value, _
> > > worksheets("Selection").Range("D4").End(xlDown), 0)) Then
> > > worksheets("System").Cells(i, "S").EntireRow.Delete
> > > Shift:=xlUp
> > > End If
> > > Next i
> > > If worksheets("System").Column("S:S").Value = "" Then
> > > worksheets("System").EntireRow.Delete
> > > End If
> > > With Application
> > > .ScreenUpdating = True
> > > .Calculation = CalcMode
> > > End With
> > > End Sub
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson