Consulting

Results 1 to 2 of 2

Thread: VBA to insert text in adjacent cells

  1. #1

    Question VBA to insert text in adjacent cells

    Hi! I’m new-ish to VBA in Excel although I’ve been using it in Word for about a decade. I’m having an issue with my most ambitious project to date, the Excel-based data source for a mail merge. To input data in the spreadsheet, you fill in a user form. Depending on what you select in a combo box, we may need more information. If we do, another userform pops up when you click submit.

    The information from the first userform fills in columns A-I, the next userform is meant to fill in columns J-N. I’m having a lot of trouble with this, because when I complete the first record, it’s fine and A2:N2 are filled in, but for every consecutive record A:I fill in in new rows, but J2:N2 just overwrite. So a few questions:

    If I don’t have time to redesign before my meeting tomorrow, How can I make the data go in columns J:N of the active row?

    If I DO redesign using a multi page instead of 10 individual userforms, would I just list the code for columns J-N under the code for other columns? Would I have to list what goes in what column multiple times to account for the separate criteria that would be in the individual tabs? (Columns J-N are innumerated AdditionalInfo1, etc. but what goes in them is different depending on the question, determined by the blurb)
    I.e., if I have textboxes named C1AI1, C2AI1, C3AI1 where C1 is combobox choice 1 and also the page in the multi page and AI1 is indicative of the text input’s place on the spreadsheet (column J, aka Additional Info1) would I have to list them individually in my code that the text input from C1AI1, C2AI1 AND C3AI1 all go in the 10th column of the active row? I’m assuming yes, otherwise how would VBA know where to put the data?

    Thank you!!

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Welcome to the forum!

    Without pasted code or an attached short example file, I don't see how we could guess at what you need. Paste code betwee code tags. Click # icon on toolbar to insert code tags. Click Go Advanced button in lower right of a reply box and then the paperclip icon in toolbar or Manage Attachments button below reply box to attach a file.

    Think about adding a Tag property value to your controls to make your tasks easier. e.g. Textbox1.Tag = "J"
    Then:
    Cells(activecell.row, Textbox1.Tag).value = Textbox1.Value

Posting Permissions

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