[UNRESOLVED] variable range selection determined on cell value?

[direktoren]

Hi

I have a bit of a complictaed problem (or at least complicated for a person with me novice VB programming skills)

I have a report in Excel, generated from an OLAP-viewer. I need to restructure the data so it is presented as I would like it. The problem is determining the range which I need to copy to a new sheet.

In my report I will be experiencing different amounts of rows in column B (Classic OLAP hierachical view - column A=group, cloumn B=subgroup, rest of the columns consists of the actual figures), as the number of subgroups may vary.

Column A Column B

BFORR GICS
a
b
c
d
e
f

W3744 GICS
a
b
c
e

SO - what i need is help with a code that will do something like this;

If range("B5")=GISC then range BFORR= ("B5: next cell in column B="GISC" (this cell not included in the range)).
After this I need to be able to copy it over in a new worksheet in a final structure.


Is this by any means possible??

/Nicolaj

[Static]

Yes it shouldnt be a problem... but I need more information...
or really a better example of what u need...

basically using a for next loop... find the row with GCIS in it.. then save the range in a string

For x = 1 To 10
     If range("B" & x) = "GCIS" Then
          rangetosave = "B" & x
     End If
Next

[direktoren]

I have attached a sample report from the OLAP-viewer just to clarify my problems a bit (dont mind the language - it is in danish)

As you can see column A is the main group and column B then shows the subgroupings of column A. However, as you can see, these subgroups can change in number (from 1 to 7).

What i need is a way to define a range, which in width is given as A to R, but in height should take whatever the size of the maingroup is. For instance my range for maingroup "BFORR" would in this example be ("A2:R8"), but next time I generate the report I might be missing the subgroup "private", then the range copied should be ("A2:R7")

After this, I will need to copy some of the specific range into a new sheet in a desired order...

hope this gave a bit of clarity...

/Nicolaj

[Hack]

Excel question moved to Office Development

[DKenny]

direktoren
The following code will, I hope, give you what you need..

Sub SelectGroupReport()
Dim wksReport As Worksheet
Dim rngStart As Range
Dim iStartCol As Integer
Dim iStartRow As Integer
Dim iEndCol As Integer
Dim iEndRow As Integer
Dim rngGroupReport As Range
Dim iReportCount As Integer
Dim iReportNumber As Integer
    
    Set wksReport = ThisWorkbook.Sheets("Sheet1")
    
    'rngStart is going to be the top-left cell in the subreport.
    'I'm assuming that this will always being in A2.
    Set rngStart = Range("A2")
    
    'The subreport range starts in column 1 (A)...
    iStartCol = 1
    '..and ends in column 18 (R)
    iEndCol = 18
    
    'Determine how many subreports will be needed by counting
    'non-blank cells in column A
    iReportCount = Application.WorksheetFunction.CountA(wksReport.Range("A:A"))
    
    'Main loop to go through each subreport
    For iReportNumber = 1 To iReportCount
        
        'Get the row number that will be the top(start) of the required subreport range
        iStartRow = rngStart.Row
        
        'Now determine the bottom(end) of the required subreport range
        If iReportNumber = iReportCount Then
            'For the last report - End(xlDown) will go all the way to
            'row 65536, so we look for the end of row B by using the offset
            
            iEndRow = rngStart.Offset(0, 1).End(xlDown).Row
        
        ElseIf rngStart.Offset(1, 0).Value = "" Then
            'For cases where we have more that one row we use
            'End(xlDown) to determin the position of the next header
            'and set our ending row to the row above that.
            
            iEndRow = rngStart.End(xlDown).Offset(-1, 0).Row
        
        Else
            'when the cell below our starting cell is populated
            'the report should only have a single row
            'so we set the end row to be the same as the start row
            
            iEndRow = iStartRow
        End If
        
        'We can now set the sub report range
        With wksReport
            Set rngGroupReport = .Range(.Cells(iStartRow, iStartCol), .Cells(iEndRow, iEndCol))
        End With
        
        
        'insert your code here to handle each report
        'the following is just to prove that the code is working
        rngGroupReport.Select
        MsgBox "Report " & CStr(iReportNumber)
        
        'Move to the next report
        'using the same logic as above
        If rngStart.Offset(1, 0).Value = "" Then
            Set rngStart = rngStart.End(xlDown)
        Else
            Set rngStart = rngStart.Offset(1, 0)
        End If
    Next iReportNumber
    
    'Clear object variables
    Set rngGroupReport = Nothing
    Set wksReport = Nothing
End Sub