Displaying some values from an array VBA excel

Question

I'm trying to display multiple values on my excel file based on first line and first column. I've made a 2D array for my values. MyValue is an InputBox variable and I loop over the first column trying to find MyValue and I want to get on display values that are on "PSC", "BCCH" and so on columns, basically the same thing as a filter based on 1st column but displaying only some values from other columns. The output that i have for this operation is in this image:

However 136, 144 and 152 which are PSC values are on line 2, 3, 4 in my source excel file, I'm not sure what are those zeros. I want to diplay those values one under each other, I tried without offset method but if I don't use it I get no value displayed.

Dim1 = wSht.Range("A2", wSht.Range("A1").End(xlDown)).Cells.Count
Dim2 = wSht.Range("A1", wSht.Range("A1").End(xlToRight)).Cells.Count

For i = 1 To Dim1
    For j = 1 To Dim2
        If wSht.Cells(i, 1) = MyValue Then
            If wSht.Cells(1, j) = "PSC" Then

                ReDim Preserve Matrice(0 To 5, 0 To Matrice_size)
                Matrice(0, Matrice_size) = wSht.Cells(i, j).value
                Matrice_size = Matrice_size + 1
            End If

            If wSht.Cells(1, j) = "BCCH" Then

                ReDim Preserve Matrice(0 To 5, 0 To Matrice_size)
                Matrice(1, Matrice_size) = wSht.Cells(i, j).value
                Matrice_size = Matrice_size + 1
            End If
        End If
    Next j

Next i

For k = LBound(Matrice, 1) To UBound(Matrice, 1)
    For l = LBound(Matrice, 2) To UBound(Matrice, 2)
        ThisWorkbook.Worksheets(1).Range("A2").Offset(k, l).value = Application.Transpose(Matrice(k, l))
    Next l

Next k

source file (or an excerpt) would help a lot

robotik
– robotik

2016-09-06 17:07:21 +00:00
Commented Sep 6, 2016 at 17:07 — robotik
– robotik, Commented Sep 6, 2016 at 17:07

robotik · Accepted Answer · 2016-09-07 11:45:21Z

switch the first If line with the For j and also the corresponding last End If with the Next j, so the inner loop only runs through the correct rows.
it seems the data starts at line 2, so start the For i loop with 2.
if you only need data from those two columns you can look for them in a separate loop first and save their position, so you don't need a nested loop. (in this case my first point doesn't apply). you don't even need a loop, you can use Excel's MATCH function.
the matrix and transpose seems to be an overkill. since i determines k and l you can get rid of this part, and write to Worksheets(1) directly from the i-j loop.
you have five rows in the matrix and every fifth output is a correct value, isn't that suspicious?
you seem to put values into line 0 and 1 of the matrix then read from line 1 and 2. this might be the reason for the line full of 0s

if you can provide what exactly you would expect from what exact source data, I can help you more. but without knowing, my solution would look something like

PSC_col = Application.WorksheetFunction.Match("PSC", wSht.Rows(1).EntireRow, 0)
BCCH_col = Application.WorksheetFunction.Match("BCCH", wSht.Rows(1).EntireRow, 0)

For i = 2 To wSht.[A2].End(xlDown).Row
    If wSht.Cells(i, 1) = MyValue Then
        ThisWorkbook.Worksheets(1).[A2].Offset(k, 0).Value = wSht.Cells(i, PSC_col).Value
        ThisWorkbook.Worksheets(1).[B2].Offset(k, 0).Value = wSht.Cells(i, BCCH_col).Value
        k = k + 1
    End If
Next i

Collectives™ on Stack Overflow

Displaying some values from an array VBA excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related