Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some syntax help, simple loop I think
Howdy,
I have programmed in Excel in a long time and am not sure where to begin... I have some data in 5 column such as: Name, City, X-coord, Y-coord, Item I want to run through this data and create a map in the second sheet where at the given X, Y coordinates for each person, it colors that cell a certain color based on the "Item" and then creates a Comment for the cell containing the Name and City. I know this should be pretty darn easy but I'm rusty and need some guidance. Some things I know I need a How to set the fill color via a command. How to insert a comment via a command. Basically everything to get me started. Thanks for any and allhelp. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some syntax help, simple loop I think
What type of data is contained in the X-coord and Y-coord values and how are
these values mapped to cells or locations on the second sheet? How is the Item value related to a color that is to be used on the second sheet? -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Mr B" wrote in message ... Howdy, I have programmed in Excel in a long time and am not sure where to begin... I have some data in 5 column such as: Name, City, X-coord, Y-coord, Item I want to run through this data and create a map in the second sheet where at the given X, Y coordinates for each person, it colors that cell a certain color based on the "Item" and then creates a Comment for the cell containing the Name and City. I know this should be pretty darn easy but I'm rusty and need some guidance. Some things I know I need a How to set the fill color via a command. How to insert a comment via a command. Basically everything to get me started. Thanks for any and allhelp. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some syntax help, simple loop I think
Hi Chip,
The data would look like this: John, Smithville, 13, 22, Coal Jane, Doesville, 45, 11, Water I figured it'd be easiest to just have 5 IF statements to set the colors... If (E,CurrentRow) = Coal then set the color to black. If (E,CurrentRow) = Water then set the color to blue etc Nothing is mapped to the second sheet at all. I just want to use the first 100 rows and columns of the 2nd sheet to be the "map". So in the example above, it would go to Column 13, Row 22, fill it with Black and add a Comment saying John - Smithville. Then it would go onto the next one... I'm good with taking existing scripts and tweaking them but I don't have anything to start with for this... Thanks! "Chip Pearson" wrote: What type of data is contained in the X-coord and Y-coord values and how are these values mapped to cells or locations on the second sheet? How is the Item value related to a color that is to be used on the second sheet? -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Mr B" wrote in message ... Howdy, I have programmed in Excel in a long time and am not sure where to begin... I have some data in 5 column such as: Name, City, X-coord, Y-coord, Item I want to run through this data and create a map in the second sheet where at the given X, Y coordinates for each person, it colors that cell a certain color based on the "Item" and then creates a Comment for the cell containing the Name and City. I know this should be pretty darn easy but I'm rusty and need some guidance. Some things I know I need a How to set the fill color via a command. How to insert a comment via a command. Basically everything to get me started. Thanks for any and allhelp. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some syntax help, simple loop I think
On Mon, 24 Mar 2008 13:08:02 -0700, Mr B
wrote: I'm good with taking existing scripts and tweaking them but I don't have anything to start with for this... Watch for line wrap: Sub MapData() Dim rCell As Range, rRng As Range Sheet2.UsedRange.Clear Set rRng = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown)) For Each rCell In rRng.Cells With Sheet2.Cells(rCell.Offset(0, 3).Value, rCell.Offset(0, 2).Value) Select Case rCell.Offset(0, 4).Value Case "Coal" .Interior.Color = vbBlack Case "Water" .Interior.Color = vbBlue Case "Solar" .Interior.Color = vbCyan Case "Wind" .Interior.Color = vbYellow Case "Gas" .Interior.Color = vbMagenta End Select '.Comment.Text = rCell.Offset(0, 0).Value & "-" & rCell.Offset(0, 1).Value '.Comment.Visible = True .AddComment rCell.Offset(0, 0).Value & "-" & rCell.Offset(0, 1).Value End With Next rCell End Sub -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some syntax help, simple loop I think
OK, that's perfect.
Thanks a TON! "Dick Kusleika" wrote: On Mon, 24 Mar 2008 13:08:02 -0700, Mr B wrote: I'm good with taking existing scripts and tweaking them but I don't have anything to start with for this... Watch for line wrap: Sub MapData() Dim rCell As Range, rRng As Range Sheet2.UsedRange.Clear Set rRng = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown)) For Each rCell In rRng.Cells With Sheet2.Cells(rCell.Offset(0, 3).Value, rCell.Offset(0, 2).Value) Select Case rCell.Offset(0, 4).Value Case "Coal" .Interior.Color = vbBlack Case "Water" .Interior.Color = vbBlue Case "Solar" .Interior.Color = vbCyan Case "Wind" .Interior.Color = vbYellow Case "Gas" .Interior.Color = vbMagenta End Select '.Comment.Text = rCell.Offset(0, 0).Value & "-" & rCell.Offset(0, 1).Value '.Comment.Visible = True .AddComment rCell.Offset(0, 0).Value & "-" & rCell.Offset(0, 1).Value End With Next rCell End Sub -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do...Loop syntax help? | Excel Programming | |||
Simple CountA syntax (VBA) | Excel Programming | |||
loop syntax | Excel Programming | |||
Syntax error (simple) | Excel Programming | |||
need help with simple syntax error | Excel Programming |