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
    1. Sub FillRangeWithRandomNumbers()
    2. Dim Col As Long
    3. Dim Row As Long
    4. For Col = 1 To 5
    5. For Row = 1 To 12
    6. Cells(Row, Col) = Rnd
    7. Next Row
    8. Next Col
    9. 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?

    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
    1. 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 "Thumbs Up" icon, below, in the bottom right corner:)

    Rather Curious to get your feedback ...


    Code
    1. Sub FillRangeWithRandomNumbersEnhanced()
    2. Dim Col As Long
    3. Dim Row As Long
    4. For Col = 2 To 11
    5. For Row = 2 To 11
    6. Cells(Row, Col) = WorksheetFunction.RandBetween(1, 100)
    7. Range("K14") = Range("K14") + Cells(Row, Col)
    8. Next Row
    9. Next Col
    10. End Sub

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

    Is this what you are trying to do?


    Code
    1. Sub FillRangeWithRandomNumbersEnhanced()
    2. Dim Col As Long, Row As Long, RunTot As Integer
    3. For Col = 2 To 11
    4. For Row = 2 To 11
    5. Cells(Row, Col) = WorksheetFunction.RandBetween(1, 100)
    6. RunTot = Cells(Row, Col) + RunTot
    7. Range("K14") = RunTot = Application.WorksheetFunction.Sum(Range("B2").CurrentRegion)
    8. Next Row
    9. Next Col
    10. 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 "Thumbs Up" icon, below, in the bottom right corner:)

    Hello again,


    Just for the Fun of it ... attached is your Test file ... in a ' generic ' mode ...;)


    Hope you will like it ...

    Files

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

    Just to explain why you are seeing False there:


    Code
    1. 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
    1. 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
    1. RunTot = Cells(Row, Col) + RunTot
    2. 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

    @ 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 "Thumbs Up" icon, below, in the bottom right corner:)

  • Post by nickimiller ().

    This post was deleted by Carim: Spam ().

  • Post by kimilronalds ().

    This post was deleted by royUK: spam ().

  • Post by Marvel09 ().

    This post was deleted by rory: spammer ().

  • Post by siajewellery ().

    This post was deleted by Carim: Spam ().

  • rory

    Closed the thread.