CODEWITHSUNDEEP
arraysexcelvba
Andrei Vieru
Andrei Vieru

Reputation: 174

Displaying some values from an array VBA excel

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: enter image description here

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

Upvotes: 3

Views: 358

Answers (1)

robotik
robotik

Reputation: 2007

  1. 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.
  2. it seems the data starts at line 2, so start the For i loop with 2.
  3. 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.
  4. 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.
  5. you have five rows in the matrix and every fifth output is a correct value, isn't that suspicious?
  6. 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

Upvotes: 1

Related Questions