Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual basic to step through range and store a value based on text attribute value.
Hi Ashleigh,
For i = 1 To 21000 Select Case Cells(i,1).Font.Colorindex Case 3 : Cells(i,3).Value = "Red" Case 10 : Cells(i,3).Value = "Green" End Select Next i Extend with extra case statements and values -- HTH Bob Phillips "Ashleigh K." wrote in message ... Help, my boss is making me do a time wasting excercise and I can't figure it out. I'm trying to sort a HUGE excel database consisting of roughly 22,000 records. What I'd like to do is to create a visual basic routine that will step through each record in the database and store a value in a free cell indicating the text attributes of the value in column A. The database looks something like this: Column A = First Names Column B = Last Names Column C = Currently blank, but I want it to store the value of the visual basic routine. So, a pseudocode example would do something like this. Start For 1 to x (x=total number of records 21,000) If font color attribute for Cell A(x) = Red Then Store the word "Red" in cell C(x) endif next x end I know from a macro that I tried to record that it will use something like Selection.Font.ColorIndex = 3 but I don't know how to make it step through each cell and then store the new value in the corresponding row in column C. Does this make sense? Am I approaching this the right way. Does anyone have a suggestion on how I might write the visual basic to achieve this. My end objective is to be able to sort the database that has records in many different colors by the color of the text. I think this approach (if I can figure it out) will get me there. Thanks for all of your help! A. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual basic to step through range and store a value based on text attribute value.
Thanks Bob and Bob....your solutions worked great, I really appreciate your
help! Ashleigh "Bob Phillips" wrote in message ... Hi Ashleigh, For i = 1 To 21000 Select Case Cells(i,1).Font.Colorindex Case 3 : Cells(i,3).Value = "Red" Case 10 : Cells(i,3).Value = "Green" End Select Next i Extend with extra case statements and values -- HTH Bob Phillips "Ashleigh K." wrote in message ... Help, my boss is making me do a time wasting excercise and I can't figure it out. I'm trying to sort a HUGE excel database consisting of roughly 22,000 records. What I'd like to do is to create a visual basic routine that will step through each record in the database and store a value in a free cell indicating the text attributes of the value in column A. The database looks something like this: Column A = First Names Column B = Last Names Column C = Currently blank, but I want it to store the value of the visual basic routine. So, a pseudocode example would do something like this. Start For 1 to x (x=total number of records 21,000) If font color attribute for Cell A(x) = Red Then Store the word "Red" in cell C(x) endif next x end I know from a macro that I tried to record that it will use something like Selection.Font.ColorIndex = 3 but I don't know how to make it step through each cell and then store the new value in the corresponding row in column C. Does this make sense? Am I approaching this the right way. Does anyone have a suggestion on how I might write the visual basic to achieve this. My end objective is to be able to sort the database that has records in many different colors by the color of the text. I think this approach (if I can figure it out) will get me there. Thanks for all of your help! A. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Range not equal to zero in Visual Basic If/Then statement | Excel Discussion (Misc queries) | |||
Currency format in visual basic text box | Excel Discussion (Misc queries) | |||
Are there any NON-Visual Basic solutions for inserting a picture based a derived or called filename? | Excel Worksheet Functions | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Visual basic to step through range and store a value based on text attribute value. | Excel Programming |