macro coding choices pros & cons

Question

Background: In my excel spreadsheet I have numerous (close to 1,000) external data links in the form of =xyz(). Many of them are in a contiguous block, eg ~800 of them in the same column in consecutive rows. And my workbook will have many (several dozens) of these worksheets. The vast majority of these spreadsheets are identical in terms of where the =xyz() are placed. I'm going to write a macro that searches out these =xyz() cells and then copy/paste values so I can retain the data without the data link.

My questions:

1. Because the vast majority of the spreadsheets will have =xyz() in the same cells, would execution be much faster if I hardcode the locations so my code copies & pastes value those blocks as compared to doing search for all such cells then loop thru each result to do copy/paste (supposing I turn off screen updating)?
2. If I were to go the search & loop path (as opposed to the hardcoding path), is there a way to sense whether the =find() command finds a contiguous block of =xyz() cells so those could be copied/pasted in one step instead of in >800 individual steps?

As I continue to evolve this workbook, the locations of these =xyz() may change and it is a hassle to keep changing the hardcoded locations that's why the search method has appeal. 

Thanks a lot.

Answer 1

1. For specific range to copy and paste, you only have to execute 2 rows of code for 1 time

If you loop through each cell to find the formula, you are executing 2+ rows of code for many times

Of course looping is much slower

2. You cannot use find() in VBA. To locate the cell with formula xyz, for example to test range A1

InStr(Range("A1").Formula, "xyz")

Range.Formula returns the formula of the cell.

InStr check if the returned formula contains "xyz", return 0 if none, return a number (the starting location of xyz) if xyz is found

You can use InStr(Range("A1").Formula, "xyz")<>0  to identify the cells contain xyz