Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with matching pasted cells
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").Cel ls(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with matching pasted cells
Try this from immediate window in the VBE:
MsgBox Worksheets("Selection").Range("D4").End(xlDown).Ad dress 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").Cel ls(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").Cel ls(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with matching pasted cells
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).Ad dress 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").Cel ls(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").Cel ls(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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with matching pasted cells
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).Ad dress 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").Cel ls(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").Cel ls(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with excel charts pasted into Word | Excel Discussion (Misc queries) | |||
Not all pasted text appears in cells | Excel Discussion (Misc queries) | |||
VBA: Pasted Cells Not Formatted | Excel Programming | |||
cell validation even for values pasted into cells | Excel Worksheet Functions | |||
how do I remove spaces from cells that were pasted | Excel Discussion (Misc queries) |