Macro Correction

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Macro Correction

Post by zyxw1234 »

Code: Select all

Sub STEP3()
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\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 5000: Lr2 = 5000
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
     Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
         
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If
        
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub




Hi,
I have to remove the limitations of this macro
I have issue with this Lr1 in this macro
Bco'z of Lr1 this macro has limitations
The macro is working perfect No Doubt In it
But due to this Lr1, it works only for 5000 data
what if there is more data in the file
So i want to remove the limitation of this macro
So plz have a look and do needful

Thnx Alot

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

You already have code for such situations. See for example Macro Modification.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Yes HansV Sir
Both codes are different
Only Lr has relation in both the codes
But I am unable to correct it
Can u plz help me out for the same HansV Sir

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Macro Correction

Post by Doc.AElstein »

I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Give me some time
I will try & I will let u know

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Code: Select all

Sub STEP3()
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\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long: 
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
     Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
         
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If
        
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

HansV Sir
U mean to say this?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

Yes.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

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

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

I am getting Error plz see
the pic and files

Code: Select all

Sub STEP6()
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\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
     Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        Else
        
        End If
        
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

You do not have the required permissions to view the files attached to this post.

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Plz see this also
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

Your code refers to H2.xlsb, but your sample workbook is .xlsx, not .xlsb
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

If I change .xlsb to .xlsx in the code, it runs without error for me!
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Actually the file is H2.xlsb but for providing sample file i changed to .xlsx
I have one more doubt
Should we use .xlsb file
Or should we go for the .xlsm file
which is perfect & more reliable file when we are using macro
& I am getting the error now also & i shared the details also HansV Sir
But u said that it doesn't have issue when u run the macro
So what is the issue that is causing the error

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

If your workbook contains VBA code, you should save it as either .xlsm or .xlsb. Unless the workbook is very large, it doesn't really matter which of the two you choose. For extremely large workbooks, .xlsb is more efficient.

Since I cannot reproduce the error, I don't know what causes it.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

I have done a little change in the macro
Instead of this

Code: Select all

Sub STEP3()
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\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long: 
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
     Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
         
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If
        
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub



I changed to this as per my needs

Code: Select all

Sub STEP6()
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\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
     Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        Else
        
        End If
        
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub


plz see the statement that has else & may be there will be some error

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

This version runs without error for me too.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Yes Sir
So plz have a look and help me in solving this problem Sir

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Macro Correction

Post by Leif »

HansV wrote:
10 Jul 2020, 14:57
This version runs without error for me too.
and then
zyxw1234 wrote:
10 Jul 2020, 15:07
Yes Sir
So plz have a look and help me in solving this problem Sir
Hans clearly stated he could detect no error. What exactly is he expected to solve?
Leif

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

Again: I did have a look, but as far as I can see, there is nothing wrong with the code, and it works for me with the two sample workbooks that you provided. So I don't know how to help you.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Ok No issues HansV Sir
Thnx For helping me in solving this problem Sir