Forum Discussion

dnkinc's avatar
dnkinc
Copper Contributor
May 04, 2024

Automatically insert formatted dropdown rows as per cell value

I very much need some help with my current project as my knowledge of excel is limited.   I have been asked to create a spreadsheet for a survey.   I have a dropdown table where the surveyor can ...
excel
Formulas and Functions
Macros and VBA
Martin_Angosto's avatar
Martin_Angosto
Iron Contributor
May 04, 2024

dnkinc 

 

Hello there, please see attached document with proposed solution. Hope it works as intended.

 

It basically reads the value of a certain cell to understand how many rows to add, then it adds that amount of rows with specific text/formula. Note that you may change your sheet name, your table name, and the cell reference indicating how many rows to add.

 

Martin

  • dnkinc's avatar
    dnkinc
    Copper Contributor
    May 07, 2024

    Martin_Angosto 

     

    Many thanks for your response Martin, however when I open the file the formulas are missing?

     

    Is there any way that you can type the formula in here please?

     

    MT

    Nicki

    • Martin_Angosto's avatar
      Martin_Angosto
      Iron Contributor
      May 07, 2024

      dnkinc 

       

      I did not use any formulas. This functionality is using VBA code (macros). To be able to see it, you just have to enable macros on this particular file. Once done, enable the "Developer" tab for the ribbon by going to File > Options > Customized Ribbon > Select Developer on the right.

       

      Then, go to Developer > Macros and run the unique macro that it exists in the file. It automatically adds the rows based on the number value on cell B3.

       

      If you prefer, I can share the code in here as well:

      Sub AddRowsTotalAP()

Dim TotalAP As ListObject
Dim rowCount As Integer
Dim i As Integer

Set TotalAP = ThisWorkbook.Sheets("MySheet").ListObjects("TotalAP")

rowCount = ThisWorkbook.Sheets("MySheet").Range("B3").Value

For i = 1 To rowCount

    TotalAP.ListRows.Add
    
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 1).Value = "AP" & i
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 2).Value = "Location"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 3).Value = "Cable"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 4).Value = "Metres"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 5).Value = ""
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 6).Formula = "=INDIRECT(""R[0]C[-2]"",FALSE)*INDIRECT(""R[0]C[-1]"",FALSE)"

Next i

End Sub

       

      Any further comments do not hesitate to reply!

Resources