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.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]

...

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]

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