Matrix addition of overlapped quadrant

T

thecat23

New Member
Joined
Apr 6, 2019
Messages
19
Office Version
  1. 365
I am trying to solve an engineering problem using matrices. I know some programming in VBA but not alot, this problem has puzzled me for quite a while. So any idea will be much appreciated.


Each of [4 x 4] matrix is generated based on the parameters of the equation, but for the purpose of easy illustration and simplicity, let's presume that the 1st matrix is filled with number 1's, 2nd with number 2's, and so on. The top left quadrant of the latter matrix is always adds to the bottom right quadrant of the previous matrix (as per photo shown below). So when a [4 x 4] matrix + [4 x 4] matrix, it forms a super matrix of [6 x 6] with the new value from previous addition, if there are no values, zero will be put in to complete the dynamic super matrix. This matrix will grow when more [4 x 4] matrices is added.


My question how can I achieve this with VBA coding? I looked up using 2 nested For loop for simple addtion of matrices (row 1, col 1 gets to add to row 1, col 1, etc..), but it not my case. I though about to generate the super matrix filled zero's first, then each of the next [4 x 4] is added into the super matrix. But the tricky part is how to offset each of the next matrix by 2 rows and 2 cols. Please help me with this one, thanks in advance for the great brains!!
ekrp8w.jpg
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Re: Matrix addition of overlapped quodrant

Try this:-
This has 12 offsets change as required !!
Code: 
[COLOR="Navy"]Sub[/COLOR] MG07Apr38

[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] N [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oSet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rnga [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rnga = Range("A1:D4")
[COLOR="Navy"]For[/COLOR] Num = 0 To 12
    c = c + 1
    [COLOR="Navy"]Set[/COLOR] Rng = Rnga.Offset(oSet, oSet)
    Rng.Borders.Weight = 2
        [COLOR="Navy"]For[/COLOR] N = 1 To 4
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 4
                Rng.Cells(N, Ac) = Rng.Cells(N, Ac) + c
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR] N
            oSet = oSet + 2
[COLOR="Navy"]Next[/COLOR] Num
Range("A1").Resize(oSet + 2, oSet + 2).SpecialCells(xlCellTypeBlanks).Value = 0
End Sub
Regards Mick
 
Upvote 0
Re: Matrix addition of overlapped quodrant

Try the following code:

Code: 
Sub MatrixOverlap()    
    Mat1 = Range("Matrix1")
    Mat2 = Range("Matrix2")
    Dim Mat3(1 To 6, 1 To 6)
    For i = 1 To 6
        For j = 1 To 6
            FromMat1 = 0: FromMat2 = 0
            On Error Resume Next
            FromMat1 = Mat1(i, j)
            FromMat2 = Mat2(i - 2, j - 2)
            Mat3(i, j) = FromMat1 + FromMat2
        Next
    Next
    On Error GoTo 0
  Range("L1").Resize(6, 6) = Mat3
End Sub
Note that I have populated the initial matrices from worksheet named ranges and poked the resultant matrix back into another worksheet range...you can adapt the code to handle the array feed and array output as you desire.

Hope this helps
 
Upvote 0
Re: Matrix addition of overlapped quodrant

Here's another snippet of code which should handle overlapping quadrant matrices of any valid degree
Code: 
Sub MatrixOverlapGeneric()
    Mat1 = Range("Matrix1")
    Mat2 = Range("Matrix2")
    If UBound(Mat1, 1) = UBound(Mat1, 2) And UBound(Mat2, 1) = UBound(Mat2, 2) And UBound(Mat1) = UBound(Mat2) And UBound(Mat1) Mod 4 = 0 Then
    n = UBound(Mat1)
    m = 3 * n / 2
    ReDim Mat3(1 To m, 1 To m)
    For i = 1 To m
        For j = 1 To m
            If i > n / 2 And i <= n And j > n / 2 And j <= n Then
            Mat3(i, j) = Mat1(i, j) + Mat2(i - n / 2, j - n / 2)
            ElseIf (i <= n / 2 And j <= n) Or (i <= n And j <= n / 2) Then
            Mat3(i, j) = Mat1(i, j)
            ElseIf (i > n / 2 And j > n) Or (i > n And j > n / 2) Then
            Mat3(i, j) = Mat2(i - n / 2, j - n / 2)
            Else:
            Mat3(i, j) = 0
            End If
        Next j
    Next i
    Else
  Exit Sub
  End If
  Range("L1").Resize(m, m) = Mat3
End Sub
 
Upvote 0
Re: Matrix addition of overlapped quodrant

Try this

Code: 
Sub matrix()
   Range("H1").Resize(4, 4).Value = 1
   Range("J7").Resize(4, 4).Value = 2
   With Range("A1").Resize(6, 6)
      .FormulaR1C1 = "=RC[7]+R[4]C[7]"
      .Value = .Value
   End With
End Sub

I came up with something like this:

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:80px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /><col style="width:47px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#92d050; text-align:center; ">3</td><td style="background-color:#92d050; text-align:center; ">3</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#ffff00; text-align:center; ">1</td><td style="background-color:#92d050; text-align:center; ">3</td><td style="background-color:#92d050; text-align:center; ">3</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td style="background-color:#8db4e3; text-align:center; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr></table> <br /><br />
 
Upvote 0
Re: Matrix addition of overlapped quodrant

The following code uses built-in Excel functionality of PasteSpecial (with add). It temporarily adds a sheet to the active workbook, pokes Mat1 and Mat2 on the inserted sheet, copies Mat2 range and pastes special on Mat1 range at the correct location with the addition operation, fills the blank cells in the Mat3 size region with zeros and pokes the values in the Mat3 array. The sheet is then deleted.
Code: 
Sub MatrixOverlapXL()    
    Set Sht = Worksheets.Add
    Mat1 = Range("Matrix1")
    Mat2 = Range("Matrix2")
    n = UBound(Mat1)
    m = n / 2
    Range("A1").Resize(n, n).Value = Mat1
    Set Rng2 = Range("A1").Offset(3 * m + 2, 0).Resize(n, n)
    Rng2.Value = Mat2
    Rng2.Copy
    Range("A1").Offset(m, m).PasteSpecial Operation:=xlPasteSpecialOperationAdd
    Application.CutCopyMode = False
    Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks) = 0
    Mat3 = Range("A1").CurrentRegion.Value
    Application.DisplayAlerts = False
    Sht.Delete
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
Re: Matrix addition of overlapped quodrant

