VBA Loop Through Range and Copy/Paste to Another Sheet

[BrandonL]

I need VBA that loops through a single column on one sheet, finds any cell with a "-", then copy and paste it to another separate sheet. The column being looped through will be dynamic.

The first sheet is named "PRO Schedule" and the range starts at "C5". I need it to loop down the column to the last entry and find the part numbers. I think searching for "-" will give me all of them. Here is a screenshot of this sheet.

The VBA will be in a button on the sheet that the found values will be pasted. This sheet is called "SIMPLE Schedule" and the first cell where the found values are to be pasted is "B5".

I have searched for a solution on the forums but can not make anything fit. Any help would be greatly appreciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

I need VBA that loops through a single column

Looping through worksheet cells is generally not required and can be a relatively slow process.

finds any cell with a "-", then copy and paste it to another separate sheet.

Hmm, I'm wondering if that really is the case?
That column has a lot of entries "Release +/-" which contain a "-" but I assume you do not want copied?
Also, do you want the values in parentheses containing dashes as well as the values not in parentheses containing dashes?

Anyway, as a starting point, try running this code from the 'SIMPLE Schedule' sheet in a copy of your workbook.

Sub PartNumbers()
  With Sheets("PRO Schedule")
    With .Range("C4", .Range("C" & Rows.Count).End(xlUp))
      .AutoFilter Field:=1, Criteria1:="*-?*"
      If .SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).Copy Destination:=Range("B5")
    End With
    .AutoFilterMode = False
  End With
End Sub

BTW, for the future, you can make it easier for helpers by providing your sample data in a form that can be copied easily.
See XL2BB

 

[BrandonL]

Peter,

Thank you so much for taking a look and replying!

Hmm, I'm wondering if that really is the case?
That column has a lot of entries "Release +/-" which contain a "-" but I assume you do not want copied?
Also, do you want the values in parentheses containing dashes as well as the values not in parentheses containing dashes?

Good catch, and yes you are correct that I would only want the values not in parentheses (excluding "Release +/-") .


I have ran the code you gave me to start with, and it is giving me what I asked for in the "SIMPLE Schedule" sheet, which is great! But it is filtering and altering the "PRO Schedule" which I can not have. Any ideas on how to get the values to the "SIMPLE Schedule" without altering the "PRO Schedule"?

Also, thanks for the XL2BB link. I am adding now so let me know if you need a snippet to work with.

Brandon

 

[Peter_SSs]

I have ran the code you gave me to start with, and it is giving me what I asked for in the "SIMPLE Schedule" sheet,

Apart from it would have also given you the values in parentheses?

But it is filtering and altering the "PRO Schedule"

Is the issue that PRO Schedule was already filtered before the code runs and my code unfilters it at the end?
If not, can you detail in what way my code alters PRO Schedule?


BTW, do you really need vba for this? Would you consider putting this formula in cell B5 of 'SIMPLE Schedule' and all the part numbers should appear.
If you make the 1000 in the formula big enough to cover any amount of data you are likely to have in 'PRO Schedule' then it should automatically update whenever the data in 'PRO Schedule' is updated without the need to press any buttons or run any further code.

=LET(pno,'PRO Schedule'!C5:C1000,FILTER(pno,(ISNUMBER(FIND("-",pno)))*(ISERROR(FIND("+/-",pno)))*(ISERROR(FIND("(",pno))),""))

 

[BrandonL]

Apart from it would have also given you the values in parentheses?


Is the issue that PRO Schedule was already filtered before the code runs and my code unfilters it at the end?
If not, can you detail in what way my code alters PRO Schedule?


BTW, do you really need vba for this? Would you consider putting this formula in cell B5 of 'SIMPLE Schedule' and all the part numbers should appear.
If you make the 1000 in the formula big enough to cover any amount of data you are likely to have in 'PRO Schedule' then it should automatically update whenever the data in 'PRO Schedule' is updated without the need to press any buttons or run any further code.

=LET(pno,'PRO Schedule'!C5:C1000,FILTER(pno,(ISNUMBER(FIND("-",pno)))*(ISERROR(FIND("+/-",pno)))*(ISERROR(FIND("(",pno))),""))

Peter,

Using the LET function worked perfectly, so thank you for getting me on the right path!

Unfortunately, now the file is being used on Excel 2016 and the LET function is not supported in that version. I had written it on Excel 365 so was not aware.

Is there another function that can do the same (even if the compute time is longer) that is compatible with the older Excel version? Any insight would be appreciated again.

