Condition Met then Replace the data & if not then do nothing


Hi Experts,

I am looking for a macro that will do the below things
Plz see the sample file
there are 3 files ap.xls & BasketOrder.xlsx & macro.xlsm (macro will be placed in macro.xlsm),both files are located in different 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

If column J is BUY of BasketOrder.xlsx then add 1% of column O of ap.xls to column O of ap.xls and compare column O of ap.xls with column L of BasketOrder.xlsx and if column O of ap.xls is smaller than column L of BasketOrder then replace column L of BasketOrder data with column O of ap.xls data(with that added 1% of column O of ap.xls) else do nothing


If column J is SELL of BasketOrder.xlsx then subtract 1% of column P of ap.xls to column P of ap.xls and compare column P of ap.xls with column L of BasketOrder.xlsx and if column P of ap.xls is Greater than column L of BasketOrder then replace column L of BasketOrder data with column P data of ap.xls (with that subtract 1% of column P of ap.xls) else do nothing

Thnx for the Help




You haven't specified if you would be working one row at a time or on all rows. You might want to try something like the folowing (will need tweaking as it references specific cells - you will probably need to use some type of variable and a loop)

Sub BuySell_1()

Dim wbBOJ As String
Dim wbBOL, O101, P99 As Variant

wbBOJ = Workbook("BasketOrder").Worksheet("Output").Range("J1")
wbBOL = Workbook("BasketOrder").Worksheet("Output").Range("L1").Value

O101 = (Workbook("ap").Worksheet("ap-Sheet1").Range("O1").Value * 1.01)
P99 = (Workbook("ap").Worksheet("ap-Sheet1").Range("P1").Value * 0.99)

If wbBOJ = "BUY" Then
If O101 < wbBOL Then wbBOL = O101
If wbBOJ = "SELL" Then
If P99 > wbBOL Then wbBOL = P99

End Sub

This might not be exactly what you are looking for but should help to get you started.

Aug. 2/20

If you had looked carefully at my suggestion you would know I did look at the sample files you provided because I used the sheet names used in those sample files. 
I originally provided a suggestion for working with one specific row at a time. The following is a suggestion for looping through every row and checking if the condition(s) are met. (the tweaking I mentioned) Note: BUY and SELL in column "J" must be upper case or the macro will not find them.

Sub BUY_SELL_test_2()
Dim wbBO_J As String     ' cell "J" - BUY or SELL
Dim wbBO_L, O101, P99 As Variant     ' cell "L", cell "O"+1%, cell "P"-1%
Dim RC, i As Integer     ' used rows count, iterations to repeat
RC = (Workbook("BasketOrder").Worksheet("Output").UsedRange.Rows.Count) - 1     ' minus 1 to account for header row
i = 2     ' start at row 2 - first row after header
Do Until i > RC
wbBO_J = Workbook("BasketOrder").Worksheet("Output").Cells(i, 10)
wbBO_L = Workbook("BasketOrder").Worksheet("Output").Cells(i, 12).Value
O101 = (Workbook("ap").Worksheet("ap-Sheet1").Cells(i, 15).Value * 1.01)
P99 = (Workbook("ap").Worksheet("ap-Sheet1").Cells(i, 16).Value * 0.99)

' check if cell "J" in Row i is BUY or SELL
If wbBO_J = "BUY" Then
    If O101 < wbBO_L Then Cells(i, 12) = O101
    End If
If wbBO_J = "SELL" Then
    If P99 > wbBO_L Then Cells(i, 12) = P99
    End If
    i = i + 1     ' increment row number
End Sub

I have also commented the code for newbies like yourself to be able to follow along.


there can be many rows & may be more  data than the sample
plz see the sample file
excel1 Aug 2, '20 at 3:31 am
I have added to my first suggestion - see Aug. 2 above.
WillieD24 (rep: 20) Aug 2, '20 at 12:34 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login