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/
add & subtract data as per condition
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
add & subtract data as per condition
You do not have the required permissions to view the files attached to this post.
Last edited by zyxw1234 on 29 Jul 2020, 07:35, edited 1 time in total.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: add & subtract data as per condition
You have asked very similar questions before.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: add & subtract data as per condition
Yes I know Sir
But that is & this is different Sir
But that is & this is different Sir
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: add & subtract data as per condition
We can't write a new macro for each and every tiny change for you. You'll have to learn to do that yourself.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: add & subtract data as per condition
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
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
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: add & subtract data as per condition
Plz share the Macro HansV Sir or Link to that thread which u r talking about
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: add & subtract data as per condition
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.
Reg Copy and Paste
Copy and paste of the data between sheets with add/subtract
They are not exactly the same, but very similar.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: add & subtract data as per condition
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
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
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: add & subtract data as per condition
https://eileenslounge.com/viewtopic.php ... TRACT+0.05
This link will help us in solving that problem
This link will help us in solving that problem
Last edited by HansV on 29 Jul 2020, 20:25, edited 1 time in total.
Reason: to correct invalid link
Reason: to correct invalid link
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: add & subtract data as per condition
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: add & subtract data as per condition
What is the problem now? That macro already opens a .xlsx file, not a .csv file.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: add & subtract data as per condition
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
-
- Administrator
- Posts: 7210
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: add & subtract data as per condition
And from the thread you linked to:zyxw1234 wrote: ↑29 Jul 2020, 20:17https://eileenslounge.com/viewtopic.php ... TRACT+0.05
This link will help us in solving that problem
and
Does that help?HansV wrote: ↑09 Jun 2019, 20:54Hi 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.
Leif