What am I doing wrong

    Working my way through the Excel VBA for dummies book which is quite fun, inputting one of the exercises as follows:


    Code
    Sub FillRangeWithRandomNumbers()
Dim Col As Long
Dim Row As Long
For Col = 1 To 5
For Row = 1 To 12
Cells(Row, Col) = Rnd
Next Row
Next Col
End Sub

    This works just as I'd expect it to. I then decided to spice it up a little as follows:





    This, sort of does as I'd expected but returns FALSE in K14 rather than the total of the random numbers. Can someone please explain to me what I'm doing wrong?

    Edited once, last by Carim: Added Code Tags ().

    Hello And Welcome to the Forum :)


    Just added Code Tags to your macros ... to make things much more easier to read ...;)


    Regarding your question ... you probably need for your Line #8


    Code
    Range("K14") = Range("K14") + Cells(Row, Col)


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Rather Curious to get your feedback ...


    Code
    Sub FillRangeWithRandomNumbersEnhanced()
Dim Col As Long
Dim Row As Long
    For Col = 2 To 11
        For Row = 2 To 11
            Cells(Row, Col) = WorksheetFunction.RandBetween(1, 100)
            Range("K14") = Range("K14") + Cells(Row, Col)
        Next Row
    Next Col
End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Is this what you are trying to do?


    Code
    Sub FillRangeWithRandomNumbersEnhanced()
    Dim Col As Long, Row As Long, RunTot As Integer
    For Col = 2 To 11
        For Row = 2 To 11
            Cells(Row, Col) = WorksheetFunction.RandBetween(1, 100)
            RunTot = Cells(Row, Col) + RunTot
            Range("K14") = RunTot = Application.WorksheetFunction.Sum(Range("B2").CurrentRegion)
        Next Row
    Next Col
End Sub

    Thanks for your Like :thumbup:


    Based on your code ... there is no need for a running total .... since it is immediately erased by your macro ... a simple Sum is enough


    Understand you are on a learning path ... so ... the Fun part would be turn it into a fully generic macro ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Just to explain why you are seeing False there:


    Code
    Range("K14") = RunTot = Cells(Row, Col) + RunTot


    You have two = signs there, which means that VBA will interpret what is to the right of the first = sign as an expression to be evaluated. Now, as has already been stated, RunTot is never actually calculated in the code, so it will have the default value of an integer, which is 0. So the expression being evaluated is:


    Code
    0 = Cells(Row, Col) + 0


    which will evaluate to True if the value of Cells(Row, Col) is 0, and False otherwise. That result is then put into K14.


    If you actually wanted to store the running total in a variable, you need to split that into two lines:


    Code
    RunTot = Cells(Row, Col) + RunTot
Range("K14") = RunTot


    although, as Carim pointed out earlier, there really isn't a lot of need for a variable here.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Love 2

    @ rory


    Thanks Rory for your clear and helpful explanation ...


    You are right ... it should have been my initial reaction ...:)



    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Good News ...


    The whole Objective of the Ozgrid Forum : Help People out ...!!!:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login