Copy and Paste based on comparisons/Match and calculations of cells in two workbooks

[DocAElstein]

Moderator Notice...
Here we go again cycle number 3 , slightly different strategy, posting almost the same question in a few cross posts, then juggling around , passing given macros from one place to the other to give the impression of having done something himself.....
https://chandoo.org/forum/threads/if...9/#post-266341
https://eileenslounge.com/viewtopic.php?f=30&t=34937
https://eileenslounge.com/viewtopic.php?f=30&t=34936
https://www.excelforum.com/excel-pro...o-nothing.html https://excelfox.com/forum/showthrea...ll=1#post13495

---

Hi
I am not sure if I understand exactly what you want.

If Application.Match does not find a match, then the code line does not error, instead it returns an error string.
We can use IsError( ) to tell us if an error string has been returned. This will be True if an error string is detected in the ( )

Possibly you want this

     Let MtchRes = Application.Match(arrWs1(Cnt, 9), ClmB(), 0)
        If IsError(MtchRes) Then ' No match was found
         Let arrS1(Cnt, 10) = "delete word"
        Else ' A match was found
            If arrWsA4(MtchRes, 4) = ">" Then
             Let arrS1(Cnt, 10) = "SHORT"
            ElseIf arrWsA4(MtchRes, 4) = "<" Then
             Let arrS1(Cnt, 10) = "BUY"
            Else
            End If
            
        End If

If that is not what you want, then provide a file or files to show me what you want

Alan

---

P.S:
IsError( ) does not tell you if a code line has eroded. If a code line errors, then the macro will stop and you will get an error pop up to tell you about the error.
Application.Match will return either
_ a number telling you the position along where it finds in the second argument what you have in the first argument ( Application.Match( First argument , second argument , 0 ) In other words it returns you the position along of the match
or
_ an error string if it does not find a match

Note also:
WorksheetFunction.Match works a bit differently. If it does not find a match then it will error. I expect your next 50 duplicated cross posted questions will come when a macro you have with that in does not work….
.... Edit: For example didn't take long... https://eileenslounge.com/viewtopic....271335#p271335

[fixer]

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

[fixer]

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\WolfieeeStyle\Desktop\1.xls")
    Set Ws1 = Wb1.Worksheets.Item(1)
    Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\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

https://eileenslounge.com/viewtopic....271443#p271443
https://chandoo.org/forum/threads/vb...rection.44637/

[DocAElstein]

You almost had it right here: https://eileenslounge.com/viewtopic....6a1745#p271427
You were close to correct.

I try to explain again what Hans tried to explain ( I think you almost understood ):
__With Ws1
………
……….
…………………. Cells(i, "E") ……… .Cells(i, "E")
__End With
is almost =
.....Ws1.Cells(i, "E") …… Ws1.Cells(i, "E")

You want Ws2 ( for Alertcodes.xlsx ) , not Ws1

Maybe like

           Wtih Ws1
                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
           End With

This will also be the same

                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
                    Ws1.Cells(i, "K").Value = Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
                Else
                    Ws1.Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
                End If

If this does not work, then you must explain again exactly what you want the macro to do.

Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
is 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx

---

This was wrong:
Ws2.Cells(i, "E").Value - 0.01 *.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * .Cells(i, "E").Value
It is 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of 1.xls
It is the same as
Ws2.Cells(i, "E").Value - 0.01 *Ws1.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * Ws1.Cells(i, "E").Value

---

( I never use
__ With
__ End With
because it confuses me.
But lots of other people, like Hans do use it. Everyone writes codes a bit differently, because all VBA codes can be written in many ways. )

---

Also
Have you seen this ? : https://excelfox.com/forum/showthrea...4565#post14565
Your macro , Sub STEP6() from here
https://excelfox.com/forum/showthrea...ll=1#post14562
and here
https://eileenslounge.com/viewtopic....271385#p271385
has the wrong Lr2

[fixer]

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

No Doc Sir this problem is not solved yet
i am sending the sample file with output in next 10 min

[fixer]

Plz run the macro and see the output Doc Sir
I was at job at that time & so that's y mistake happened
macro will be placed in a seperate file

[DocAElstein]

_1) What macro are you using? ( I got confused with all your similar duplicated cross postings )
_2) Remind me again. What exactly should the macro do? ( I see the output in file, but please also explain again in words what is going on, because I got confused with all your similar duplicated cross postings )

( I have time first tomorrow to look again. So take your time and make sure you have correct files, and all infomation correct!! )

[fixer]

If column I of 1.xls matches with column B of Alertcodes.xlsx & column D has < this then calculate the 1% of of column E & add that 1% to column E & the result which will come it will be pasted to column K of 1.xls

If column I of 1.xls matches with column B of Alertcodes.xlsx & column D has > this then calculate the 1% of of column E & subtract that 1% to column E & the result which will come it will be pasted to column K of 1.xls



sheet name can be anything
macro will be placed in macro.xlsm

[DocAElstein]

You didn't answer one of my questions...

_1) What macro are you using?....)

post the macro you are using....

---

Plz run the macro and see the output....

what macro shall I run??

[fixer]

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\WolfieeeStyle\Desktop\1.xls")
    Set Ws1 = Wb1.Worksheets.Item(1)
    Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\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 * Ws2.Cells(i, "E").Value
                Else
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
                End If
           End If
        Next i
    End With
    Wb1.Save
    Wb1.Close
    Wb2.Close
    
End Sub