Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can this VBA Code do this? (2003)

    I have some code (I have tried to adapt it but my VBA skills are low, obviously)
    'Select the full table (including headings) first
    Sub MakeListFromTable()
    ' Dim rngData, rngR As Range
    Dim rngData As Range
    Dim rngR As Range
    Dim wsDestination, wsSource As Worksheet
    Dim lIndex, lColumnHeadingsRow As Long
    Dim iRowHeadingsColumn As Integer

    'i added these lines
    'Range("A1").Select
    'Selection.CurrentRegion.Select

    'find position of row and column heading relative to the worksheet
    Selection.CurrentRegion.Select

    lColumnHeadingsRow = Selection.Rows(1).Row
    iRowHeadingsColumn = Selection.Columns(1).Column

    'define data area
    Set rngData = ActiveSheet.Range(Selection.Columns(2).Cells(2), Selection.Cells(Selection.Cells.Count))
    'set source worksheet variable
    Set wsSource = ActiveSheet
    'add a new worksheet for the list
    Set wsDestination = ActiveWorkbook.Worksheets.Add

    'initialise counter to keep track of list rows
    lIndex = 2
    'loop through each cell in the data range
    For Each rngR In rngData
    'get column heading
    wsDestination.Cells(lIndex, 1).Value = _
    wsSource.Cells(lColumnHeadingsRow, rngR.Column).Value
    'get row heading
    wsDestination.Cells(lIndex, 2).Value = _
    wsSource.Cells(rngR.Row, iRowHeadingsColumn).Value
    'add data value
    wsDestination.Cells(lIndex, 3).Value = rngR.Value
    'increment row counter for new sheet
    lIndex = lIndex + 1
    Next rngR
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToRight).Select
    'ReplaceZeros
    End Sub

    You can see, hopefully, from the attached sheet what I am trying to do--basically turn the date from a tabular format into a columnar format, leaving the first 3 columns, inserting a column called month and another called Value or Amt. Basically turn Sheet1 into Sheet2

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can this VBA Code do this? (2003)

    Does the attached do what you want?

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this VBA Code do this? (2003)

    Outstanding! You guys are great. Yes, this works well. I will have to break it down some more to see where my thinking was off. For some reason, when I ran this code, the copying of a1 to c1 came over as blank, but I kind of fixed it. That was minor--this worked well!

    Many thanks again,
    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can this VBA Code do this? (2003)

    The main point is that you have to loop through the source rows, and within them through the columns. One loop through all cells won't work, since the data from the first three columns have to be repeated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •