Excel VBA Macro Question

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • asciiletters
    New Member
    • Oct 2007
    • 2

    Excel VBA Macro Question

    I have a spreadsheet with cells that use randbetween(). At the bottom of each column there is a sum() cell for these cells. I made a macro that refreshes the values until a column's total is within a certain number range. Each time a sum cell is true to this criteria it should "paste special" the column's values to a range of cells in a blank table corresponding to it. It doesn't seem to work though. It never paste's anything and contiues to generate random values even though I see it hitting the correct numbers.

    Here's the code:

    Sub GEN()
    Do
    Calculate
    If ([B8] < 40) And ([B8] > 38) Then
    Range("B4:B9"). Select
    Selection.Copy
    ActiveSheet.Ran ge("B15:B20").P asteSpecial Paste:=xlPasteV alues
    End If
    Loop Until ([B8] < 40) And ([B8] > 38)


    Do
    Calculate
    If ([C8] < 40) And ([C8] > 38) Then
    Range("C4:C9"). Select
    Selection.Copy
    Worksheets(1).R ange("C15:C20") .PasteSpecial Paste:=xlPasteV alues
    End If
    Loop Until ([C8] < 40) And ([C8] > 38)


    Do
    Calculate
    If ([D8] < 40) And ([D8] > 38) Then
    Range("D4:D9"). Select
    Selection.Copy
    ActiveSheet.Ran ge("D15:D20").P asteSpecial Paste:=xlPasteV alues
    End If
    Loop Until ([D8] < 40) And ([D8] > 38)


    Do
    Calculate
    If ([E8] < 40) And ([E8] > 38) Then
    Range("E4:E9"). Select
    Selection.Copy
    ActiveSheet.Ran ge("E15:E:20"). PasteSpecial Paste:=xlPasteV alues
    End If
    Loop Until ([E8] < 40) And ([E8] > 38)
    End Sub
  • kadghar
    Recognized Expert Top Contributor
    • Apr 2007
    • 1302

    #2
    Originally posted by asciiletters
    ...

    Here's the code:

    Sub GEN()
    Do
    Calculate
    If ([B8] < 40) And ([B8] > 38) Then
    Range("B4:B9"). Select
    Selection.Copy
    ActiveSheet.Ran ge("B15:B20").P asteSpecial Paste:=xlPasteV alues
    End If
    Loop Until ([B8] < 40) And ([B8] > 38)
    ...
    I'll make some changes in the first one, try it with the rest

    [CODE=vb]Do
    Calculate
    If cells(2,8) < 40 And cells(2,8) > 38 Then
    Range("B4:B9"). copy
    cells(2,15).pas tespecial -4163
    exit do
    End If
    Loop[/CODE]

    Comment

    • asciiletters
      New Member
      • Oct 2007
      • 2

      #3
      Thanks for the quick response. When I looked over my code later with a fresh head I realized that I inputted the wrong sum cell location, so my code works. When I ran yours it kept generating rands. Are you sure that the cells() function works like that? I tried it with .value but that didn't work either.

      Comment

      • kadghar
        Recognized Expert Top Contributor
        • Apr 2007
        • 1302

        #4
        Originally posted by asciiletters
        Are you sure that the cells() function works like that?
        Actually im sure they dont work like that i wrote columns first, then rows, it should be rows then columns.

        For B8 its cells(8,2)

        my apologies :$

        Comment

        Working...