Code to Locate Numbers and Write Results

**scott1945** · 03-20-2019, 01:39 PM

I need the to locate numbers 1 TO 10 in MANY cell ranges, example only D3:D12, H3:H12, L3:L12 (I will need to be able to add additional ranges to the code, this is only an example). I have a sample Excel sheet at the end to visually see the details below.

A positive result would be a "*#-#*" in the column to the right of 1 to 10 numbers and also numbers in the second column to the right of the 1 to 10 numbers. Once numbers are found and have a positive result the numbers in the second column are to be written (do not delete after the write) to the following cells (again please make these cells changeable):

(1 to 10 are the search numbers)

1 to cell B3
2 to cell B4
3 to cell B5
4 to cell B6
5 to cell B7
6 to cell B8
7 to cell B9
8 to cell B10
9 to cell B11
10 to cell B12

I need the code to add all numbers in the cells, example from my Excel sheet is number 5 is found in cell D3 would write 10.00 to cell B7 and also cell L12 has a 5 and a 2.00 in which would be written to cell B7 (10.00 plus 2.00 = 12.00).

Using my sample Excel sheet has sample data and the expected result highlighted in yellow.

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

LOCATE NUMBERS.png

**mehmetcik** · 03-20-2019, 02:42 PM

I am sorry

I read this twice. It makes no Sense

**scott1945** · 03-20-2019, 03:21 PM

Hi

Just a quick summary using my Excel sample:

- Find numbers 1 TO 10 in cell ranges, example only D3:D12, H3:H12, L3:L12 (don't hard code, have to add additional ranges). So lets do the first find cell D3 with a 3 in it and also a 1-1 in cell E3 and 10.00 in cell F3 (a positive result). Cell D4 would normally have a number in the cell (1 to 10, my fault here) but their is no set of numbers in cell E4 and no numbers in cell E5 so be not be a positive result.

- Positive results D3 (first positive one), using the numbers in cell F3 would get written to cell B7 and so on.

Hope this helps.

**mehmetcik** · 03-20-2019, 05:30 PM


Sub Test()
MyRanges = Array("D3:D12", "H3:H12", "L3:L12")

For Count = 0 To UBound(MyRanges)

With Range(MyRanges(Count))

'Is this a cell or a range?

If InStr(1, MyRanges(Count), ":") = 0 Then

'This is a Single Cell
SR = .Row
SC = .Column
LR = SR
LC = SC

Else

'This is a range

SR = .Row
SC = .Column
LR = .Rows(UBound(.Value)).Row
LC = .Columns(UBound(.Value, 2)).Column

End If

End With

For CCount = SC To LC
For RCount = SR To LR
If IsNumeric(Cells(RCount, CCount).Value) Then

If Cells(RCount, CCount).Value >= 1 And Cells(RCount, CCount).Value <= 10 And InStr(1, Cells(RCount, CCount + 1).Value, "-") > 0 And IsNumeric(Cells(RCount, CCount + 2).Value) Then

'If You get here then there is a match
'I do not understand what you want done here.
End If
End If
Next
Next
Next

End Sub

**scott1945** · 03-21-2019, 11:50 AM

Hi

Thank-you for your code. Your note on if there is a match what to do should be done is to write the numbers (if more then number is found then it should add to the existing amount) to the following cells:

Number 1 to cell B3
Number 2 to cell B4
Number 3 to cell B5
Number 4 to cell B6
Number 5 to cell B7
Number 6 to cell B8
Number 7 to cell B9
Number 8 to cell B10
Number 9 to cell B11
Number 10 to cell B12

Thanks so much for your help.
Scott

**scott1945** · 03-25-2019, 04:31 PM

Could you close this posting. My helper is gone.
Thank-you

Code to Locate Numbers and Write Results

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Code to Locate Numbers and Write Results

Re: Code to Locate Numbers and Write Results

Re: Code to Locate Numbers and Write Results

Re: Code to Locate Numbers and Write Results

Re: Code to Locate Numbers and Write Results

Re: Code to Locate Numbers and Write Results

Thread Information

Users Browsing this Thread

Similar Threads

Code to Locate Numbers and Write Location

[SOLVED] Modify Myrna Larson's code to write results in rows

[SOLVED] Code to write numbers from one cell to another

how can i write vba code for replacing continuos fraction numbers in on row

[SOLVED] How to search one item in Excel and locate many results?

Locate two equal numbers in an array of numbers

[SOLVED] Wanting to write results to array instead of sheet, results overwriting....

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions