# Thread: [RESOLVED] Need help getting diagonal values from a matrix using VBA

1. ## [RESOLVED] Need help getting diagonal values from a matrix using VBA

I have a matrix that looks like this. What I need to do is to examine the values in columns L to O so that the results are what is in column AV. The black lines show where the data comes from. Hopefully someone can help me.  Reply With Quote

2. ## Re: Need help getting diagonal values from a matrix using VBA

If you could give us the way you calculate "AV" based on L to O
Because just looking at it, i can't figure out, how "39" in AV6 comes to life  Reply With Quote

3. ## Re: Need help getting diagonal values from a matrix using VBA

39 I get... 2219 I don't ... the pattern as the data is laid out seems to be like that of a knight in chess ... up two, one to the right.
So starting with the 3 at A4.. up two (A2) and to the right one (B2) is 9.
That pattern seems to work... right up until the blank line... that's where the pattern breaks down.

-tg  Reply With Quote

4. ## Re: Need help getting diagonal values from a matrix using VBA

Moderator Actions: Moved from Mobile Development to Office Development.  Reply With Quote

5. ## Re: Need help getting diagonal values from a matrix using VBA Originally Posted by techgnome 39 I get... 2219 I don't ... the pattern as the data is laid out seems to be like that of a knight in chess ... up two, one to the right.
So starting with the 3 at A4.. up two (A2) and to the right one (B2) is 9.
That pattern seems to work... right up until the blank line... that's where the pattern breaks down.

-tg
OUCH!
Now i see it, too.
Never would have figured out, that it's basically concating a String from those cells.
What i see, too:
Colum Q and W:
in Q you have a concat of A to D (resp. L to O), W has the same digits as Q, just different order (no pattern recognizable)

And yea, that empty line with red in W breaks everything.

btw: I see an "AM/PM" thingy.... Time(s)?  Reply With Quote

6. ## Re: Need help getting diagonal values from a matrix using VBA

Maybe the solution is up two, over one... if it's empty, then keep going up until a value is reached.

-tg  Reply With Quote

7. ## Re: Need help getting diagonal values from a matrix using VBA Originally Posted by techgnome;[URL="tel:5583126"
5583126[/URL]]Maybe the solution is up two, over one... if it's empty, then keep going up until a value is reached.

-tg
Hmmmm.
there are 12 rows in total, 6 with AM, 6 with PM.
if that calculation always goes into the row with PM, then i think i could devise an algorithm in VBA.

thats if it stays with those 12 rows.  Reply With Quote

8. ## Re: Need help getting diagonal values from a matrix using VBA

I don't think the ampm is part of the data... at least not part of the relevant data... it looks (to me) that it's hte compositon of two images... where the data in question is over top another image with another set of data... the am/pm designation is a red herring... but I could be wrong...

-tg  Reply With Quote

9. ## Re: Need help getting diagonal values from a matrix using VBA

I apologize; I never got any notifications for this question. In the meantime I figured it out by just using a few semi-hardcoded lines. I needed to do it for both AM and PM values since my picture shows 1 week of data and while that week doesn't have any AM values, weeks below it do. Here is the code I used:
Code:
```Sub DMUAMPM()

Dim lngLastRow As Long
Dim lngRow As Long
Dim strAMPM As String
Dim strColumn As String
Dim intOffset As Integer

With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet
Case "AU3"
strColumn = "AU"
strAMPM = "AM"
intOffset = -1
Case "AT3"
strColumn = "AT"
strAMPM = "PM"
intOffset = 0
Case Else
MsgBox "Please click the DMU PM or DMU AM button"
Exit Sub
End Select
lngLastRow = .Range("L1048576").End(xlUp).Row
.Range(strColumn & "5" & ":" & strColumn & lngLastRow).ClearContents

For lngRow = 5 To lngLastRow Step 13
.Cells(lngRow + intOffset + 1, strColumn) = .Cells(lngRow + intOffset + 3, "L") & _
.Cells(lngRow + intOffset + 1, "M")
.Cells(lngRow + intOffset + 3, strColumn) = .Cells(lngRow + intOffset + 5, "L") & _
.Cells(lngRow + intOffset + 3, "M") & _
.Cells(lngRow + intOffset + 1, "N")
.Cells(lngRow + intOffset + 5, strColumn) = .Cells(lngRow + intOffset + 7, "L") & _
.Cells(lngRow + intOffset + 5, "M") & _
.Cells(lngRow + intOffset + 3, "N") & _
.Cells(lngRow + intOffset + 1, "O")
.Cells(lngRow + intOffset + 7, strColumn) = .Cells(lngRow + intOffset + 9, "L") & _
.Cells(lngRow + intOffset + 7, "M") & _
.Cells(lngRow + intOffset + 5, "N") & _
.Cells(lngRow + intOffset + 3, "O")
.Cells(lngRow + intOffset + 9, strColumn) = .Cells(lngRow + intOffset + 11, "L") & _
.Cells(lngRow + intOffset + 9, "M") & _
.Cells(lngRow + intOffset + 7, "N") & _
.Cells(lngRow + intOffset + 5, "O")
.Cells(lngRow + intOffset + 11, strColumn) = .Cells(lngRow + intOffset + 11, "M") & _
.Cells(lngRow + intOffset + 9, "N") & _
.Cells(lngRow + intOffset + 7, "O")
Next
End With

With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub```  Reply With Quote

vba excel #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•