Macro Correction

[zyxw1234]

Hi Sir I posted this question in other forum
But unable to track the issue
so i posted the question here
https://www.excelforum.com/excel-progra ... tched.html

Code: Select all

Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\2.xlsx")
Set sh1 = wb1.Worksheets.Item(1)
Set sh2 = wb2.Worksheets.Item(2) '---> you put item(1), while the sheet in wb2 which has data is in sheet2.
Set rngWb1 = sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
Set rngWb2 = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
sh1.Cells(1, 10).Value = "KEEP"

With sh1
For Each c1 In rngWb2
sym = c1.Value
num = c1.Offset(0, 1).Value
    For Each c2 In rngWb1
    tt = c2.Value
    If c2.Value = sym And c2.Offset(0, 7).Value = num Then _
    c2.Offset(0, 8).Value = "keep"
    Next
Next

.Range("A1").AutoFilter Field:=10, Criteria1:="="
.Range("A2", .Range("i" & Rows.Count).End(xlUp)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Cells.AutoFilter
End With

sh1.Cells(1, 10).EntireColumn.Delete
'wb1.Save
'wb1.Close
'wb2.Close


End Sub

Error Details

[zyxw1234]

I was unable to upload all four files in 1 post so i uploaded it here

[HansV]

You don't specify what Rows.Count belongs to. The number of rows in a worksheet of a .xls workbook is less than that of a worksheet in a .xlsx workbook.
Change the line with the error and the one that follows it to

Code: Select all

Set rngWb1 = sh1.Range("B2", sh1.Range("B" & sh1.Rows.Count).End(xlUp))
Set rngWb2 = sh2.Range("A2", sh2.Range("A" & sh2.Rows.Count).End(xlUp))

Also, change the line

Code: Select all

.Range("A2", .Range("i" & Rows.Count).End(xlUp)) _

to

Code: Select all

.Range("A2", .Range("i" & .Rows.Count).End(xlUp)) _

[Doc.AElstein]

same error that could occur in the macro Karmpala gave you before...
https://excelfox.com/forum/showthread.p ... #post14142

Code: Select all

Dim Rng As Range ' For main data range in 1.xls
' Set Rng = Sh1.Range("D2", Sh1.Range("D" & Rows.Count).End(xlUp)) ' This and the next line will error if macro.xlsm is active when the macro is run as Rows.Count will give a much larger number ( 1048576 ) than there are rows in a pre Excel 2007 worksheet ( .
' Set Rng = Sh1.Range(Sh1.Range("D2"), Sh1.Range("D" & Rows.Count).End(xlUp))'
Set Rng = Sh1.Range("D2", Sh1.Range("D" & Sh1.Rows.Count).End(xlUp))

similar to what Han's said... ' This and the next line will error if macro.xlsm is active when the macro is run as Rows.Count will give a much larger number ( 1048576 ) than there are rows in a pre Excel 2007 worksheet

[zyxw1234]

Thnx Alot HansV Sir & Doc Sir for helping me in solving this problem
Problem Solved
Have a Great Day

[zyxw1234]

Code: Select all

Sub STEP6()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\ap.xls")
Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\H2.xlsb")
Set sh1 = wb1.Worksheets.Item(1)
Set sh2 = wb2.Worksheets.Item(2)
Set rngWb1 = sh1.Range("E", sh1.Range("E" & sh1.Rows.Count).End(xlUp))
Set rngWb2 = sh2.Range("A2", sh2.Range("A" & sh2.Rows.Count).End(xlUp))
sh1.Cells(1, 10).Value = "KEEP"

With sh1
For Each c1 In rngWb2
sym = c1.Value
num = c1.Offset(0, 1).Value
    For Each c2 In rngWb1
    tt = c2.Value
    If c2.Value = sym And c2.Offset(0, 7).Value = num Then _
    c2.Offset(0, 8).Value = "keep"
    Next
Next

.Range("A1").AutoFilter Field:=10, Criteria1:="="
.Range("A2", .Range("i" & .Rows.Count).End(xlUp)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Cells.AutoFilter
End With

sh1.Cells(1, 10).EntireColumn.Delete
wb1.Save
wb1.Close
wb2.Close


End Sub

I have one more problem Like this only i posted the code and plz see the sample file
& the code is not working

[HansV]

The line

Code: Select all

Set rngWb1 = sh1.Range("E", sh1.Range("E" & sh1.Rows.Count).End(xlUp))

should be

Code: Select all

Set rngWb1 = sh1.Range("E1", sh1.Range("E" & sh1.Rows.Count).End(xlUp))

[zyxw1234]

No HansV Sir, I am not getting any output
this question is similar but slightly different plz reloook

[HansV]

In the inner loop, you test

Code: Select all

If c2.Value = sym And c2.Offset(0, 7).Value = num Then

c2 is a cell in column E of ap-Sheet1.
c2.Offset(0, 7) is a cell in column L of that sheet.
Column L is entirely blank.
So the condition c2.Offset(0, 7).Value = num will never be met.
That is why the code doesn't appear to do anything.

[zyxw1234]

Yes sir
I dont know what to do now
Plz correct it
Bcoz I am unable to correct it

[HansV]

You haven't told us what you want the macro to do.

[zyxw1234]

A Mistake Happened
plz give me 5 min
i will correct it

[HansV]

I have lots of time...

[zyxw1234]

Problem Solved Thnx Alot HansV Sir
actually this macro is optional & i dont need it
Have a Great Day Sir