+ Reply to Thread
Results 1 to 2 of 2

Attempting to Add Column with Corresponding Sheet

  1. #1
    Registered User
    Join Date
    06-08-2018
    Location
    Maryland
    MS-Off Ver
    2010
    Posts
    1

    Attempting to Add Column with Corresponding Sheet

    Hello, I'm new to VBA. I'm trying to search row 5 of a sheet and after every time a cell contains the phrase "Skymaster", a new column is inserted to the right with a InputBox-specified name. I would then like to add a sheet to the workbook with the same InputBox-specified name. Here is the code I have so far; for some reason it works the first time, but after that I get a 1004 error.

    Private Sub CommandButton1_Click()
    Dim givenname
    For a = 1 To ActiveSheet.Cells(5, Columns.Count).End(xlToRight).Column
    If ActiveSheet.Cells(5,a).Value = "Skymaster" Then
    ActiveSheet.Columns(a+1).Insert
    givenname = InputBox("Enter Name of New Aircraft", "New Aircraft")
    Cells(5, a + 1).Value = givenname
    a = a + 1
    End If
    Next a
    Dim WS As Worksheet
    Set WS = Sheets.Add
    Sheets.Add.Name = givenname
    End Sub

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: Attempting to Add Column with Corresponding Sheet

    Welcome to the forum!

    Off the bat, there are two quick issues that jump out at me. First, your For-Next loop runs from a=1 to a=LastColumn, but the moment you hit a Skymaster and insert a column to the right, then the LastColumn in your loop is no longer the actual LastColumn. In cases like this, it's usually smarter to move from a=LastColumn to a=1 using Step -1, so that you're moving backwards through the columns and inserting columns behind the direction of your loop. Secondly, you're only adding worksheets after the loop is complete, so if there are multiple Skymaster entries, it doesn't look like you'd currently be adding multiple sheets.

    More importantly, perhaps, diagnosing and updating code by sight and with incomplete context is inexact and inefficient. The best way to get useful help is to attach a small sample workbook (use Go Advanced > Manage Attachments to open the uploader). Make sure you remove any private data. Using an attachment, we should be much better able to see what the error is pointing to and what the procedure is/isn't doing as intended.

    Format Note: We also have a rule about using CODE tags when posting chunks of VBA. Could you please edit your post, select the text that comprises the VBA procedure, and click on the hashtag/pound/# icon to wrap the selected text in code tags? Thanks!
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Attempting to reformat layout of excel sheet
    By jackthegeographer in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 11-04-2017, 05:39 PM
  2. attempting to activate a particular sheet
    By markjohnconley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-21-2016, 11:30 PM
  3. Replies: 3
    Last Post: 03-30-2015, 05:44 PM
  4. Replies: 0
    Last Post: 06-26-2014, 11:31 AM
  5. Attempting to copy date from one sheet to another
    By jvonier in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-15-2014, 09:10 AM
  6. attempting to pull data from column G conditional of name in Column A
    By exceln00b123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-01-2013, 08:04 PM
  7. Replies: 0
    Last Post: 02-24-2012, 02:46 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1