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

 

[Niki_S]

And I used this also, but I got and error as subscript out of range error in the below highlighted line.

Dim vArray As Variant


  vArray = oXLSheet.Range("E12:H138,Q12:U138").Value
  
   With oXLSheet
       
            For lp = 1 To UBound(vArray)
                
                   [highlight #FCE94F] List1.AddItem vArray(lp)[/highlight]
                   
            Next
      
   End With

 

[strongm]

>i=50 and l=0

As I said in my post that introduced some vsFlexgrid code, it "assumes the existence of an appropriately sized VSFlexgrid". The default number of Rows is a vsFlexGrid is 50. And the numbering starts from 0, i.e. it has rows 0 - 49 ... can you see where the problem might be?

>This is my excel

So, somewhat different from the example inputs you have previously provided. And won't work properly with my code as it stands, since range 2 column order is reversed

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

It would help if you put all your requirements at the start!

>I can only get U12:R44. What is the reason for that and how can I fix it?

No idea. Works fine here, but then I am using source data that I made up simply to test the principle. It is beginning to get to the stage that you'll need to attach a copy of the spreadsheet you are working from rather than just a screenshot.

 

[Niki_S]

This is my excel sheet.

 

[combo]

When working with excel multiple area ranges (as vArray = oXLSheet.Range("E12:H138,Q12:U138").Value), you get only first area, ie. "E12:H138" You need to loop each area, Range.Areas.Count returns number of areas, Range.Areas(2) refers to second area.
Note that vArray from your last example is 2D array, even when got from single excel column. You need two indexes when you refer to its contents.

combo

 

[strongm]

Wow ...

Well, that really bears little resemblance to what you have previously outlined

1) Each of the two ranges you want to combine actually have a different number of columns
2) The populated columns in the second range are reversed with respect to the populated columns in the first range
3) Each line item for a job number seems to be able to have multiple activities, eg

4) Position of job number is inconsistent

5) You have borders etc on the cells (again, not shown in any of your previous examples of what you wanted to extract)

6) There are rows in each range that you don't want copied but which DO have data in, i.e. not empty as you previously suggested

7) It is unclear how you define a duplicate. All columns match? Specific column(s) match?

Basically, your source data is a bit of a mess. Where does it come from? is it manually created? It rather looks like a report - and frankly you should never then try to analyse a report. Source data in a nice table is best (although, of course, you may not have that).

But if the report is all you have got, you need to ensure it is consistent before trying to extract data from it reliably. And I'm afraid I don't have the time or inclination to suggest code that can deal with all the inconsistencies.

>I can only get U12:R44. What is the reason for that and how can I fix it?
Going back to this, I'm afraid I cannot replicate your issue. Well, as long as I use the code as intended. However, you seem to have changed the call to [tt]Example[/tt]. Go back, study, and see if you can understand what your error was/is.

 

[Niki_S]

It is unclear how you define a duplicate. All columns match? Specific column(s) match?

I want to match all columns.

is it manually created?

Yes it is doing manually.

>I can only get U12:R44. What is the reason for that and how can I fix it?

I got only this range values. I go this range Example oXLSheet.Range("E12:H138"), oXLSheet.Range("Q12:U138"), but I didn't get the values.


Thank you

 

[combo]

I go this range Example oXLSheet.Range("E12:H138"), oXLSheet.Range("Q12:U138")

If you look at function definition [tt]Public Sub Example(destcell As Range, ParamArray srcrng()[/tt] provided by strongm, you can notice that the first argument is a target range, IMO it should be somewhere in oXLSheet2 worksheet.

combo

 

[strongm]

>IMO it should be somewhere in oXLSheet2 worksheet

Quite so

 

[Niki_S]

Finally I got the data into a listbox. But I want to get them into my grid. But in the grid it doesn't appear 3 columns. How can I do that?

Thank you.

 

[strongm]

>it doesn't appear 3 columns

Because at no point prior to this have you definitively specified that you wanted only 3 columns in the vsFlexGrid. Your current source data includes both 4 and 5 columns, and various examples you have provided of the output you require have involved differing numbers of columns.

But let's be clear here

[COLOR=blue]For Each mycell In myrow.Cells
    myVSFlexgrid.TextMatrix(i, l) = mycell.Text
    l = l + 1
Next[/color]

is adding data to each row in the vsflexgrid on a column by column basis, so you can obviously clearly just select the columns you want at this point. Shouldn't be difficult for a programmer to implement that

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

and now suddenly (in this thread)

>got the data into a listbox

 

[Niki_S]

>Your current source data includes both 4 and 5 columns
I changed it into like this and now both of them have 5 columns. ("E12:I138"),("R12:V138")

For Each mycell In myrow.Cells
    myVSFlexgrid.TextMatrix(i, l) = mycell.Text
    l = l + 1
Next

By doing this code I got "Invalid property array index" error on "i=50 and l=0". So I think it is better to add the details into a list box with multiple columns. If it is so how can I do it?
And according to my excel ranges,
E = V
F = S
I = R
As an example it's like this.

And the final output should be like this,

So I think it is better to add these data into a listbox. So how can I do it?

Thank you.

 

[strongm]

>By doing this code I got "Invalid property array index" error on "i=50 and l=0"

I repeat what I said previously: "As I said in my post that introduced some vsFlexgrid code, it 'assumes the existence of an appropriately sized VSFlexgrid'. The default number of Rows is a vsFlexGrid is 50. And the numbering starts from 0, i.e. it has rows 0 - 49 ... can you see where the problem might be?"

And here's the answer to that question: oh, I've got too few rows in the vsFlexgrid. I should add some more ...

>As an example it's like this.

Well, no, no it isn't. The reality - from the spreadsheet you provided - is more like:

Unless you have changed lots. And here's our problem in trying to help you: you keep changing things, you provide examples of input that bear no real relation to what you are really working with (even some of your example outputs have not necessarily matched the example inputs), and you spring new requirements on us that you have not previously told us about (except perhaps in a different thread that not all of us may have read).

Even something such as "I want to match all columns." is not straightforward, thanks to "Yes it is doing manually [populating the spreadsheet]" - because there are typos. I'd suspect, for example, that "PRESS GARMNT" and "PRESS GARMENT" are actually the same thing ...