Alternative vba code - help

N

nubranger

Board Regular
Joined
Dec 23, 2019
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Hi,

I want to check if there is an alternative code (shorter) for the code starting from the line
VBA Code: 
[B] If Abs(r1D(h) - r1D(j)) <= DaySet _[/B]
to
VBA Code: 
[B] And Abs(r1D(q) - r2D(l)) <= DaySet Then[/B]

This loop matches combination of values from two different worksheets. I store all information in an array and perform the loop inside the array. Once it finds a match, the macro will check if the difference in number of days between these matched transactions should not be more than the range set by the user. (e.g., if the amount of transaction A matched with Transaction D, the difference in days between these two transactions should not be over 3 (range set by the user).
VBA Code: 
r1D
below refers to the "Day" (e.g, 10) stored in the array.







VBA Code: 
'5:3 Matching
    For h = 0 To uR1
        For i = 0 To uR2
            For j = h + 1 To uR1
                For o = j + 1 To uR1
                    For p = o + 1 To uR1
                        For q = p + 1 To uR1
                            For k = i + 1 To uR2
                                For l = k + 1 To uR2
                                    If r1V(h) > 0 And r1V(j) > 0 And r1V(o) > 0 And r1V(p) > 0 And r1V(q) > 0 And r2V(i) > 0 And r2V(k) > 0 And r2V(l) > 0 Then
                                        If r1V(h) + r1V(j) + r1V(o) + r1V(p) + r1V(q) = r2V(i) + r2V(k) + r2V(l) Then
[B]                                            If Abs(r1D(h) - r1D(j)) <= DaySet _
                                            And Abs(r1D(h) - r1D(o)) <= DaySet _
                                            And Abs(r1D(h) - r1D(p)) <= DaySet _
                                            And Abs(r1D(h) - r1D(q)) <= DaySet _
                                            And Abs(r1D(h) - r2D(i)) <= DaySet _
                                            And Abs(r1D(h) - r2D(k)) <= DaySet _
                                            And Abs(r1D(h) - r2D(l)) <= DaySet _
                                            And Abs(r1D(j) - r1D(o)) <= DaySet _
                                            And Abs(r1D(j) - r1D(p)) <= DaySet _
                                            And Abs(r1D(j) - r1D(q)) <= DaySet _
                                            And Abs(r1D(j) - r2D(i)) <= DaySet _
                                            And Abs(r1D(j) - r2D(k)) <= DaySet _
                                            And Abs(r1D(j) - r2D(l)) <= DaySet _
                                            And Abs(r1D(o) - r1D(p)) <= DaySet _
                                            And Abs(r1D(o) - r1D(q)) <= DaySet _
                                            And Abs(r1D(o) - r2D(i)) <= DaySet _
                                            And Abs(r1D(o) - r2D(k)) <= DaySet _
                                            And Abs(r1D(o) - r2D(l)) <= DaySet _
                                            And Abs(r1D(p) - r1D(q)) <= DaySet _
                                            And Abs(r1D(p) - r2D(i)) <= DaySet _
                                            And Abs(r1D(p) - r2D(k)) <= DaySet _
                                            And Abs(r1D(p) - r2D(l)) <= DaySet _
                                            And Abs(r1D(q) - r2D(i)) <= DaySet _
                                            And Abs(r1D(q) - r2D(k)) <= DaySet _
                                            And Abs(r1D(q) - r2D(l)) <= DaySet Then[/B]
                                                rpt1.Range(r1A(h)).Interior.ColorIndex = 26
                                                rpt1.Range(r1A(j)).Interior.ColorIndex = 26
                                                rpt1.Range(r1A(o)).Interior.ColorIndex = 26
                                                rpt1.Range(r1A(p)).Interior.ColorIndex = 26
                                                rpt1.Range(r1A(q)).Interior.ColorIndex = 26
                                                rpt2.Range(r2A(i)).Interior.ColorIndex = 26
                                                rpt2.Range(r2A(k)).Interior.ColorIndex = 26
                                                rpt2.Range(r2A(l)).Interior.ColorIndex = 26
                                                r1V(h) = 0: r1V(j) = 0: r1V(o) = 0: r1V(p) = 0: r1V(q) = 0: r2V(i) = 0: r2V(k) = 0: r2V(l) = 0
                                                If IsEmpty(sl) = False Then
                                                    rpt1.Cells(E_N(r1A(q)), sl).Value _
                                                    = "=" & "SUM(" & r1A(h) & "," & r1A(j) & "," & r1A(o) & "," & r1A(p) & "," & r1A(q) & ")" & "-" _
                                                    & "SUM('" & sn & "'!" & r2A(i) & ",'" & sn & "'!" & r2A(k) & ",'" & sn & "'!" & r2A(l) & ")"
                                                    rpt1.Cells(E_N(r1A(q)), sl).Interior.ColorIndex = 26
                                                End If
                                            End If
                                        End If
                                    End If
                                Next l
                            Next k
                        Next q
                    Next p
                Next o
            Next j
        Next i
    Next h
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
It is a bit difficult to read partial section of your code with many variables that we don't know that they refer to … Could you post a small sample of your data from both sheets & the desired outcome
 
Upvote 0
mse330 said:
It is a bit difficult to read partial section of your code with many variables that we don't know that they refer to … Could you post a small sample of your data from both sheets & the desired outcome
Click to expand...


Hi,

So from the sample below, you would see that the values in red in Sheet 1 ties with the values in red in sheet 2. So this is what basically my macro does finding matches based on the value of the transactions and to complete the match, the difference in days between these matched values should not be over the range set by the user. For the sample below, range set is 3 days, which is why the first 4,582.93 was not picked up by the macro.





Sheet 1
DAYCreditsDiscountNet Amount
1​
0​
29.1​
1633.5​
1​
0​
26.32​
1245.18​
1​
0​
3.96​
140.04​
3​
0​
25.76​
1446.24​
3​
0​
28.91​
1296.59​
3​
0​
36.23​
2033.77​
4​
0​
45.94​
1814.86​


Sheet 2
Value dateTransaction TypeDebitCredit
1/2/2020TFR+2,878.68
2/2/2020TFR+140.04
8/2/2020TFR+4,582.53
5/2/2020TFR+1,868.89
3/2/2020TFR+4,582.53
 
Upvote 0
I believe you could rewrite the if condition like below. However, I have read before that accessing the WorksheetFunction might actually slow down your code so you should evaluate both ways to see which one suites your need better

VBA Code: 
 If Abs(r1D(h) - WorksheetFunction.Max(r1D(j), r1D(o), r1D(p), r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(j) - WorksheetFunction.Max(r1D(o), r1D(p), r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(o) - WorksheetFunction.Max(r1D(p), r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(p) - WorksheetFunction.Max(r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(q) - WorksheetFunction.Max(r2D(i), r2D(k), r2D(l))) <= DaySet Then
 
Upvote 0
mse330 said:
I believe you could rewrite the if condition like below. However, I have read before that accessing the WorksheetFunction might actually slow down your code so you should evaluate both ways to see which one suites your need better

VBA Code: 
If Abs(r1D(h) - WorksheetFunction.Max(r1D(j), r1D(o), r1D(p), r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(j) - WorksheetFunction.Max(r1D(o), r1D(p), r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(o) - WorksheetFunction.Max(r1D(p), r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(p) - WorksheetFunction.Max(r1D(q), r2D(i), r2D(k), r2D(l))) <= DaySet _
And Abs(r1D(q) - WorksheetFunction.Max(r2D(i), r2D(k), r2D(l))) <= DaySet Then
Click to expand...

Thank you. I have tried the code. seems to slowed my macro a bit. not sure how it would affect a large data though. but its good to know that I have that as an alternative. thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
135
gtd526
gtd526
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
158
gtd526
gtd526
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
167
Tetra201
Tetra201
K
  • Question
conditional formatting question
Replies
2
Views
156
kerm007
K
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
272
Alex Blakenburg
A

Forum statistics

Threads
1,214,787
Messages
6,121,569
Members
449,038
Latest member
Guest1337

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top