paste value macro

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

hi all

just wondering if someone could help me write a macro
where is takes a random number from cell A1, then pastes
that value only into cell E1, then press F9 to refresh the
random number and then takes the new value in cell A1, and
pastes the value only into cell E2, and so on for 1,000
random numbers being pasted from E1 to E1000?

any clues?
 
I have some thoughts, but not sure exactly how to do it, will pla
around in Excel later and see if I can get it working.

Preliminary thoughts.

dim cellnumber
dim cellindicate
Sub Generate()
ScreenUpdate = false
For cellindicate = 1 to 1000
[A1] = RAND()
cellnumber = cellindicate
"[E" & cellnumber & "]" = [A1]
next cellindicate
Beep
End Sub

I might go play with that during my lunchbreak, not sure if I have tim
now. Hope it helps.

-Bo
 
Try this macro.

It generates teh random number internally, not from A1.
I have done this for speed, if it is not what you want please let m
know.

Sub InsertRandomNumbers()
Dim I As Integer
Dim calc As Integer
Application.ScreenUpdating = False
calc = Application.Calculation
Application.Calculation = xlManual
For I = 0 To 999
[E1].Offset(I, 0).Value = Rnd(50)
Next I
Application.Calculation = calc
Application.ScreenUpdating = True
End Su
 
This works. I can't beleive I worked out how to do it. :D Have a butto
that calls the macro Generate.

Module 1.

Dim cellnumber
Dim cellindicate

Sub Generate()
Application.ScreenUpdating = False
For cellindicate = 1 To 1000
Worksheets("Sheet1").Calculate
cellnumber = cellindicate
[Sheet1!A1].Select
Worksheets("Sheet1").Cells(cellnumber, 5).Value = [A1]
Next cellindicate
Beep
Application.ScreenUpdating = True
End Sub

Hope that helps you.

-Bo
 
Bob

Thanks for this! I pasted from Sub Generate() in... what
does the
Module 1.

Dim cellnumber
Dim cellindicate

bit do???

i actually have the random number now in Cell B15, if i
want the 1000 pasted values to go from E15 to E1014 how
would i do this?

THANKS!
 
Module 1 is my way of saying "Insert->New Module"

So that everything I type is in Module 1.

The 2 dim variables would be the reason (I think) why it hasn't paste
in E1-1000. I prefer to make all my variables global, so jus go to th
"Declarations" section of the module (the top) and paste those in.

As for getting it to a different Cell.

Here's the bits to change.

Dim cellnumber
Dim cellindicate

Sub Generate()
Application.ScreenUpdating = False
For cellindicate = 1 To 1000
Worksheets("Sheet1").Calculate
cellnumber = cellindicate
[Sheet1!B15].Select
Worksheets("Sheet1").Cells(cellnumber, 4).Value = [B15]
Click to expand...
Click to expand...
Click to expand...
Next cellindicate
Beep
Application.ScreenUpdating = True
End Sub

The first change is where the random number is. I made the macro selec
this cell, and work out how far to move.
The second change is how far the cell moves (cellnumber cells down, an
4 cells across (B->E = 4)) as well as changing where the random numbe
is inserted from.

That should be all. I'll check on my worksheet.

-Bo
 
Duhhhr. I just remembered.

It doesnt matter WHAT cell is selected when I start. So you can tak
out the Select bit.

Secondly, because it ALWAYS starts in A1, you need to do this to star
in E15 and go onwards.

Worksheets("Sheet1").Cells(cellnumber + 14, 5).Value = [B15]

cellnumber is the 1 to 1000, and adding 14 offsets down a further 1
rows. I forget all these little things. It'll work now.

-Bo
 
Back
Top