Search for Numbers

Hi

I’m looking for help to develop code to find numbers on my Excel sheet and then write the results. I’ll be using it with other code so I can not use a formula.

Details:

- Project1 is the sheet code name.
- The search range cells and the write cells are formatted as number.
- I can only use hard code for the search range and write cell range. There will never be a change in cell location or sheet name.

The code searches cell ranges E3:E14, H3:H14 (and other ranges to be added by me) to locate numbers from 1 to 12 and also finds data in the adjacent cell to the right would be a positive result. When this condition is met the code would then search cell range A3:A14 for the found number and write the found number in the adjacent cell to the right. There will always be found numbers. If the code finds two of the same numbers could it write “err” or something sililiar.

Examples and expected results

The first found number would be 3 in cell E5 and would be a positive result because there is data in cell F5. Using the found number 3, the code would then search the range A3:A14 for the number 3, cell A5, and then write 3 in the cell to the right of it B5. The second example is cell E9 with a 7 in it and has data in cell F9 so the code would write 7 in cell B9. Same for the third example in cell H11.

Thanks for all your help.
fins numbers.png

According to your attachment as a beginner starter :

PHP Code: 

Sub Demo()
            Dim Rg(2) As Range, C%
            Set Rg(0) = [A3].CurrentRegion.Columns(1)
    For C = 6 To ActiveSheet.UsedRange.Columns.Count Step 3
            Set Rg(1) = Cells(C).End(xlDown)
        While Rg(1).Value2
            Set Rg(2) = Rg(0).Find(Rg(1)(1, 0).Value2, , xlValues, xlWhole)
            If Not Rg(2) Is Nothing Then Rg(2)(1, 2).Value2 = Rg(2).Value2
            Set Rg(1) = Rg(1).End(xlDown)
        Wend
    Next
            Erase Rg
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Hi Marc

How are you? Thanks so much for the code. I have not tested it yet. I have a question: how do add say 10 or 20 new ranges to search? Also, this was just test data the real sheet will not be located in say cell range A3:A14. Thanks so much.
Chrintine

I'm fine, thanks !

If you leave only one blank column between the ranges like in your attachment, all is yet set in my code, you have nothing to mod !
[A3:A14] is not harcoded in my script neither …

Hi

Just tested the code and a message comes up saying: Basic syntax error and Range is highlighted in this line" Dim Rg(2) As Range, C%"
Thanks

Bad luck ‼

As it well works on my side under different Excel versions …

Hey Marc

When I ask for hard code I hope that's the correct terminology. That means specific cell references for say search etc. Am I correct in that understanding?

Thanks so much.

Yes you're !

My code just starts from A3, and search for columns since #6 (so F) then jump each time 3 columns until the end of the sheet used range …

Hi

Thank-you for the code but unfortunately I can't use it. I have to be able to change the search range cell location in the code.

Yes, you can easily mod the code.

Hi Marc

I may owe you an apology on your code but I don't understand how to make a change to it. If I could use an example how would I add the new search range K15:K35 to the existing code?
Thanks so much.

As I yet wrote since post #4, my code should still work without any mod ! Did you at least try ?

In case of any issue, just attach the workbook …

Hi

Thanks again for your reply. I sometime feel like I'm speaking Greek and you're speaking well, French. I think I may know why all the confusion. When I say this is sample data (above) and you wrote the code, which works, it searches the sample data and produces the result that I was after. The issue is there is a lot more data that is on the actual sheet that I use. The code would certainly count data, that I do not want counted in the results. So my question is what is the term that I should have used to identity that information to you in the beginning?

The child logic, the how my code works is yet explained in post #4 : just with a single blank column between each range …
As a beginner starter it just follows the 'Examples and expected results' part of your initial post !
The reason why with your new range in columns K:L it still does the job.
So just try ! And in case of any issue, revert, explain and attach the workbook according to your issue.
As a reminder, the better initial explanation and attachment, the better & quicker solution …

Hi Marc

Yes your code works but only under specific conditions that my actual sheet does not support. It would be a massive undertaking to make the necessary changes. It's far easier to have specific cell in the code and so I'm very sorry to have wasted your valuable time.

Christine

So in this case just attach a correct workbook reflecting exactly your real one like it must be when creating a thread …

Poor explanation & attachment, don't be surprised to get a poor code !

To be fair to me I did state the following in the first post:

"I’ll be using it with other code so I can not use a formula".

"I can only use hard code for the search range and write cell range".

According to your initial post, my code does not use any formula …

As hardcoding is often the silly way when things may move, like the little 10 years old girl told me
« If we know from where to start, just only with a single blank column between numbers, obviously it's brainless
to write any range address in the code as at each new change a mod is necessary accordingly, am I right ? »
My anwser was : « I totally agree with you, of course ! »
As she was waiting for a while she said :
« Leave that baby level subject as we need a goal keeper, I want to test my new soccer shoes ! »

So when creating any thread, post at least the code you already have and with a complete &
crystal clear explanation of your need within your global project and a reflecting workbook.
Like it is my code should work whatever your actual code, at least for the few you have explained …

According to your attachment :

Originally Posted by CHRISTINEKENDALL93

- Project1 is the sheet code name.

Wrong : it's just its name but very not its CodeName property …

Originally Posted by CHRISTINEKENDALL93

- The search range cells and the write cells are formatted as number.

Wrong again as some are text formatted !

Originally Posted by CHRISTINEKENDALL93

- I can only use hard code for the search range

Not necessary, useless, often a bad idea …
The good code is the one which does not need any mod when ranges change,
people ask for dynamic, automatic, barely without nothing to maintain …

Anyway a harcoded demonstration as my last beginner starter
where you ' have to be able to change the search range cell location ' :

PHP Code: 

Sub Demo0()
         Dim Rc As Range, Rf As Range
    For Each Rc In [F3:F14].SpecialCells(xlConstants)
         Set Rf = [A3:A14].Find(Rc(1, 0).Value2, , xlValues, xlWhole)
      If Not Rf Is Nothing Then Rf.Copy Rf(1, 2)
    Next
         Set Rf = Nothing
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !