Bernie,
thank you very much for your suggestion, I'll have a close look at it to see
if I can use it (I'm not yet very familiar with VBA).
The model I originally refered to, however, simply harnessed the command
"Data - table..." (English version) or "Daten - Mehrfachoperation..."
(German version), without any programming (VBA was unknown to early XL
anyway).
In case there is still an old manual (MS still printed them out at the time)
of Excel 3 around - the technique was shown/explained there on page 500 +
something. Any idea where to retrieve that ?
Thank you in advance.
Kind regards,
H.G. Lamy
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:(E-Mail Removed)...
> H.,
>
> I think that you are talking about a cross-tab table.
>
> There are two ways to convert a database to a cross-tab table.
>
> If you have a three column database, and the third column is numeric,
> simply use the database as the source of a pivot table. Drag column A to
> the row field, column B to the column field, and column C to the data
> field, set to sum.
>
> Or you can use a macro, like the one below, which converts a three column
> database to a cross-tab table.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> Sub DBtoCrossTab2()
> Dim myCell As Range
> Dim myTable As Range
> Dim mySht As Worksheet
> Dim myRow As Long
> Dim myCol As Integer
>
> Set myTable = ActiveCell.CurrentRegion
>
> If myTable.Columns.Count <> 3 Then
> MsgBox "This macro works on a 3 column database only"
> Exit Sub
> End If
>
> On Error Resume Next
> Application.DisplayAlerts = False
> Worksheets("Cross Tab").Delete
> Application.DisplayAlerts = True
>
> Set mySht = Worksheets.Add
> mySht.Name = "Cross Tab"
>
> 'myTable.Rows(1).EntireRow.Copy mySht.Rows(1)
>
> Set myTable = myTable.Offset(1, 0).Resize _
> (myTable.Rows.Count - 1, myTable.Columns.Count)
> MsgBox myTable.Address
> MsgBox myTable.Columns(1).Cells.Address
>
> For Each myCell In myTable.Columns(1).Cells
> If IsError(Application.Match(myCell.Value, _
> mySht.Range("A:A"), False)) Then
> mySht.Range("A65536").End(xlUp)(2).Value = myCell.Value
> End If
> If IsError(Application.Match(myCell(1, 2).Value, _
> mySht.Range("1:1"), False)) Then
> mySht.Range("IV1").End(xlToLeft)(1, 2).Value = myCell(1, 2).Value
> End If
> myRow = Application.Match(myCell.Value, _
> mySht.Range("A:A"), False)
> myCol = Application.Match(myCell(1, 2).Value, _
> mySht.Range("1:1"), False)
>
> If IsNumeric(myCell(1, 3).Value) Then
> mySht.Cells(myRow, myCol).Value = mySht.Cells(myRow, myCol).Value +
> myCell(1, 3).Value
> Else
> mySht.Cells(myRow, myCol).Value = myCell(1, 3).Value
> End If
> Next myCell
>
> End Sub
>
>
>
>
> "H.G. Lamy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hello,
>>
>> in an old Excel-manual (possibly way back to version 3) by Microsoft
>> there was an excellent example of a 2-dimensional datatable (in German:
>> Mehrfachoperation) with input variables based on database results. Now I
>> would need to apply this technique, but my old manual is lost, and I
>> can't figure out by heart exactly how this was done.
>>
>> Does somebody still have an old manual from which to draw the model, or
>> can let me know the deatils of the trick?
>>
>> Thank you in advance.
>>
>> Kind regards,
>>
>> H.G. Lamy
>>
>>
>>
>
>
|
Similar Threads
