Scoring a model on 10k observations

[maddawg82]

Hey everyone,

I've got a [FONT=inherit !important][FONT=inherit !important]data [/FONT][FONT=inherit !important]set[/FONT][/FONT] with ~10k observations and 5 input variables on each observation. I've got a model that will take the 5 input variables and translate them into 5 output variables, that I want to analyze.

So I need a macro that will cycle through all 10k rows. I am thinking I just need to copy/past the five variables on tab1 into the model (on tab2), then copy paste the results back to tab1, then move down one cell and repeat.

How do I write the macro that moves down one cell and repeats? Let me know if this makes sense

Thanks!

 

[nuked]

It sort of makes sense, but could probably be achieved a lot easier.

Can you provide more details such as the structure of both sheets (tabs), and the formula/calcs used in sheet2?

Ta

 

[slinky]

This is an excerpt from a journal writing tool I built for our finance guys here.. It's probably written really badly, but it works nicely and (when run in context) writes 1500 journals a second (with a bunch of extra criteria outside of the pasted code), so I'm happy...

    Range("A65536").End(xlUp).Offset(0, 0).Select
    Rcount = ActiveCell.Row
Range("A1").Select
'Harvest data from Export
    Do While Rcount > 0
        PDateOld = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        TransType = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        GLOld = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        GLDesc = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        TransDesc = LCase(ActiveCell.Value)
        ActiveCell.Offset(0, 1).Select
        Amount = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        TransDesc2 = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        CostCentreOld = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        ProjectCode = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        GroupExecCode = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        PropCode = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        TransaxRef = ActiveCell.Value
        On Error Resume Next

'write data to Journals sheet

        Sheets("Journal").Select
            If Rcount = TotalR Then
            Range("A1").Select
            Else
            Range("A65536").End(xlUp).Offset(1, 0).Select
            End If
        ActiveCell.Value = PDate
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = "G/L Account"
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = GLOld
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = TransDesc
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = AmountNeg
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = CostCentreOld
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = ProjectCode
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = GroupExecCode
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = PropCode

'Bounce' tool for returning to export sheet and bouncing to start of next sheet line.
Bounce:
        Checkit = ""
        Sheets(ExportSheet).Select
        Bouncer = ActiveCell.Column
        Bouncer = (Bouncer - 2 * Bouncer) + 1
        ActiveCell.Offset(1, Bouncer).Select
        Rcount = Rcount - 1
Loop

Hopefully you should be able to repurpose some of that to get what you want to do?