Error in Excel/VB6 function: "Constant expression required"

[Niki_S]

I got this error in this highlighted line and I want to read these ranges and get data into my array.

Private Sub VSFlexGrid2_DblClick()

Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Dim Style_no As String
Dim Line_no As String
Dim vArray As Variant
Dim Array1(D12 To H138, Q138 To [highlight #FCE94F]V12[/highlight]) As Variant

Style = Trim(VSFlexGrid2.TextMatrix(VSFlexGrid2.Row, 1))

  If VSFlexGrid2.Row <= 0 Then
   
    Exit Sub
   
   Else
   
        Set oXLApp = New Excel.Application
        Set oXLBook = oXLApp.Workbooks.Open("K:\GENERAL\POD_Infor\" & Style & ".xls")
        Set oXLSheet = oXLBook.Worksheets(1)
        Set oXLSheet2 = oXLBook.Worksheets(2)

        
        
     With oXLSheet


        For i = D12 To H138
        
            For i2 = Q138 To V12
            
                Array1(i, i2) = X
                X = X + 1
            
            Next
        
        Next
        
        
    End With
   
   End If
   
End Sub

How can I fix this and get my data into my array?

Thank you

 

[strongm]

Perhaps you should describe what you are trying to do here, as your code doesn't make any real sense. You seem to be trying to treat two rectangular regions as linear, and building an array that contains a sequential count.

For example: are you trying to merge data from two separate, equally sized ranges in a spreadsheet into a single 2d array? If so is that merge a verticcal stack of the data? A horizontal stack? Interleaved? Random?

And what are you then intending to use the array for? Simply as a data store for easy retrieval? To feed a combobox (as per some of your other threads)?

Don't want to waste time fixing the wrong problem.

 

[Niki_S]

This is how my excel looks like,

And I want to get my output as below,

And I want to insert these data into an excel sheet.

Thank you

 

[combo]

From the syntax point of view:
1. dynamic arrays are declared in two steps, first [tt]Dim Array1() As Variant[/tt], next the size with [tt]ReDim[/tt],
2. in your examples D12, H138, Q138 and V12 are VB(A) variables, not related to excel addresses. If you need to assign excel data in range to Array1, the code example for it was in one of your previous posts, together with reading data from the array.

combo

 

[Niki_S]

I did something like this,

     With oXLSheet

        Job = oXLSheet.Range("D12 : D138").Value
        Des = oXLSheet.Range("D12 : D138").Value
        SMV = oXLSheet.Range("D12 : D138").Value
        
        For lngRow1 = LBound(Job, 1) To UBound(Job, 1)
        
            For lngRow2 = LBound(Des, 1) To UBound(Des, 1)
            
                For lngRow3 = LBound(SMV, 1) To UBound(SMV, 1)
                
                
                   [highlight #FCE94F] Array1(lngRow1, lngRow2, lngRow3) = X[/highlight]
                    X = X + 1
                
                Next lngRow3
                
            Next lngRow2
            
        Next lngRow1
        
        
    End With

And I got "Subscript out of range" error from this highlighted line.
How can I fix this?

Thank you

 

[combo]

How Array1 is declared and its dimensions set? Do you really need 3D array with serial numbers (it is what X=X+1 does in loop)?

combo

 

[Niki_S]

This is how I declared Array1.

Dim Array1(12 To 138, 12 To 138, 12 To 138) As Variant

I want to get my output as I mentioned above. How can I do that using this Array1?
And I don't want to use X=X+1 in my loop.

Thank you.

 

[combo]

OK, so you have 3D array with indexes from 12 to 138 in each dimension. LBound in each of three arrays: Job, Des and SMV is probably 1, so you start to fill Array1 from Array1(1, 1, 1), that is out of declared indexes.

For me you rather need a single loop or a series of loops, to fill an array. The array should be variant and 2D. You can use [tt]ReDim Preserve[/tt] to change the size when you add a row, or do it after filling the array. Next this array can be assigned to range in an opposite way (range = variant array).

Arrays: Job, Des and SMV use the same range, so they have the same data. How do you expect getting the result as in your post from 21 Oct 22 09:18 without picking data from proper ranges and processing the arrays with data row by row to use only non-empty rows?

combo

 

[strongm]

Ok, so indirectly you have managed to answer most of the questions that I posed earlier.

However, one key one remains undetermined: why do you need the array? Is it simply to giove you an easy way to insert the data into a spreadsheet? Why not insert it directly from the source data without using an interim array? The use of the array is adding complexity you may not need (particularly since you are for some reason introducing a somewhat nonsensical sparse 3d array)

So, the following might be sufficient for your needs:

[COLOR=blue]Public Sub Example(destcell As Range, ParamArray srcrng())
    Dim myrng, myrow
    Dim i As Long
    i = 0
    
    For Each myrng In srcrng
        For Each myrow In myrng.Rows
            If Excel.Application.WorksheetFunction.CountA(myrow) <> 0 Then
                myrow.Copy destcell.Cells(1, 1).Offset(i) [COLOR=green]' just use top left call as anchor for   destination[/color]
                i = i + 1
            End If
        Next
    Next

End Sub[/color]

which might be used as follows (I am trying to keep to your variables):

[COLOR=blue]    Set oXLSheet = Worksheets("SourceSheet") [COLOR=green]' set to your own source sheet, as per you code I am guessing this would be oXLBook.Worksheets(1)[/color]
    Set oXLSheet2 = Worksheets("TargetSheet") [COLOR=green]' set to your own destination sheet, as per you code I am guessing this would be oXLBook.Worksheets(2)[/color]
    
    Excel.Application.ScreenUpdating = False
    Example oXLSheet2.Range("A1"), oXLSheet.Range("B5:E15"), oXLSheet.Range("H5:K15") [COLOR=green]' using ranges from your example sheet[/color]
    Excel.Application.ScreenUpdating = True[/color]

 

[Niki_S]

I want to display the results on a grid. How can I do it?

Thank you

 

[strongm]

>I want to insert these data into an excel sheet
>I want to display the results on a grid.

Well, which? Or both? And which grid?

 

[combo]

And I want to insert these data into an excel sheet.

I want to display the results on a grid. How can I do it?

What grid do you mean? If excel worksheet, strongm's code does this.


combo

 

[Niki_S]

I want to insert them into the both and have to take a new grid.

 

[strongm]

>have to take a new grid.

I repeat, what grid?

 

[Niki_S]

I want to take a VSFLEXGRID.

 

[strongm]

Ah, a 3rd party control with which I am not familiar (and do not have a copy of). But a quick glance at the documentation suggests there is a method called TextMatrix which should allow a minor alteration to my previous [tt]Example[/tt] code to allow it to insert both into a spreadsheet and into a VSFlexgrid, something like:

[COLOR=blue]Public Sub Example(destcell As Range, ParamArray srcrng())
    Dim myrng, myrow[b][COLOR=red], mycell[/color][/b]
    Dim i As Long[b][COLOR=red], l As Long[/color][/b]
    i = 0
    
    
    For Each myrng In srcrng
        For Each myrow In myrng.Rows
            If Excel.Application.WorksheetFunction.CountA(myrow) <> 0 Then
                myrow.Copy destcell.Cells(1, 1).Offset(i) [COLOR=green]' just use top left call as anchor for   destination[/color]
                [b][COLOR=red]l = 0
                For Each mycell In myrow.Cells
                    myVSFlexgrid.TextMatrix(i, l) = mycell.Text
                    l = l + 1
                Next[/color][/b]
                i = i + 1
            End If
        Next
    Next

End Sub[/color]

This assumes the existence of an appropriately sized VSFlexgrid called myVSFlexgrid

 

[Niki_S]

I got "invalid property array index" error in this line.

myVSFlexgrid.TextMatrix(i, l) = mycell.Text

How can I fix this?

 

[strongm]

No idea. As I say, you are asking about a 3rd party control, one I don't have, and nothing directly to do with VB6. What are the values of i and l when the error occurs? That might be a clue.

 

[Niki_S]

i=50 and l=0.
Is there have any other way to get my output?

With oXLSheet

    Excel.Application.ScreenUpdating = False
    Example oXLSheet.Range("E12:H138"), oXLSheet.Range("Q12:U138")
    Excel.Application.ScreenUpdating = True


    End With

This is what I did in my code. By doing this I can only get U12:R44. What is the reason for that and how can I fix it?

Thank you

 

[Niki_S]

This is my excel.

And I want to get my output as below,

In my final output I want to get all these data into a grid and also need to remove duplicates also.


Thank you.