Macro to fill in a static form with formulas using the next row of a reference sheet

[Lmel58]

Hi Gurus,
I am fairly new to macros in t hat its been a while since i have worked with them and have forgotten alot....
I have a dynamic form in worksheet "Part Form" and a data dump from an app in worksheet "App Data import"
I have formulas in the static cells fill in the "Answers" from the app Data import that all reference the same row number but am looking to get a macro if possible whereby if I run the macro it tells the formulas to reference the next row down in all of the formulas.
I hope this makes sense and I apologise if I haven't included all things required.

L

[kvsrinivasamurthy]

No macro required.
I have changed all formulas. Spin button added to O4 cell. It is linked to P4 cell. When the spin button is operated P4 changes.
If P4=3, 3rd row data will be selected
If P4=8, 8th Row and so on.

[Lmel58]

Thanks so much, this works great, I will have to apply the formula changes to all of the "answer cells in the full form document to test its full functionality and cannot thank you enough for your assistance.

[BadlySpelledBuoy]

Or perhaps this:

Sub LoopThroughRows()
    Dim i As Long
    
    With ThisWorkbook.Sheets("part Form")
        For i = 2 To ThisWorkbook.Sheets("App Data Import").Cells(Rows.Count, 1).End(xlUp).Row
            'PROJECT DETAILS
            .Range("G11").Formula = "=LEFT('App Data Import'!A" & i & ",10)"
            .Range("G12").Formula = "=LEFT('App Data Import'!A" & i & ",FIND("":"",'App Data Import'!A" & i & ")-1)"
            .Range("G13").Formula = "=MID('App Data Import'!A" & i & ",6,100)"
            .Range("G14").Formula = "='App Data Import'!B" & i
            
            'CONTRACTOR DETAILS
            .Range("G18").Formula = "='App Data Import'!C" & i
            .Range("G19").Formula = "='App Data Import'!D" & i
            .Range("G21").Formula = "='App Data Import'!E" & i
            .Range("G22").Formula = "='App Data Import'!F" & i
            
            'ASSESSMENT DETAILS
            .Range("G25").Formula = "='App Data Import'!G" & i
            .Range("G26").Formula = "='App Data Import'!H" & i
        
            'PRESUMABLY YOU WANT TO DO MORE THAN JUST ITERATE THE ROW NUMBER?
            'PRINT OR SAVE THE FORM FOR EXAMPLE.
            'IF SO, ADD YOUR CODE TO DO STUFF HERE
        Next i
    End With
End Sub

BSB

[BadlySpelledBuoy]

Just out of interest, which approach is the one that did what you need?

BSB

[Lmel58]

Hi BSB, so far I have used spin button solution, I haven't tried out the code you gave me yet but I will try it and let you know how it goes (or ask many more questions LOL)
Thanks again

[BadlySpelledBuoy]

I wasn't sure what it actually was you were trying to do with the data. If just skipping from one record to another is the aim then the SpinButton approach is certainly an effective method.
For some reason I had it in my head that you were printing multiple invoices or similar hence the code to loop through all records and perform an action on each.

If you're using this workbook like a database, i.e. the form acts as a front end for viewing and editing the data, then you might want to look into UserForms. They give a much more polished feel to this type of project and often make things easier.

BSB

[Lmel58]

Hi BSB,
I will be needing to use the code as the Data dump from the app doesn't like the workbook to contain the spin button. The actual form is 131 field entries so its going to be a long code......
If I have issues may I grab your help when you are available?

[kvsrinivasamurthy]

I will be needing to use the code as the Data dump from the app doesn't like the workbook to contain the spin button. The actual form is 131 field entries so its going to be a long code.....

If you have problem with spin button remove the button and enter the row number in P4 directly. It works.

[BadlySpelledBuoy]

Sorry, only just seen this reply. Of course, happy to help.

Depending on what the formulas you use and the layout of the form there may be better methods than my suggestion above, but won't know for sure without seeing it in context and having some more of an idea on what you're trying to achieve.

BSB