CODEWITHSUNDEEP
excelvba
Priit Mets
Priit Mets

Reputation: 495

How select values from one column based on values from the another column in VBA?

I have a table "data", where I want to save row values in column 3 (shows row number) based on condition if values in column 2 = 1

enter image description here

The next table shows the information below. I want to filter this table showing only columns a,b,e (1,2,5 column order). The index for columns is retrieved from the first table's 3rd column because the order of the rows is equivalent to the order of columns in the second table.

enter image description here

I wrote a code, however, it saves only 1 and 2 values (from column 3 ), but the number 5 should be also saved. How can I change the code to getting the right 3rd column values from the first table and then filter the second table?

Dim ploeg As Range
   Dim ploeg2 As Range

   For v = 1 To 5
       If rng.Cells(v, 2) = 1 Then
           Set ploeg = Cells(v, 3)
           If ploeg2 Is Nothing Then
               Set ploeg2 = ploeg
           Else
               Set ploeg2 = Union(ploeg2, ploeg)
           End If
           
       End If
   Next v
   

Worksheets("Sheet1").Range("E1").Resize(ploeg2.Rows.Count, ploeg2.Columns.Count).Cells.Value = ploeg2.Cells.Value


End Sub

Upvotes: 0

Views: 2308

Answers (1)

norie
norie

Reputation: 9857

If you only want the values use an array to 'collect' them.

This code assumes the data is in the range A1:E5.


Dim ploeg As Range
Dim arrVals()
Dim cnt As Long
dim v As Long

   ReDim arrVals(1 To 1, 1 To 5)

   For v = 1 To 5
       If Cells(v, 2) = 1 Then
           Set ploeg = Cells(v, 3)
           cnt = cnt + 1
           arrVals(1, cnt) = ploeg.Value          
       End If
   Next v
   
   If cnt > 0 Then
       ReDim Preserve arrvals(1 To 1, 1 To cnt)
       Worksheets("Sheet1").Range("E1").Resize(cnt).Value = Application.Transpose(arrVals)
   End If

Upvotes: 2

Related Questions