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
Or
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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Condition Met then Replace the data & if not then do nothing
Answers
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 SubThis 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
Loop
End Sub
I have also commented the code for newbies like yourself to be able to follow along.
Discussion
plz see the sample file
For helping me in solving this problemSir
Have a Awesome Day Sir
Problem Solved