[Closed] Excel macro help pleas

You'd want to copy the cells and then change sheet, select the top cell of the new sheet then complete an XLDOWN and then a plus one offset to select the next cell down and do a paste special into that cell.

I think a FOR NEXT loop will do it.

Its been a while since i've done something like this but i'd start by recording a macro to copy and past one row then edit the Visual Basic code.

FOR NEXT loops are really simple and will enable you to programme this with effectively one line of code. Set a variable to have a value from 1 to 1000 so as the code returns to the top, indexes the variable and the row reference and continues until the variable completes the 1000'th cycle.

I've always found Visual Basic help very useful.

Cheers. Not sure I understand this. What would it look like in code form?

Sorry for being a numpty

I need to understand the question better. I think you want to have Excel calculate some results, in a1 and a2, copy the results to sheet2, refresh the values, copy the new results to sheet2 on the line below, 1000 times. What triggers the refresh of the values - is it a button you click, do you type an initial value, something automatic, or what? The reason I ask is because that trigger is probably what needs to be captured to initiate the copy and paste (which would be done like somouk says)

I can't construct the exact code/syntax now, I would have to mess around in Excel for 10 mins or so. But it would be constructed something like this:-

FOR X = 1 TO 1000 (row number increment)
FOR Y = 1 TO 2 (column number increment)

copy data from sheet1 cell aY
select sheet2
select cell X,Y
paste the data

NEXT Y (to increment column and repeat the copy and paste routine with column reference incremented)
NEXT X

So this is a nested loop so you'll remain in the Y loop until you get to the value 2, then it drops out of the Y loop into the Next loop until the value of X reaches 1000.

Like I said before i'd start by recording a simply copy and paste macro to get a starter code that gives you the code to switch between sheets and copy and paste date from a cell on one worksheet to another worksheet, then edit that code to include the FOR NEXT loops. So a bit of a trial and error thing, but i'm not that great at coding - I can usually get there and have written some long and reasonably complex VB code in my distant past when I was in a job that involved a lot of data analysis, but i'm a good few years out of that now.

Edit: I'm assuming you want to copy results from cells a1 and a2 in sheet1 and paste them into a list of answers 1000 rows long in sheet2? You'd also need to include into this code the execution of the calculation to change the answers in sheet 1 cells a1 & a2 or you'll end up with a list 1000 rows long of the same answer!

The trigger is a "calc now". The numbers in a1 and a2 have a random number element. I want to run it 1000 times and then see what the spread of results look like.

I can't drag and copy within sheet 1 as there are a fair few calls needed to get the result.

Does that make sense?

Whilst solution is not yet in sight, I challenge anyone to give a reason why this place isn't the best thing on the web.

From excel macros to litigation against a ferret for medical negligence, there's usually several knowledgeable folk on hand here, for free and with good intent. Where else does that happen?!

Email the workbook with the calculations to me if you like.

Why do you need a macro? Your result will be a list of 1000 random numbers in cells. Why not fill down your random number function, then copy the whole lot and paste as values to sheet 2? Same result if I understand your request properly.

^ That's kinda what I was thinking. Often do something other than the requestor asks for to get the desired result...

Well, I got this far. It works but runs very very slow so, is there a better construct? :o)

Dim i As Integer
Dim NPV As Integer
Dim AMP6 As Integer
Dim AMP7 As Integer
Dim AMP8 As Integer

For i = 2 To 5000

' select the data from the calculation sheet

NPV = Sheets("Catchment solution").Range("c154").Value
AMP6 = Sheets("Catchment solution").Range("c155").Value
AMP7 = Sheets("Catchment solution").Range("c156").Value
AMP8 = Sheets("Catchment solution").Range("c157").Value

' paste the data for use in the percentile calculation

Sheets("Monte Carlo Model").Cells(i, 1).Value = NPV
Sheets("Monte Carlo Model").Cells(i, 2).Value = AMP6
Sheets("Monte Carlo Model").Cells(i, 3).Value = AMP7
Sheets("Monte Carlo Model").Cells(i, 4).Value = AMP8

Next i
End Sub

So now your doing 5000! That is the way to do it but...
What's the formula in c154? Is it using randbetween?

Yeah the 5000 is me testing the data!

The cell is an npv of the capex and Opex of 30 schemes. The schemes are randomly selected in time based on a likelihood of occurrence. So I can't just copy down 1000 (or 5000!) rows as I need to see what the programme cost is each time.

Well I could but I'd have 1000 x 30 x 2 rows which is a bit big.

Excel is a dangerous tool I think!

Excel is a dangerous tool I think!

Not as dangerous as some of the tools who use it

The below prevents unnecessary re-calculation and, most importantly no rendering to screen - which *should* speed things significantly.

It's annoying how the indentation will be lost here...

Option Explicit
Sub Test()
Dim i As Integer, NPV As Integer, AMP6 As Integer, AMP7 As Integer, AMP8 As Integer

With Application
.ScreenUpdating = False
Calc_Man

For i = 2 To 5000
'Select the data from the calculation sheet
With Sheets("Catchment solution")
.Calculate
NPV = .Range("c154").Value
AMP6 = .Range("c155").Value
AMP7 = .Range("c156").Value
AMP8 = .Range("c157").Value
End With

'Paste the data for use in the percentile calculation
With Sheets("Monte Carlo Model")
.Cells(i, 1).Value = NPV
.Cells(i, 2).Value = AMP6
.Cells(i, 3).Value = AMP7
.Cells(i, 4).Value = AMP8
End With
Next i

Calc_Auto
.ScreenUpdating = True
End With
End Sub
Sub Calc_Man()
'Turns on manual calculation'
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub
Sub Calc_Auto()
'Turns on auto calculation'
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

For larger amounts of data I'd probly try cramming it all into an array and then splatting it all on to the second worksheet at once to speed it up*, but here it shouldn't matter.

*Not that this always works...

cool, cheers rob.

It's working and I'm getting results out which is great. Thanks sir.

Yes, a little knowledge is a dangerous thing!

No worries, HTH