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

[MartinLiss]

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.

[Zvoni]

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

[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

[dday9]

Moderator Actions: Moved from Mobile Development to Office Development.

[Zvoni]

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)?

[techgnome]

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

-tg

[Zvoni]

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.

Martin, more info?

[techgnome]

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

[MartinLiss]

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:

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
    Select Case ActiveCell.Address(0, 0)
        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