Code to Locate Numbers and Write Location

I need the to locate all the sets of numbers in the cell range E2:E20 (I will need to be able to add additional ranges, this is only an example). Using my sample Excel sheet the first number found in the range (1-1 located in cell E3) we would use 1 as a reference and write the cell location to the following cells, below (please make the cell location adjustable). Also, if the code finds two sets of numbers (say a 1-7 in cell E5 (not on the Excel sheet) it should write “error” to cell A1 in that case.

The set of number cells are formatted as text. I searched and searched for existing code and found a close example that possible could be used but just adjusted.

1 to cell A1
2 to cell A2
3 to cell A3
4 to cell A4
5 to cell A5
6 to cell A6
7 to cell A7
8 to cell A8
9 to cell A9
10 to cell A10

So using my Excel example sheet 1-1 (in cell E3) would write as cell E3 in cell A1 and 10-2 (cell E9 would write as A10 in cell A10.

That would do it. Thanks so much for all your help.



Possible Code?

Dim strFirstAddress As String
Dim searchlast As Range
Dim search As Range

Set search = ActiveSheet.Range("A1:A100")
Set searchlast = search.Cells(search.Cells.Count)

Set rngFindValue = ActiveSheet.Range("A1:A100").Find(Text, searchlast, xlValues)
If Not rngFindValue Is Nothing Then
  strFirstAddress = rngFindValue.Address
  Do
    Set rngFindValue = search.FindNext(rngFindValue)
  Loop Until rngFindValue.Address = strFirstAddress

LOCATE CELL ADDRESS.png

Hi Pauleyb

This looks great. I tried it in my day-to-day sheet and it does not work unless I put the formula in A1 (where it is in the sample Excel sheet). The example in the Excel sheet is only a very small example of what I use and so I really have to have a very flexible formulas/VBA code so I can move cell locations within the code/formulas of where the data gets written. Is it possible to place cell locations where it will get written in your formula?

This would really fit the bill. Thank-you so much.

Scott

Hi

Are you still there?

Could you please close this posting. Reason: my helper is gone.
Thank-you