Brandon

 

[Peter_SSs]

Using the LET function worked perfectly, so thank you for getting me on the right path!

Unfortunately, now the file is being used on Excel 2016 and the LET function is not supported in that version. I had written it on Excel 365 so was not aware.

Is there another function that can do the same (even if the compute time is longer) that is compatible with the older Excel version?

Yes. Try this in B5 of 'SIMPLE Schedule' instead and copy down as far as you might need.

=IFERROR(INDEX('PRO Schedule'!C:C,AGGREGATE(15,6,ROW('PRO Schedule'!C$1:C$1000)/(ISNUMBER(FIND("-",'PRO Schedule'!C$1:C$1000))*ISERROR(FIND("+/-",'PRO Schedule'!C$1:C$1000))*ISERROR(FIND("(",'PRO Schedule'!C$1:C$1000))),ROWS(B$5:B5))),"")

 

[BrandonL]

Yes. Try this in B5 of 'SIMPLE Schedule' instead and copy down as far as you might need.

=IFERROR(INDEX('PRO Schedule'!C:C,AGGREGATE(15,6,ROW('PRO Schedule'!C$1:C$1000)/(ISNUMBER(FIND("-",'PRO Schedule'!C$1:C$1000))*ISERROR(FIND("+/-",'PRO Schedule'!C$1:C$1000))*ISERROR(FIND("(",'PRO Schedule'!C$1:C$1000))),ROWS(B$5:B5))),"")

This is great, thanks!

To cover everything that I needed to find, I had to add some other items to FIND to the original formula. I am not having any success adding these to the new formula. When I put in another FIND, the cell then becomes blank.

I need to add:

"7161675"
"55369603"
"AA"
"AB"
"AC"
"AG"

Thanks,

Brandon

 

[Peter_SSs]

This looks like it may be a very different task.
To assess whether a formula result is possible/feasible could we have some new representative sample data and expected results (with XL2BB)?

Does any new suggestion still also have to work in Excel 2016?

 

[BrandonL]

This looks like it may be a very different task.
To assess whether a formula result is possible/feasible could we have some new representative sample data and expected results (with XL2BB)?

Does any new suggestion still also have to work in Excel 2016?

It is an expanded task, just more results to return if applicable. And, yes, it still needs to be 2016 compatible. If not, the original formula would have this project done!

It was easy to add to the LET Function originally provided. See below:

=LET(pno,'PRO Schedule'!C5:C2500,FILTER(pno,(ISNUMBER(FIND("7161675",pno))+(ISNUMBER(FIND("55369603",pno))+(ISNUMBER(FIND("AA",pno))+(ISNUMBER(FIND("AB",pno))+(ISNUMBER(FIND("AC",pno))+(ISNUMBER(FIND("AG",pno))+(ISNUMBER(FIND("-",pno))*(ISERROR(FIND("+/-",pno))))))))))))

I can provide some XL2BB content once I have the plugin working.

Thanks again for the help. Very appreciated.

Brandon

 

[Peter_SSs]

It was easy to add to the LET Function originally provided. See below:

=LET(pno,'PRO Schedule'!C5:C2500,FILTER(pno,(ISNUMBER(FIND("7161675",pno))+(ISNUMBER(FIND("55369603",pno))+(ISNUMBER(FIND("AA",pno))+(ISNUMBER(FIND("AB",pno))+(ISNUMBER(FIND("AC",pno))+(ISNUMBER(FIND("AG",pno))+(ISNUMBER(FIND("-",pno))*(ISERROR(FIND("+/-",pno))))))))))))

If that formula works in 365 then I think this should work in 2016 as well

=IFERROR(INDEX('PRO Schedule'!C:C,AGGREGATE(15,6,ROW('PRO Schedule'!C$5:C$2500)/((ISNUMBER(FIND("7161675",'PRO Schedule'!C$5:C$2500))+ISNUMBER(FIND("55369603",'PRO Schedule'!C$5:C$2500))+ISNUMBER(FIND("AA",'PRO Schedule'!C$5:C$2500))+ISNUMBER(FIND("AB",'PRO Schedule'!C$5:C$2500))+ISNUMBER(FIND("AC",'PRO Schedule'!C$5:C$2500))+ISNUMBER(FIND("AG",'PRO Schedule'!C$5:C$2500))+ISNUMBER(FIND("-",'PRO Schedule'!C$5:C$2500)))*ISERROR(FIND("+/-",'PRO Schedule'!C$5:C$2500))),ROWS(B$5:B5))),"")