You have some code that has been offered which, if they work, will make my question moot; however, I am wondering how each of your matrices are stored either on the worksheet (where I presume they are in non-overlapping ranges) or in memory (within two-dimensional arrays I would guess)... I think we need to know that in order to write code that can chain three or more such 4x4 matrices. Can you clarify this for me (us)?
 
Upvote 0
Re: Matrix addition of overlapped quodrant

Hi All

Thanks for all the responses, I can see many coding styles already, I am really amazed by this brain sharing, so many talented personnel here!

I was only able to go through one of the many coding (MikeG's coding specifically), and it works for the example that I have provided. I will test all the others this weekend and maybe few others if I get some time after work. As Rick Rothstein mentioned above, I will need to clarify this issue a bit better.

The super/mother matrix at the end will be cut into 4 quadrants (different sizes), then transposed and multiplied with other matrices (not from this super matrix) to obtain the final result that I am after. So, I personally think it will be easier to store the worksheet and then manipulate? But however, I would prefer everything to be store in the memory, and plot the final result on the worksheet, maybe outputs few critical numbers. I am not sure if this is doable or I am only dreaming here. I have done this in the past without the coding, but storing everything in the worksheet, it did get the job done. But it is not dynamic, it will not allow me to make any changes as the everything is fixed by the worksheet I set up. That is the reason that I have reached out here to improve it using VBA.

The previously simplified [4x4] matrices with all 1’s, 2’s or 3’s ….. for simplicity, and actual matrix equation is shown in the code below, each element has to be specifically calculated using the equations below based on the parameters E, I, and L (can be fetched from worksheet). I am thinking the inputs E, I and L can be the same after a number of N repetitions, and then it will be seeking different set of E, I and L for N repetitions then form the super matrix.
I will keep this one going and hopefully at end I will get there with support from the talented. I already learned heaps from you all, please let me know if anything is unclear, I will try my best to answer them timely. Sorry for the late response.
Regards

Code: 
Public Function K_Matrix(E As Double, I  As Double, L As Double)

Dim Array(4, 4) As Double, C As Double


' Construct the member matrix for beam
C = E * I / L ^ 3

Array(1, 1) = C * 12
Array(1, 2) = C * 6 * L
Array(1, 3) = C * (-12)
Array(1, 4) = C * 6 * L

Array(2, 1) = C * 6 * L
Array(2, 2) = C * 4 * L^ 2
Array(2, 3) = C * (-6) * L
Array(2, 4) = C * 2 * L^ 2

Array(3, 1) = C * (-12)
Array(3, 2) = C * (-6) * L
Array(3, 3) = C * 12
Array(3, 4) = C * (-6) * L

Array(4, 1) = C * 6 * L
Array(4, 2) = C * 2 * L^ 2
Array(4, 3) = C * (-6) * L
Array(4, 4) = C * 4 * L^ 2


End Function
 
Upvote 0
Re: Matrix addition of overlapped quodrant

Hi Ravisingh

Out of the all responses, this one is probably the one closest to what I would like to achieve. But I am trying to achieve a more dynamic super matrix that will add more [4 x 4] matrices, the number of [4 x 4] matrices can be between 2 - n, the finale size of the super matrix will be 4+(n-1)x2, where n is the number of [4 x 4] matrix that are added.

So your coding will be more like this from below. I am not sure how to achieve this result, it's not easy for me!!! Thanks for your ideas, really apprecaite it.

Sub MatrixOverlap()
Mat1 = Range("Matrix1")
Mat2 = Range("Matrix1")
Mat3 = Range("Matrix2")
Mat4 = Range("Matrix2")
.........
Matn = Range("Matrixn")


Dim Mat3(1 To 4+(n-1)x2, 1 To 4+(n-1)x2)
For i = 1 To 4+(n-1)x2
For j = 1 To 4+(n-1)x2
FromMat1 = 0: FromMat2 = 0: FromMat3 = 0: FromMat4 = 0: ....... : FromMatn =0
On Error Resume Next
FromMat1 = Mat1(i, j)
FromMat2 = Mat2(i - 2, j - 2)
FromMat3 = Mat3(i - 4, j - 4)
FromMat4 = Mat4(i - 6, j - 6)
............................
FromMatn = Matn(i - n + 2, j - n + 2)
Matn+1(i, j) = FromMat1 + FromMat2 + FromMat3 + FromMat4 + .... + FromMatn
Next
Next
On Error GoTo 0
Range("L1").Resize(4+(n-1)x2, 4+(n-1)x2) = Matn+1
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Add "x" to a matrix
Replies
2
Views
201
Monikbor
M
pcc
  • Question Question
Can anyone explain this behaviour?
Replies
5
Views
371
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Using VBA to Flag and Calculate Overlapping Shift Times
Replies
0
Views
464
Snort
S
ibmy
  • Question Question
How to structure the formula properly?
Replies
2
Views
243
ibmy
ibmy
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
570
Davenil
D

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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