Macro Correction

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

Macro Correction

Post by zyxw1234 »

Hi Experts,

Code: Select all

Sub STEP6()
    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
    Wb1.Save
    Wb1.Close
    Wb2.Close
    
End Sub


This codes calculate 1% of column of column D of 1.xls
but what i wanted is instead of column D, it should calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx and paste the result to same place where the current macro is putting,rest everything will be same

Kindly note AlertCodes.xlsx doesn't have headers so keep a eye on the same

Thnx for the Help

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

Re: Macro Correction

Post by HansV »

In the line

.Cells(i, "K").Value = .Cells(i, "D").Value - 0.01 * .Cells(i, "D").Value

the last part .Cells(i, "D").Value (indicated in bold above) is the value of which you take 1%. So you have to replace that with the cell in column E of ws2.
Best wishes,
Hans

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

Re: Macro Correction

Post by zyxw1234 »

Yes HansV Sir

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

Re: Macro Correction

Post by HansV »

So - have you done that?
Best wishes,
Hans

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

Re: Macro Correction

Post by zyxw1234 »

Sir I tried but something I missed
That y I need ur help to solve this problem

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

Re: Macro Correction

Post by HansV »

In the line

If Ws2.Cells(r2, "D") = ">" Then

Ws2.Cells(r2, "D") refers to the cell in column D of ws2. How would you refer to the cell in column E of that worksheet?
Best wishes,
Hans

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

Re: Macro Correction

Post by zyxw1234 »

That I don't know HansV Sir
That's I asked this question

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

Re: Macro Correction

Post by HansV »

Come on - it's not that hard!
Best wishes,
Hans

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

Re: Macro Correction

Post by zyxw1234 »

Code: Select all

Sub STEP6()
    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 = Ws2.Cells(i, "E").Value - 0.01 * .Cells(i, "E").Value
                Else
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * .Cells(i, "E").Value
                End If
            End If
        Next i
    End With
    Wb1.Save
    Wb1.Close
    Wb2.Close
    
End Sub
May be this

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

Re: Macro Correction

Post by HansV »

.Cells(i, "E") refers to the sheet in the With Ws1 ... End With block, so it refers to Ws1, the worksheet in 1.xls.
You want to refer to the worksheet in AlertCodes.xlsx. See the strong hint I posted above...
Best wishes,
Hans

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

Re: Macro Correction

Post by zyxw1234 »

Sir I am unable to make it perfect Sir
Plz help me & let me know the changes in the macro

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

Re: Macro Correction

Post by HansV »

What exactly is the problem? I have practically given you the solution - you should be able to apply it yourself.
Best wishes,
Hans

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

Re: Macro Correction

Post by zyxw1234 »

Sir I am unable to do it
Can u correct it HansV Sir & help me in solving this problem

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

Re: Macro Correction

Post by HansV »

If you aren't even able to solve this minuscule problem, there is no point in helping you.
Best wishes,
Hans

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

Re: Macro Correction

Post by zyxw1234 »

Ok Sir No issues
Thnx Alot HansV Sir for helping me in solving this Problem
Have a Great Day

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

Re: Macro Correction

Post by zyxw1234 »

Last edited by zyxw1234 on 12 Jul 2020, 07:44, edited 1 time in total.

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

Re: Macro Correction

Post by Leif »

zyxw1234 wrote:
11 Jul 2020, 17:14
Ok Sir No issues
Thnx Alot HansV Sir for helping me in solving this Problem
Have a Great Day
As I stated here, you are not asking for help, but once again asking for a solution. It is quite clear to me that you are either too lazy to learn anything, or simply expecting others to do your job for you, or most likely both.

This is your final warning.
Leif

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

Re: Macro Correction

Post by zyxw1234 »

Sir u r getting me wrong
We have to do things which we know
I don't do thing's which i don't know(I am unable to solve this question)
This is coding a single mistake from my end will put me into trouble
& the most important thing, I learned things about vba but not more than 0.1%
Correct me if am wrong Sir
& Sorry if that impression is created but that is was not my intention

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

Re: Macro Correction

Post by zyxw1234 »

Code: Select all

If Ws2.Cells(r2, "D") = ">" Then 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
                Else
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
                End If

Problem Solved
Thnx Alot for helping in the same

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

Re: Macro Correction

Post by HansV »

That wasn't so difficult, was it? :smile:
Best wishes,
Hans