Locate Numbers 26 and higher

**CHRISTINEKENDALL93** · 07-03-2019, 10:18 AM

I have some code that searches a range and does a count of the values and writes the results to cell A18.

Please Login or Register  to view this content.

I need to make a change to the code to search the ranges but look for values over 25 and write the results to cells B3, B4, B5 or B6. Some ranges may or may not have sets of numbers in them. I’m only interested in sets of numbers that have a value of 26 (2nd number in set) or higher in them. An example would be 8-26 or 7-35. Below is sample data and when I’m finished designing my sheet it will have data above, below the search ranges. See example below with expected result.

General Notes:

- I can not use a formula because their are issues with some other VBA code I run in the same cell range.
- I need hard code for the search ranges (I have many ranges in different locations on the sheet) so I can add or delete in the code.
- The sheet name is Sheet 1 and their is only one sheet in the workbook.
- Using Excel 2007.
- The search range cells are formatted as text.
- The write cells are formatted as general.

Sample data:

The code searches ranges D3:D12, F3:F12, H3:H12 for sets of numbers that are over 26 or higher. The first found set of numbers would be 10-26 in cell E9 and the 2nnd positive set of numbers would be 5-35 located in cell F10. The code would then write the cell location to cells B3, B4, B5 or B6. If their are NO positive results could the code write a 0 in each of the four cells.

If you have any questions please ask so I can clarify any issue(s). Thanks so much for all your help.

**Marc L** · 07-03-2019, 10:36 AM

Tricky as your attachment seems to not reflect your explanation & picture …

**CHRISTINEKENDALL93** · 07-03-2019, 11:08 AM

The correct Excel sheet has been uploaded, thanks.

**jolivanes** · 07-03-2019, 01:19 PM

Going by Post #1. Have not opened attachment.

Please Login or Register  to view this content.

**CHRISTINEKENDALL93** · 07-03-2019, 02:01 PM

Hi jolivanes

Thank-you for your reply and code. Your code works but it's not writing anywhere on my real sheet. It looks really good but I may owe you an apology as I didn't specify that I need the code to write to hard coded cells. In my sample data above to cells B3:B6. I'm hoping I haven't caused a big problem. Sorry

**jolivanes** · 07-03-2019, 05:58 PM

The code looks for the first empty cell below B3 so if you have anything below B6 it could be anywhere in Column B (Below B6 anyway). Check to the end of Column B.

**jolivanes** · 07-03-2019, 06:08 PM

For the specific range to paste the addresses into, try this.

Please Login or Register  to view this content.

**CHRISTINEKENDALL93** · 07-04-2019, 10:46 AM

Thank-you again for the code. The issue I have is I’m still designing my sheet and your code works today but next week when I have to made a design change it will not work or will overwrite something I require.

I really have to have hard code so I can control where the write occurs and I can easily make the necessary changes. You are very proficient in your code writing I on the other hand am not and would have a lot of trouble make the necessary changes. If at all possible can we make the necessary change to the code? Thanks very much.

**jolivanes** · 07-04-2019, 11:31 AM

Which part of the supplied suggestion are you referring to?
Have you tried the code? It is "hard coded"

An explanation might help.

Please Login or Register  to view this content.

**CHRISTINEKENDALL93** · 07-04-2019, 03:38 PM

Thank-you so much for the explanation, now I get it. The code works perfect. A last request, if possible: if their are no values over 25 can you have have the code write a zero in the first cell (B3 in the sample above) that is used to write a positive result if their is one?

Again, thank-you

**jolivanes** · 07-04-2019, 04:15 PM

I don't quite get that. You are checking 3 ranges with 10 cells in each range which relates to a possible 30 cell addresses. This obviously is not the case because you have only four cells to enter the possible addresses.
Maybe explain the relationships.
Also let us know when to change from Cell B3 to B4, and B5 and/or B6, even if there is no Cell Address to be entered.
If there are no addresses at all to be entered you can add this to the bottom of the code (just before "End Sub")

Please Login or Register  to view this content.

**Marc L** · 07-04-2019, 09:35 PM

Because of these unexplained relationships I keep my code on my side
('cause too of the previous post as same 'issues' within this one) …

I wanna know for example if there are 10 data in the search ranges and 7 values are matching so what happens ?!
Still always data in the 4 cells in B3:B6 only ?

**jolivanes** · 07-04-2019, 10:17 PM

Hey Marc.
Don't hold back. I'm learning also.

**Marc L** · 07-05-2019, 06:20 AM

See the forum rules to learn how to well create a thread …

Any answer to post #11 & 12 ?‼

**CHRISTINEKENDALL93** · 07-05-2019, 11:26 AM

Hi jolivanes

I just finished testing and it all works great. Thank-you so much for all your help it’s greatly appreciated.
Christine

**CHRISTINEKENDALL93** · 07-05-2019, 11:35 AM

Hi Marc L

I can see why you would have trouble with my explanation. It should have read that I only expect 1 occurrence of over 25 therefore having 4 cells to write would be adequate, my fault. When jolivanes posted an explanation of his code that made all the difference and I highly recommend this to all who read this post. For most of us we are not that technical VBA readers so it really make a huge difference in understanding code that is written. VBA is like leaning a foreign language, hard in the beginning but easy when you understand the issues.

Christine

**Marc L** · 07-05-2019, 11:49 AM

Jolivanes, private message sent !

Christine,
to understand why this 4 fixed cells we post questions in order to clarify your need
- so nothing to do about VBA but just about explaining clearly your need - and we had no answer …
What if 10 cells with data and 7 are matching, what must be the result and where ?

Locate Numbers 26 and higher

LinkBack

Thread Tools

Rate This Thread

Display

Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Re: Locate Numbers 26 and higher

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Random Numbers Attempts Must Be Higher Than Made

Compare two numbers - the closer they are the higher the % score in a different cell

possible to locate higher hierarchical list of HTML code?

[SOLVED] The user cannot type in numbers higher than 20. How to prevent him from doing that?

[SOLVED] Look at Range of numbers and return the next number higher than x

Locate two equal numbers in an array of numbers

Can I flag numbers that are higher/lower than a range of #'s

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions