Solved

Automatically add an index

Posted on 2013-06-22
4
249 Views
Last Modified: 2013-06-23
I'm trying to figure out a way to automatically add an ampersand along with a unique number when I transfer my row of data from one sheet to another.  On the Matrix worksheet the column SHOP has three initials. My cut and paste vba code transfers anything with something in column A & B. When the vba moves my data to Print worksheet I need it to look at the number IDs in column A and add the next corresponding ID following an ampersand. My example is listed below in the attached link.  I would also like to add a conditional format that colors the numbers and ampersand white so it isn't visible
Add-Unique-Identifier.xlsm
0
Comment
Question by:Southern_Gentleman
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 400 total points
ID: 39269005
0
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 100 total points
ID: 39269018
I took the liberty of revising your code in some parts, in particular where the cell referencing wasn't clear. Bear in mind that a naked Cells(1, "A") refers to the Activesheet, even if it is embedded in something like ShData.Range(Cells(1, "A")). If it so happens that the ActiveSheet isn't ShData the result may be perplexing.
I also added "Option Explicit" to your sheet and declared a variable you had left out. And, finally, I moved Application.CutCopyMode = False to after you had used it rather than before. You don't need to Cut because you are deleting the entire row in the next step.
To the such revamped code I added a few rows to do what you asked. Please repalce your existing code with the following:-
Option Explicit

Sub TransferIt()

    Dim shData As Worksheet
    Dim shDetail As Worksheet
    Dim Response As VbMsgBoxResult
    Dim IdxRow As Long
    Dim TgtRow As Long
    Dim Idx As Long
    
    Response = MsgBox("You are about to move items to the Print Sheet?", vbYesNo)
    If Response = vbNo Then Exit Sub
    
    Set shData = Worksheets("Matrix")
    Set shDetail = Worksheets("Print")

'    On Error Resume Next
    With shDetail
        TgtRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Idx = GetIndex(TgtRow, shDetail)
    End With
    
    With shData
        For IdxRow = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
            If WorksheetFunction.IsText(.Cells(IdxRow, "A").Value) And _
                                 IsDate(.Cells(IdxRow, "B").Value) Then
                TgtRow = TgtRow + 1
                Idx = Idx + 1
                Range(.Cells(IdxRow, "A"), .Cells(IdxRow, "R")).Copy _
                       Destination:=shDetail.Cells(TgtRow, "A")
                With shDetail.Cells(TgtRow, "A")
                    .Value = .Value & "&" & CStr(Idx)
                    .NumberFormat = ";;;"
                End With
                .Cells(IdxRow, 1).EntireRow.Delete Shift:=xlUp
                Application.CutCopyMode = False
            End If
        Next
    End With
End Sub

Private Function GetIndex(ByVal R As Long, _
                          Ws As Worksheet) As Long
    Dim S() As String
    
    If R > 1 Then
        S = Split(Ws.Cells(R, "A").Value, "&")
        If IsNumeric(S(UBound(S))) Then
            GetIndex = S(UBound(S))
        End If
    End If
End Function

Open in new window

0
 

Author Closing Comment

by:Southern_Gentleman
ID: 39269774
Thanks Faustulus and als315. For Faustulus, I didn't want the items in the 'Print' sheet to be deleted so I kept the CutCopyMode=True and did an accumulation of what was already on the sheet.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39270064
Southern_Gentleman,
Thank you for giving me some of the points.
After you cut or copy an area in a worksheet manually you will still see that area marked, indicating that it is still on the clipboard and, should you choose to paste again, another copy will be made whereever you might be pointing the cursor. That is the CutCopyMode indicator which, in VBA, you remove setting Application.CutCopyMode = False. It is created by a preceding Cut or Copy command and is quite unrelated to deletion.
Some programmers will set this mode to False before they cut  or copy. That action is aimed at any manual cut or copy action that might have preceded running of the code. In fact, the mode is set or reset automatically with the Cut or Copy command. Once the cutting or copying has been accomplished a clean slate should be left behind, and that includes setting to CutCopyMode to False.
My code doesn't delete any items in the Print sheet.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now