PDA

View Full Version : create a list of numbers in a single cell



davidw
08-28-2009, 10:14 AM
I have a worksheet "ALL KEYS" that has data that are contained within a list. I have another sheet "KEYGRAPH" that has row lettered as A,B,C...The columns on Keygraph are 1,3,5,7... Some of the graphs will be 2,4,6,8... I want to create a "key graph" worksheet that is constructed Totally using the information from the "ALL KEYS" worksheet. I have a macro that is called "CREATE KEYGRAPH " that sortof gives an example of what i want but is far from ok. I know just a little vba but only enough through trial and error to put together other macros i find to create anything. I have attached the file for you to look at. Any help would be appreciated. Thx

Bob Phillips
08-28-2009, 10:48 AM
Halfway down the spreadsheet it changes from A, B, C etc. to names. What is that all about?

davidw
08-28-2009, 11:00 AM
this form is used for all kinds of "keys" data including personal keys... I only want the macro to run for buildings. They are all defined as A-Zrows and columns defined as 1-whatever. What I want is that I will physically choose the Building and location. I want the macro to create a keygraph using what i have exposed using my dropdown list. Example: I will pick building 1 and location CP. The macro will run the rest. This allows me to create individual graphs for each building.

Bob Phillips
08-28-2009, 11:03 AM
Sub CREATE_KEYGRAPH()
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim RowNum As Long
Dim ColNum As Long

With Worksheets("ALL KEYS")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow

tmp = tmp & .Cells(i, "F").Value & Chr(10)

If .Cells(i, "A").Value = 1 Then

If .Cells(i, "C").Value <> .Cells(i + 1, "C").Value Or _
.Cells(i, "D").Value <> .Cells(i + 1, "D").Value Then

RowNum = Asc(.Cells(i, "C").Value) - 64 + 1
ColNum = (.Cells(i, "D").Value + 1) \ 2 + 1
Worksheets("KEY GRAPH").Cells(RowNum, ColNum).Value = tmp
tmp = ""
End If
End If
Next i
End With

End Sub

davidw
08-28-2009, 11:16 AM
This macro needs to be able to run other areas other than building 1 CP. When I show building 13 cp it still prints out building 1 cp

rbrhodes
08-29-2009, 06:52 PM
Hi david,

May be a little buggy but it's as far as I can go on a hobby level...

I limited it to building numbers and did not provide for key columns ##A, ##B or ###/###, etc. Only numbers.