Results 1 to 9 of 9

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

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,360

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

    I have a matrix that looks like this.
    Name:  2022-10-16_15-56-18.jpg
Views: 66
Size:  38.9 KB

    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.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,552

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,064

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,183

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

    Moderator Actions: Moved from Mobile Development to Office Development.
    "Code is like humor. When you have to explain it, it’s bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,552

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

    Quote Originally Posted by techgnome View Post
    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)?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,064

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,552

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

    Quote 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.

    Martin, more info?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,064

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,360

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

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width