cortexnotion
Board Regular
- Joined
- Jan 22, 2020
- Messages
- 150
- Office Version
- 2013
- Platform
- Windows
Good Morning,
I stumbled across this forum through some research and I was hoping you could help me with some VBA code.
I have a data set A4:P7000 (row 3 is Headers) which I have set up as an array. I need to remove the following things from the data before copying the matching rows to the new workbook in Sheet1 from cell A2. The curveball is I need to copy the row in reverse order leading with Column P to A. My string criteria is:
- Remove any values from Column F equal 0
- Remove any values from Column A and Column B that match the names in the NamesArray anywhere in the string (wildcard?) and case not to be sensitive
I've had a go setting my arrays and starting my code but not sure on how to compare the range array with the name array and factor in my wildcard need. I'm also not sure how to copy the data to the new workbook.
Many thanks
Chris.
I stumbled across this forum through some research and I was hoping you could help me with some VBA code.
I have a data set A4:P7000 (row 3 is Headers) which I have set up as an array. I need to remove the following things from the data before copying the matching rows to the new workbook in Sheet1 from cell A2. The curveball is I need to copy the row in reverse order leading with Column P to A. My string criteria is:
- Remove any values from Column F equal 0
- Remove any values from Column A and Column B that match the names in the NamesArray anywhere in the string (wildcard?) and case not to be sensitive
I've had a go setting my arrays and starting my code but not sure on how to compare the range array with the name array and factor in my wildcard need. I'm also not sure how to copy the data to the new workbook.
VBA Code:
Option Compare Text
Sub myarray()
Dim sh1 As Worksheet, wb2 as Workbook, sh2 as Worksheet
Dim dataarray As Variant, namesarray As Variant,
Dim i As Long
Set sh1 = Worksheets("RawData")
Set wb2 = Workbooks.Open("C:\Userx\tempwork\new.xlsm")
dataarray = Range("A4:P7000").Value
namesarray = Array("JOHN","CLAIRE","PETER","MICHELLE","PAUL","CHRIS")
For i = LBound(TheArray) To UBound(TheArray)
If RangeArray(i, 6) > 0 And _
RangeArray(i, 10) Like ??? 'How to perform comparison with namesarray?
Many thanks
Chris.