Rcflife2335
New Member
- Joined
- Feb 15, 2024
- Messages
- 2
- Office Version
- 365
- 2021
- 2019
- Platform
- Windows
Hello,
I’m trying to write a vba script to return all columns that match a list of numbers. Some numbers have multiple rows of data. I use the choosecols and filter function to return the data which works. Now I’m trying to write a VBA script that does the same thing. Problem now is that it’s not returning the full data and my counter doesn’t continue. Any help is welcome thank you. Code below
Sub all()
Dim shODS As Worksheet
Dim shCFC As Worksheet
Dim indx As Integer
Dim LastROW As Integer
Set shODS = ThisWorkbook.Worksheets("ODS_PROD")
Set shCFC = ThisWorkbook.Worksheets("CFC_PROD")
LastROW = shODS.Cells(Rows.Count, 1).End(xlUp).Row
shODS.Range("N5").Formula = "=CHOOSECOLS(FILTER(CFC_PROD!A:L,CFC_PROD!A:A=ODS_PROD!D941," & Chr(34) & " " & Chr(34) & "), 3,6,9)"
shODS.Range("O5").Formula = "=CHOOSECOLS(FILTER(CFC_PROD!A:L,CFC_PROD!A:A=ODS_PROD!D941," & Chr(34) & " " & Chr(34) & "),3)"
For indx = 2 To LastROW
shODS.Range("K" & indx).Formula = "
=CHOOSECOLS(FILTER(CFC_PROD!A:L,CFC_PROD!A:A=ODS_PROD!D941," & Chr(34) & " " & Chr(34) & "),3)"
Next
End Sub
I’m trying to write a vba script to return all columns that match a list of numbers. Some numbers have multiple rows of data. I use the choosecols and filter function to return the data which works. Now I’m trying to write a VBA script that does the same thing. Problem now is that it’s not returning the full data and my counter doesn’t continue. Any help is welcome thank you. Code below
Sub all()
Dim shODS As Worksheet
Dim shCFC As Worksheet
Dim indx As Integer
Dim LastROW As Integer
Set shODS = ThisWorkbook.Worksheets("ODS_PROD")
Set shCFC = ThisWorkbook.Worksheets("CFC_PROD")
LastROW = shODS.Cells(Rows.Count, 1).End(xlUp).Row
shODS.Range("N5").Formula = "=CHOOSECOLS(FILTER(CFC_PROD!A:L,CFC_PROD!A:A=ODS_PROD!D941," & Chr(34) & " " & Chr(34) & "), 3,6,9)"
shODS.Range("O5").Formula = "=CHOOSECOLS(FILTER(CFC_PROD!A:L,CFC_PROD!A:A=ODS_PROD!D941," & Chr(34) & " " & Chr(34) & "),3)"
For indx = 2 To LastROW
shODS.Range("K" & indx).Formula = "
=CHOOSECOLS(FILTER(CFC_PROD!A:L,CFC_PROD!A:A=ODS_PROD!D941," & Chr(34) & " " & Chr(34) & "),3)"
Next
End Sub