Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Location data to grid
The grid is a usual data table. As discribed it is 26 row * 26 columns The clolumns header are letters , the row headers are numbers. Th entry in each cell should be the product name. example: Input data item 1 - a1, b2 item 2 - a2 item 3 - b1 Result: ---| a | b | 1 | item 1 | item 3 | 2 | item 2 | item 1 -- fredl ----------------------------------------------------------------------- fredlo's Profile: http://www.excelforum.com/member.php...fo&userid=1453 View this thread: http://www.excelforum.com/showthread.php?threadid=26160 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Location data to grid
I'd use a small macro:
Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim testRng As Range Dim mySplit As Variant Dim iCtr As Long Dim curWks As Worksheet Dim newWks As Worksheet Set curWks = Worksheets("Sheet1") Set newWks = Worksheets.Add With curWks 'headers in row 1 of item list sheet??? Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells mySplit = Split97( _ Application.Substitute(myCell.Offset(0, 1).Value, " ", ""), ",") 'mySplit = Split(Replace(myCell.Offset(0, 1).Value, " ", ""), ",") For iCtr = LBound(mySplit) To UBound(mySplit) Set testRng = Nothing On Error Resume Next Set testRng = newWks.Range(mySplit(iCtr)) On Error GoTo 0 If testRng Is Nothing Then MsgBox "Error with row: " & myCell.Row _ & " value: " & mySplit(iCtr) Else If testRng.Value = "" Then testRng.Value = myCell.Value Else testRng.Value = testRng.Value & "," & myCell.Value End If End If Next iCtr Next myCell End With End Sub 'from Tom Ogilvy Function Split97(sStr As Variant, sdelim As String) As Variant Split97 = Evaluate("{""" & _ Application.Substitute(sStr, sdelim, """,""") & """}") End Function === If you're using xl2k or higher, delete this logical line: mySplit = Split97( _ Application.Substitute(myCell.Offset(0, 1).Value, " ", ""), ",") And uncomment the Split() version. And you can delete the split97 function, too. Both Split and Replace were added in xl2k. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm fredlo wrote: The grid is a usual data table. As discribed it is 26 row * 26 columns. The clolumns header are letters , the row headers are numbers. The entry in each cell should be the product name. example: Input data item 1 - a1, b2 item 2 - a2 item 3 - b1 Result: ---| a | b | 1 | item 1 | item 3 | 2 | item 2 | item 1 | -- fredlo ------------------------------------------------------------------------ fredlo's Profile: http://www.excelforum.com/member.php...o&userid=14535 View this thread: http://www.excelforum.com/showthread...hreadid=261601 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grid lines in Excel not showing.Have tools,options,view/grid cked | Excel Discussion (Misc queries) | |||
Pulling data from grid | Excel Worksheet Functions | |||
Is it possible to use 'grid' as a value in vb to process XL data? | Excel Discussion (Misc queries) | |||
Location data to grid | Excel Programming | |||
Active X and Data Grid | Excel Programming |