From macro to vba
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: From macro to vba

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default From macro to vba

    Hello everyone.
    I am here trying to see the transformation from a simple Macro to a Code, I know guy for you is something so Simple, but “ sorry “ no for me, I am a new self-thaught bee.
    I just sum two numbers with the macro recorder I got this:

    Range("H2").Select
    ActiveCell.Formula = "=SUM(A2:B2)"

    So with my Basic knowledge I see that H2 has to be a variable, sum I can think as a total, or another variable, and the two location as well, so I got this:

    Sub real_code()
    Dim H As Integer, A As Integer, B As Integer
    A = Range("A2").Value: B = Range("B2").Value
    H = A + B
    Range("H1").Value = H
    End Sub

    And “”Bingo”” work well.

    But now I intent to transform the macro using the function COUNT,

    Sub C_m()
    Range("H6").Select
    ActiveCell.Formula = "=COUNT(B:B)"
    End Sub

    BUT then, I realize my logic do not worked as before, the only thing
    So far, I can see is, must be a loop, how I will deduce that?, “ I don’t know” that why I am here.
    Thank you.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,320
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: From macro to vba

    What do you need?
    Forget the macro and the code for a moment, you can explain with your words what you want to do.
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From macro to vba

    wow, thank you so much Mr. D a n t e A m o r, your offer is really Priceless.
    explain with your words what you want to do.
    Still I am in the same idea of counting my Dynamic Array.
    Code:
    Sub DanteAmor()    Dim r As Range, wMin As Double, wMax As Double, wRow As Long, wCol As Long, i As Long, j As Long
        
        Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
        wMin = WorksheetFunction.Min(r)
        wMax = WorksheetFunction.Max(r)
        r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents
        
        wRow = 2
        For i = wMin To wMax
            wCol = 4
            Cells(wRow, "C").Value = i
            For j = wMin To wMax
                Cells(1, wCol).Value = j
                Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
                wCol = wCol + 1
            Next
            wRow = wRow + 1
        Next
        MsgBox "Done"
    End Sub
    in this code, you know count one column and displays a matrix, so now I would like count a dynamic array of six columns (B2:G2600) and the numbers in this array is a set from one to 53 the maximun number.
    Code:
    B C D E F G
    1 15 17 30 42 44 49
    2 11 15 18 19 22 49
    3 8 15 22 25 40 23
    4 12 13 19 20 36 47
    5 3 5 7 13 19 48
    6 13 15 17 20 44 20
    7 3 4 10 23 38 48
    8 13 17 27 40 42 42
    9 2 6 11 14 31 47
    10 3 4 13 28 35 34
    11 1 2 3 13 28 46
    12 10 23 26 36 37 48
    13 12 18 20 32 38 42
    14 12 15 17 18 31 42
    15 7 14 15 22 37 42
    16 6 24 28 40 47 38
    17 6 20 26 29 42 48
    18 3 5 19 20 27 45
    19 2 10 27 31 33 35
    20 1 21 24 38 39 45
    21 5 17 21 38 41 44
    22 16 20 27 31 40 47
    23 12 17 30 43 44 41
    24 10 16 21 29 41 46
    25 3 11 14 15 27 43
    26 4 8 24 34 43 40
    27 4 11 12 19 31 45
    28 9 22 23 29 31 40
    29 7 15 24 30 31 44
    30 17 19 23 31 33 49
    and the matrix will be
    Code:
    O P Q R S T ETC
    FROM 1 2 3 UNTIL 53
    FROM 1
    2
    3
    UNTIL
    53
    Last edited by montecarlo2012; May 21st, 2019 at 05:47 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,320
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: From macro to vba

    And what is the result you expect in the matrix?
    You could explain a couple of examples with the data that you put.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From macro to vba

    Quote Originally Posted by DanteAmor View Post
    And what is the result you expect in the matrix?
    You could explain a couple of examples with the data that you put.
    So, Sorry DanteAmor
    1 2 3 4 5 6 7
    1 37 30 25 29 23 24 21
    2 30 21 25 26 15 16 11
    3 31 35 15 23 22 18 13
    4 29 21 22 21 25 16 11
    5 19 18 20 23 11 19 13
    6 24 20 21 14 14 9 18
    7 24 16 13 20 10 16 7
    8 20 9 11 7 13 10 2
    9 13 6 12 10 8 2 9
    10 10 2 14 13 5 8 10
    instead of search into one column, this time will count 6 columns and display how many time each number follow each number, the random numbers in this array are a set of 1 to 53. Thank you for your time.
    Last edited by montecarlo2012; May 21st, 2019 at 06:28 PM.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,320
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: From macro to vba

    You can explain how you get a 37 and how you get a 30


    1 2
    1 37 30
    2
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From macro to vba

    Quote Originally Posted by DanteAmor View Post
    You can explain how you get a 37 and how you get a 30


    1 2
    1 37 30
    2
    The fact is, those numbers are just an example, about how looks like.
    In other words if I don't have the code I really don't know what the real results will be.
    Sorry.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,320
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: From macro to vba

    Quote Originally Posted by montecarlo2012 View Post
    The fact is, those numbers are just an example, about how looks like.
    In other words if I don't have the code I really don't know what the real results will be.
    Sorry.
    But to make the code, I need you to explain how to get to the result.

    Forget the code for a moment.
    What do you want to do? Do you want to add, or to count, what does it mean?
    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From macro to vba

    Sorry about all this. and thank you for your patient.
    One post before, with Your code I was able to get the results from one column only. Now I would like to get the results no just from one column, I need to COUNT in each column.
    COUNT.
    More or less let say, is to expand your code, and instead to count only the column in the example, count the whole entire DYNAMIC array.
    Code:
    B C D E F G H I J K L M N 13
    1 12
    2 15 1 2 3 4 5 6 7 8 9 10 11 12
    3 11 1 37 30 31 29 19 24 24 20 13 10 8 5
    4 8 2 30 21 35 21 18 20 16 9 6 2 9 5
    5 12 3 25 25 15 22 20 21 13 11 12 14 8 8
    6 3 4 29 26 23 21 23 14 20 7 10 13 5 9
    7 13 5 23 15 22 25 11 14 10 13 8 5 9 9
    8 3 6 24 16 18 16 19 9 16 10 2 8 7 1
    9 13 7 21 11 13 11 13 18 7 2 9 10 4 6
    10 2 8 12 17 6 11 9 6 5 5 11 5 4 5
    11 3 9 16 11 14 13 3 8 7 6 4 3 4 3
    12 1 10 19 8 10 10 4 5 5 5 7 2 2 6
    13 10 11 10 9 8 8 6 7 5 5 5 2 3 2
    14 12 12 9 11 6 9 5 5 6 9 1 4 4 3
    15 12 13 9 10 9 11 10 3 3 6 5 3 5 1
    16 7 14 9 6 3 8 7 2 3 0 6 1 4 2
    17 6 15 12 9 4 3 8 0 8 2 5 7 1 2
    18 6 16 5 5 4 7 1 5 2 1 0 3 5 6
    19 3 17 4 3 4 1 2 2 4 0 3 4 1 0
    20 2 18 7 2 2 3 1 2 2 4 3 2 1 1
    21 1 19 0 0 4 0 3 4 4 3 0 1 1 2
    22 5 20 3 2 1 3 1 2 2 4 1 1 1 1
    23 16 21 4 2 3 1 2 1 1 2 2 0 0 0
    24 12 22 0 2 1 3 1 1 1 1 0 0 1 0
    25 10 23 1 2 0 0 4 4 0 0 0 0 1 1
    26 3 24 3 1 1 1 0 3 1 0 1 1 0 1
    27 4 25 0 2 0 0 1 1 1 0 0 0 0 1
    28 4 26 1 2 2 0 4 0 0 0 0 0 0 0
    29 9 27 0 0 0 0 0 0 0 0 0 1 0 0
    30 7 28 0 0 1 0 2 1 1 0 1 0 0 0
    31 17 29 1 0 1 0 0 0 0 1 0 0 0 0
    32 12 30 1 0 1 0 0 0 0 0 2 0 0 0
    33 8 31 0 0 1 1 0 0 0 0 0 0 0 0
    34 15 32 0 0 0 1 0 0 0 0 0 0 0 0
    35 1 33 0 0 0 0 0 0 0 0 0 0 0 0
    36 20 34 0 0 0 0 0 0 0 0 1 0 0 0
    37 2 35 0 0 1 1 0 0 0 0 0 0 0 0
    38 5 36 0 0 0 0 0 0 0 0 0 0 0 0
    AND THIS IS YOUR CODE FROM THE LAST POST.
    Code:
      Sub g_n()    Dim r As Range, wMin As Double, wMax As Double, wRow As Long, wCol As Long, i As Long, j As Long
        
        Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
        wMin = WorksheetFunction.Min(r)
        wMax = WorksheetFunction.Max(r)
        r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents
        
        wRow = 2
        For i = wMin To wMax
            wCol = 4
            Cells(wRow, "C").Value = i
            For j = wMin To wMax
                Cells(1, wCol).Value = j
                Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
                wCol = wCol + 1
            Next
            wRow = wRow + 1
        Next
        MsgBox "Done"
    End Sub
    Last edited by montecarlo2012; May 21st, 2019 at 08:45 PM.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,320
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: From macro to vba

    Try this.

    The result will begin in column I

    Code:
    Sub g_n()
        Dim r As Range, wMin As Double, wMax As Double, wRow As Long
        Dim cini As Long, wCol As Long, i As Long, j As Long
        
        Application.ScreenUpdating = False
        Set r = Range("B1", Range("G" & Rows.Count).End(xlUp))
        wMin = WorksheetFunction.Min(r)
        wMax = WorksheetFunction.Max(r)
        cini = r.Columns.Count + 3
        Range(Cells(1, cini), Cells(Rows.Count, Columns.Count)).ClearContents
        
        wRow = 2
        For i = wMin To wMax
            wCol = cini + 1
            Cells(wRow, cini).Value = i
            For j = wMin To wMax
                Cells(1, wCol).Value = j
                Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
                wCol = wCol + 1
            Next
            wRow = wRow + 1
        Next
        Application.ScreenUpdating = False
        MsgBox "Done"
    End Sub
    Note:
    For a matrix of: B2:G2600, the macro will take a long time.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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