+ Reply to Thread
Results 1 to 4 of 4

insert rows and insert text in the cells in the inserted row

Hybrid View

  1. 02-03-2023, 06:00 PM #1
    James Keuning
    James Keuning is offline
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    insert rows and insert text in the cells in the inserted row

    I am trying to improve my experience creating quizzes in a online gradebook course management software called D2L. This software has a CSV template for importing quizzes. The template has many types of questions, with specific rows and text for each question type. I only use three question types. I am fairly certain that if I can get started with a macro for one of the types, I can figure out the rest. The way I currently do this is when I want to add a question to the quiz, I copy the rows including two blank lines at the bottom from my template and then Insert Copied Cells where I want the question to appear. That works fine, but I know it can be faster.

    I imagine my workflow will be to select the row where I want to insert these populated rows, and then run the macro.

    This is a screen shot of the data in Excel.
    6AD0513F-F71F-4378-998E-EAAEB72A8C22_4_5005_c.jpeg

    This is the data in CSV form:
    NewQuestion,MC,,,
QuestionText,This is the question text for MC1,,,
Option,0,This is the correct answer,,
Option,0,This is incorrect answer 1,,
Option,0,This is incorrect answer 2,,
Option,0,This is partially correct,,
Feedback,This is the feedback text,,,
,,,,
,,,,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-04-2023, 11:43 AM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    8,036

    Re: insert rows and insert text in the cells in the inserted row

    It might be useful if you could show us a before and after view. Not a lot of information to go on with work out the expected output.

    BSB
    Reply With QuoteRegister To Reply
  3. 02-04-2023, 01:52 PM #3
    James Keuning
    James Keuning is offline
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: insert rows and insert text in the cells in the inserted row

    Before there is nothing. After there is the text that I show in the screen shot.

    If I imagine the way it could be done: I select a cell and run the macro. The macro inserts nine rows. It assigns the current cell the coordinate 0,0

    Then it add the string "NewQuestion" into 0,0
    It adds MC into 1,0
    It adds QuestionText into 0,1
    It adds "This is the question text for MC1" into 1,1
    It adds Option into 0,2
    It adds 0 into 1,2
    It adds "This is the correct answer 1" into 2,2
    It adds Option into 0,3
    It adds 0 into 1,3
    It adds "This is the correct answer 2" into 2,3

    And just fills up the area with the exact text every time. Alteratively if the macro could load the clipboard with those values, it could just insert the rows and then paste.
    Reply With QuoteRegister To Reply
  4. 02-04-2023, 02:15 PM #4
    James Keuning
    James Keuning is offline
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: insert rows and insert text in the cells in the inserted row

    After I typed out that last response, I decided to just mess around with recording a macro and then script monkeying the macro, and I came up with this. Is this the best way to do these kids of things?

    (EDIT: never mind, I am not there. These approach does not use relative cell references, how do I change the cell references to be relative to the cell that is selected when I run this?)
    (EDIT@: I find an approach that works. I am not sure if it's the best way though. Is this the best way to do this?)


    Sub MC()

    ActiveCell.Rows("1:9").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "NewQuestion"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "MC"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(1, -2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "QuestionText"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "This is the question text for MC"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(1, -2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Option"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "0"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Answer"
    ActiveCell.Offset(1, -2).Range("A1").Select
    
    ActiveCell.FormulaR1C1 = "Option"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "0"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Answer"
    ActiveCell.Offset(1, -2).Range("A1").Select
    
    ActiveCell.FormulaR1C1 = "Option"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "0"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Answer"
    ActiveCell.Offset(1, -2).Range("A1").Select
    
    ActiveCell.FormulaR1C1 = "Option"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "0"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Answer"
    ActiveCell.Offset(1, -2).Range("A1").Select

    
    ActiveCell.FormulaR1C1 = "Feedback"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "This is the feedback text"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(1, -2).Range("A1").Select
    
    
End Sub
    Last edited by James Keuning; 02-04-2023 at 05:14 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to insert different number of rows and drag values from the row before inserted?
    By Ekaterina26 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-08-2016, 10:31 AM
  2. [SOLVED] Insert Row and Add Text to new cell of Inserted Row
    By Hood in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2016, 07:51 AM
  3. [SOLVED] VBA codes to insert the rows and copy the first entire row text and insert sheet
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2013, 05:05 AM
  4. Macro: Copy 3 Rows & Insert below with Format....Also hide last two rows inserted
    By AWiT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2013, 12:04 PM
  5. Automatically Insert Columns as a Result of Inserted Rows
    By The Exceller in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-01-2012, 09:35 AM
  6. Insert row, modify original & inserted rows
    By 1cor1_27 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2011, 05:00 PM
  7. [SOLVED] automatically insert formula when new rows are inserted
    By BLUEJAY in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-27-2006, 05:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1