Discussion :

[mail2]

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

Thnx for the Help

[EricDgn]

Bonjour,

Un début de solution, à vérifier et adapter:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub BuySell()
    Dim shBO As Worksheet, sh1 As Worksheet
    Dim kR As Long, kR1 As Long
    Workbooks.Open "BasketOrder.xlsx"
    Set shBO = Sheets("Sheet1")
    Workbooks.Open "1.xls"
    Set sh1 = Sheets("1-Sheet1")
    kR = 1
    shBO.Activate
    With sh1
        Do While Range("C" & kR) <> ""     '--- terminé si cellule vide
            kR1 = kR + 1
            If Range("C" & kR) <> .Range("B" & (kR1)) Then
                Range("C" & kR).Select
                MsgBox "Tickers différents sur ligne " & kR
                Exit Do
            Else
                If Range("J" & kR) = "BUY" Then '--- Column J is BUY
                    '--- add 0.05 to column D of 1.xls
                    .Range("D" & kR1) = .Range("D" & kR1) + 0.05
                    '--- 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 Range("L" & kR) > .Range("D" & kR1) Then
                        Range("L" & kR) = .Range("D" & kR1)
                    End If
                ElseIf Range("J" & kR) = "SELL" Then
                    '--- subtract 0.05 to column D of 1.xls
                    .Range("D" & kR1) = .Range("D" & kR1) - 0.05
                    '--- 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 Range("L" & kR) < .Range("D" & kR1) Then
                        Range("L" & kR) = .Range("D" & kR1)
                    End If
                End If
            End If
            kR = kR + 1
        Loop
    End With
    Set sh1 = Nothing
    Set shBO = Nothing
End Sub

Bonne continuation.