add & subtract data as per condition

[zyxw1234]

Hi Experts,
I am looking for a macro that will do the things mentioned below

there are 3 files 1.xls & BasketOrder.xlsx & macro.xlsm (macro will be placed in macro.xlsm),both files are loacted in diffrent places so the path will be hardcoded in the macro so that i can change it as per my needs
sheet name can be anything
plz see the sample file


If column C of basketorder.xlsx matches with column B of 1.xls & Column J is BUY then add 0.05 to column D of 1.xls & compare column L of basketorder.xlsx with column D of of 1.xls & if column D of 1.xls is lower then Column L of basketorder.xlsx then replace column L data with column D data OF 1.xls

If column C of basketorder.xlsx matches with column B of 1.xls & Column J is SELL then subtract 0.05 to column D of 1.xls & compare column L of basketorder.xlsx with column D of of 1.xls & if column D of 1.xls is greater then Column L of basketorder.xlsx then replace column L data with column D data OF 1.xls

https://www.excelforum.com/excel-progra ... y-vba.html
https://www.mrexcel.com/board/threads/a ... n.1141303/

[HansV]

You have asked very similar questions before.

[zyxw1234]

Yes I know Sir
But that is & this is different Sir

[HansV]

We can't write a new macro for each and every tiny change for you. You'll have to learn to do that yourself.

[zyxw1234]

Sir may I see that code which u r talking about?
Bcoz I think I asked the question previously but that & this is completely different only add & subtract is there but complete macro is different
May I see that macro which u r talking about
No Doubt if there will be a tiny change them I will try to do it by my own

But that & this macro is only 5% similar as per my knowledge

[zyxw1234]

Plz share the Macro HansV Sir or Link to that thread which u r talking about

[HansV]

See:

Reg Copy and Paste
Copy and paste of the data between sheets with add/subtract

They are not exactly the same, but very similar.

[zyxw1234]

It is not similar HansV Sir
https://eileenslounge.com/viewtopic.php ... mp;t=30708
This macro will not work for me bcoz it is solving the same by calculation & in that caculation u have mentioned sheet name also but sheet name can be anything(so any addition in this macro will not work)


https://eileenslounge.com/viewtopic.php ... mp;t=30649
In this macro there is no matching part & this is not similar to his problem


Only two things are same
add 0.05 & subtract 0.05 this line is similar to this thread

[zyxw1234]

https://eileenslounge.com/viewtopic.php ... TRACT+0.05

This link will help us in solving that problem

[zyxw1234]

Code: Select all

Sub CopyData()
    Dim wbk1 As Workbook
    Dim wsh1 As Worksheet
    Dim lngLast1 As Long
    Dim rng1 As Range
    Dim wbk2 As Workbook
    Dim wsh2 As Worksheet
    Dim lngLast2 As Long
    Dim r2 As Long
    Application.ScreenUpdating = False

    Set wbk1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
    Set wsh1 = wbk1.Worksheets(1)
    lngLast1 = wsh1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Set wbk2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\BasketOrder.xlsx")
    Set wsh2 = wbk2.Worksheets(1)
    lngLast2 = wsh2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For r2 = 2 To lngLast2
       
        If wsh2.Range("L" & r2).Value = "" Then
            
            Set rng1 = wsh1.Range("B:B").Find(What:=wsh2.Range("C" & r2).Value, LookAt:=xlWhole)
            
            If Not rng1 Is Nothing Then
                Select Case wsh2.Range("J" & r2).Value
                    Case "BUY"
                        wsh2.Range("L" & r2).Value = rng1.Offset(0, 3) + 0.05
                    Case "SELL"
                        wsh2.Range("L" & r2).Value = rng1.Offset(0, 4) - 0.05
                End Select
            End If
        End If
    Next r2

    
    wbk1.Close SaveChanges:=False

    
    Application.DisplayAlerts = False
    wbk2.Close SaveChanges:=True
    Application.DisplayAlerts = True

    
    Application.ScreenUpdating = True
End Sub

Plz see the macro& this macro was made for .csv file but now that .csv file is converted to .xlsx
So plz have a look into the macro & any changes are there then plz let me know HansV Sir

[HansV]

What is the problem now? That macro already opens a .xlsx file, not a .csv file.

[zyxw1234]

I sent u the macro To check If u have any suggestion then plz share that details or changes to me so that i can make the chnages

[Leif]

https://eileenslounge.com/viewtopic.php ... TRACT+0.05

This link will help us in solving that problem

And from the thread you linked to:

I would recommend that you get a professional Excel developer to write the code for you. That will be much more efficient than asking more or less the same question over and over again.

and

Hi leonardo1234/umbug/roger@/c@1234,

I will give you a macro this time. But as mentioned before in this and other threads, it is not the purpose of a forum like this to do that all the time, especially if you aren't willing or able to learn anything from the replies that you receive.

Does that help?