First is my latest "rant".
I'll soon hit 100,000 lines of VBA code in Excel applications that many run
daily.
I'm a member of several VBA groups and enjoy sharing my experiences with
others to help with their problems. I particularly enjoy "puzzles", problems
that intrique me and offer a challenge.
In most of the User Groups, there are "tips" on how to post a question.
One such tip is that Subject lines should describe the problem.
Imagine an email inbox that looks like:
$$Excel-Macros$$ Create box and copy information to it
$$Excel-Macros$$ IF Function
$$Excel-Macros$$ Help Required
$$Excel-Macros$$ Creating an XPath function in Excel
$$Excel-Macros$$ Code to enter value in multiple worksheets
$$Excel-Macros$$ Help!?
$$Excel-Macros$$ Problem subtracting Dates
which do you suppose would interest the other members enough to look further?
NORMALLY, I simply delete those that say: "Help Required", or "URGENT!"
Unless I happen to be bored and the other messages have ongoing discussions.
So, my first advice to you is to take a minute to provide a subject line
that describes your problem in such a way as to capture the attention of
people with experience with your problem.
(If you have a problem with Pivot Tables, you'd PREFER to have someone that
knows about them answer your question instead of the guy that uses Excel
as a front-end to an Access database)
Second, since you've posted in a Excel MACRO group, I'll assume you're
looking for a Macro solution...
I've got one for you:
This utilizes a "Dictionary" object.
which, essentially is a single-dementioned indexed array.
The nice thing is that it is VERY fast.
I've loaded over 90,000 values in it before and it can locate the value nearly
instantaneously!
Option Explicit
Sub Tag_Data()
Dim Dict_Data, RowCnt, inx, Col_Data, stat, I
Set Dict_Data = CreateObject("Scripting.Dictionary")
stat = Dict_Data.RemoveAll
'===========================================================
RowCnt = ActiveCell.SpecialCells(xlLastCell).Row
inx = 0
Col_Data = 2
For I = 2 To RowCnt
If (ActiveSheet.Cells(I, Col_Data) & "X" <> "X") Then
If (Not Dict_Data.exists(ActiveSheet.Cells(I, Col_Data).Value)) Then
inx = inx + 1
Dict_Data.Add Trim(ActiveSheet.Cells(I, Col_Data).Value), inx
ActiveSheet.Cells(I, Col_Data - 1) = inx
Else
ActiveSheet.Cells(I, Col_Data - 1) =
Dict_Data.Item(ActiveSheet.Cells(I, Col_Data).Value)
End If
End If
Next I
End Sub
Give it a try.
Of course, you'll have to change the Col_Data = ## value to be the column that
has your data!
Paul
________________________________
From: Mike <michael.os...@navy.mil>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Wed, August 25, 2010 3:26:31 PM
Subject: $$Excel-Macros$$ Help!?
Thanks in advance!
1. I am using MS Excell 2007
2. Problem: I have a Column of serial #'s and they are listed low to
high. Some of the Serial # are listed once, some twice and some 3
times.
3. I want to alternate shading for each set of the same serial #'s.
EX:
276CHK0130
276CHK0130
276CHK0131
276CHK0131
276CHK0131
276CHK0132
276CHK0132
276CHK0132
276CHK0133
Does anyone know how this can be done? Is there a formula I can use
for CONDITIONAL FORMATTING?
Also, I would like to add a sequential # in a column left of the
Serial #.
EX:
1 276CHK0130
1 276CHK0130
2 276CHK0131
2 276CHK0131
2 276CHK0131
3 276CHK0132
3 276CHK0132
3 276CHK0132
4 276CHK0133
Does anyone know a formula I can do this for? I have 2000 rows and do
not want to go through by hand.
Thanks! Mike
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
