Results 1 to 11 of 11

Loop through Column B msgboxYesNo if Yes copy to Column C, if No copy to Column D

Threaded View

WBravard Loop through Column B... 11-05-2017, 07:37 PM
alansidman Re: Loop through Column B... 11-05-2017, 08:02 PM
Maudibe Re: Loop through Column B... 11-05-2017, 09:21 PM
WBravard Re: Loop through Column B... 11-06-2017, 12:24 AM
Maudibe Re: Loop through Column B... 11-06-2017, 01:20 AM
WBravard Re: Loop through Column B... 11-06-2017, 08:31 AM
Maudibe Re: Loop through Column B... 11-06-2017, 10:31 PM
Maudibe Re: Loop through Column B... 11-07-2017, 12:20 AM
WBravard Re: Loop through Column B... 11-07-2017, 08:52 AM
Maudibe Drag and Drop names 11-08-2017, 10:17 PM
WBravard Re: Loop through Column B... 11-09-2017, 09:46 AM
  1. #1
    Registered User
    Join Date
    08-09-2017
    Location
    Indiana
    MS-Off Ver
    Execl 2013, Windows 10
    Posts
    23

    Post Loop through Column B msgboxYesNo if Yes copy to Column C, if No copy to Column D

    I am still very new and can't figure out how to accomplish this. I have a spreadsheet (attached) with Names in Column B that are not uniform, some start with Last Name, some start with First Name, some have MI some do not, some have SR JR some do not.

    First, I'd like to setup VBA/macro starting on Column B Row 2, Select & Copy the field, Ask "Does XXXX start with Last Name? (ie XXXX displays the field value), If answer Yes copy the field value to Column C of the same row, then go to the next line down, If answer No copy the field value to Column D of the same row, then move to the next line down and repeat the above steps again until the last row in Column B is not populated. I have multiple spreadsheets - all with different # of rows.

    Unfortunately I have had issues getting the name of the cell to display in the message, and I am not understanding the looping through to get it to continue after the Yes or No to the next lines. I get errors ALL OVER. Can anyone guide me to get this to work?

    Second, I plan to record the text to column and split all the values, then add to the above. I think I can handle this part by recording a macro and using that to get it to work. After the above is working.


    HERE's some of the VBA I can't get to work:


        'Copy Rows in Column B that start with last name to  Column C, Copy rows in Column B that start with first namne to Column D
        
        Range("B2").Selection Copy
        MsgBox "Does this Name Start with Last Name?. If yes place in Column C, If no place in Column D.", vbYesNo
        Answer2 = MsgBox("Does this name C3 start with the members Last Name?", vbYesNo, "Copy to Last/First name columns")
            If Answer = vbNo Then
                Range("D1").Selection Copy
                 Exit Sub
            Else: Answer = vbYes
                Range("C1").Selection Copy
                sheets(AuditSheet).Sort.SortFields.Clear
                sheets(AuditSheet).Sort.SortFields.Add Key:=Range( _
                    "B2:B1460"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                    xlSortNormal
               With ActiveWorkbook.Worksheets(AuditSheet).Sort
                    .SetRange Range("A1:AZ4000")
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
        
           ActiveWorkbook.SaveAs (AuditSheet + "AFTMACRO")
        
        
     End If
    End Sub
    Attached Files Attached Files
    Last edited by alansidman; 11-05-2017 at 08:02 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. loop through range to copy row if data exists in column, then delete the column
    By wpryan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2015, 05:03 PM
  2. Replies: 1
    Last Post: 05-30-2015, 04:38 PM
  3. Replies: 5
    Last Post: 11-17-2014, 02:40 PM
  4. Copy data from column K to column B if Column C and column I match
    By goog2013 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2013, 12:19 PM
  5. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  6. [SOLVED] Loop-copy cell values from a row, and then copy them into a column
    By vukovicnikola in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2012, 06:03 AM
  7. Replies: 1
    Last Post: 05-06-2011, 03:08 AM

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