Macro Modification

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

Macro Modification

Post by zyxw1234 »

Code: Select all

Sub STEP5()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim wb1 As Workbook, wb2 As Workbook
    Dim r2&, lr&, i&
    
    Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
    Set ws1 = wb1.Worksheets.Item(1)
    Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Files\AlertCodes.xlsx")
    Set ws2 = wb2.Worksheets.Item(4)
    With ws1
        lr = .Cells(.Rows.Count, "I").End(xlUp).Row
        For i = 2 To lr
            r2 = WorksheetFunction.Match(.Cells(i, "I"), ws2.[B1:B5], 0)
            If ws2.Cells(r2, "D") = ">" Then
                .Cells(i, "K").Value = .Cells(i, "D").Value - 0.01 * .Cells(i, "D").Value
            Else
                .Cells(i, "K").Value = .Cells(i, "D").Value + 0.01 * .Cells(i, "D").Value
            End If
        Next i
    End With
    
End Sub
This code has issue
line that has errors
r2 = WorksheetFunction.Match(.Cells(i, "I"), ws2.[B1:B5], 0)

The issue is with this B1:B5
i have more data & this is not working for that

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

Re: Macro Modification

Post by HansV »

What is the error message?
Best wishes,
Hans

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

Re: Macro Modification

Post by zyxw1234 »

Plz see HansV Sir
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 Modification

Post by HansV »

That means that there is no match. Should there have been one? Check your data!
Best wishes,
Hans

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

Re: Macro Modification

Post by zyxw1234 »

plz see the sample file
You do not have the required permissions to view the files attached to this post.
Last edited by zyxw1234 on 09 Jul 2020, 13:14, edited 2 times in total.

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

Re: Macro Modification

Post by HansV »

You didn't answer my question...
Best wishes,
Hans

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

Re: Macro Modification

Post by zyxw1234 »

No Sir there is a match

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

Re: Macro Modification

Post by HansV »

No, there isn't. When i = 7, ws1.Cells(i, "I") is 236. This does NOT have a match in ws2.[B1:B5].
Try making the range B1:B5 larger.
Best wishes,
Hans

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

Re: Macro Modification

Post by zyxw1234 »

Yes correct HansV Sir
I am looking for that only
& I am unable to do the same
Should I do
B:B?

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

Re: Macro Modification

Post by zyxw1234 »

I am working with one more issue
HansV Sir
I will let u know the details about this
Plz give me some time
Till then plz leave this problem bcoz Right now i cant give the confirmation
Thnx Alot HansV Sir for helping me in solving this problem
Problem is solved or not, I can't give confirmation bcoz something is pending
I will let u know after 2 days for this problem

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

Re: Macro Modification

Post by zyxw1234 »

Code: Select all

Sub STEP5()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim wb1 As Workbook, wb2 As Workbook
    Dim r2&, lr&, i&
    
    Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
    Set ws1 = wb1.Worksheets.Item(1)
    Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Files\AlertCodes.xlsx")
    Set ws2 = wb2.Worksheets.Item(4)
    With ws1
        lr = .Cells(.Rows.Count, "I").End(xlUp).Row
        For i = 2 To lr
            r2 = WorksheetFunction.Match(.Cells(i, "I"), ws2.[B:B], 0)
            If ws2.Cells(r2, "D") = ">" Then
                .Cells(i, "K").Value = .Cells(i, "D").Value - 0.01 * .Cells(i, "D").Value
            Else
                .Cells(i, "K").Value = .Cells(i, "D").Value + 0.01 * .Cells(i, "D").Value
            End If
        Next i
    End With
    
End Sub


HansV Sir I tried with this
But still i am getting error

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

Re: Macro Modification

Post by HansV »

When the error occurs, click Debug.
This will take you to the code.
If you hover the mouse pointer over .Cells(i, "I"), you will see that this cell has value 676.
If you then inspect column B of Sheet4 in AlertCodes.xlsx, you will see that the value 676 does not occur in this column.
So there is no match, and that is the cause of the error.
Best wishes,
Hans

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

Re: Macro Modification

Post by zyxw1234 »

Something i changed in the process for the betterment
now the file would be
Plz have a relook
and I don't know what i have to do so i am looking for help Sir
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 Modification

Post by HansV »

There is no rule against investigating problems yourself, you know...

The problem is still the same.
You originally claimed "there is a match".
That is still not true.
What would you like to happen?
Best wishes,
Hans

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

Re: Macro Modification

Post by zyxw1234 »

Actually i assumed u r asking different question so i said yes
sorry for the same
If matches then macro should do that work
&
If not found (matched )then do nothing

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

Re: Macro Modification

Post by HansV »

Like this. I added comments to explain how it works.

Code: Select all

Sub STEP5()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim wb1 As Workbook, wb2 As Workbook
    Dim r2&, lr&, i&
    
    Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
    Set ws1 = wb1.Worksheets.Item(1)
    Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Files\AlertCodes.xlsx")
    Set ws2 = wb2.Worksheets.Item(4)
    With ws1
        lr = .Cells(.Rows.Count, "I").End(xlUp).Row
        For i = 2 To lr
            ' Reset r2
            r2 = 0
            ' Avoid error messages
            On Error Resume Next
            ' Try to get r2
            r2 = WorksheetFunction.Match(.Cells(i, "I"), ws2.[B:B], 0)
            ' Restore error handling
            On Error GoTo 0
            ' Only set column K if r2 is valid
            If r2 > 0 Then
                If ws2.Cells(r2, "D") = ">" Then
                    .Cells(i, "K").Value = .Cells(i, "D").Value - 0.01 * .Cells(i, "D").Value
                Else
                    .Cells(i, "K").Value = .Cells(i, "D").Value + 0.01 * .Cells(i, "D").Value
                End If
            End If
        Next i
    End With
End Sub
Best wishes,
Hans

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

Re: Macro Modification

Post by zyxw1234 »

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