Excel VBA Macro Question

[asciiletters]

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.Range("B15:B20").PasteSpecial Paste:=xlPasteValues
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).Range("C15:C20").PasteSpecial Paste:=xlPasteValues
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.Range("D15:D20").PasteSpecial Paste:=xlPasteValues
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.Range("E15:E:20").PasteSpecial Paste:=xlPasteValues
End If
Loop Until ([E8] < 40) And ([E8] > 38)
End Sub

[kadghar]

...

Here's the code:

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

I'll make some changes in the first one, try it with the rest

Do
    Calculate
    If cells(2,8) < 40 And cells(2,8) > 38 Then
        Range("B4:B9").copy
        cells(2,15).pastespecial -4163
        exit do
    End If
Loop

[asciiletters]

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.

[kadghar]

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