How do i compare properties and display it?

I am currently stuck with my program because my knowledge of VBA is still very limited.
It is a basic interface where a summary will display the properties of different ranges when pressed.

I am having trouble displaying compared the result in the summary column.

I have uploaded the excel file and also pasted my code here. Could anyone help me?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Try such code (event handler) in the same module
It's now only dealing wityh columns B and C. Column D would be very similar to column C, but column E would be more complicated.
But let's start from checking if this produces desired output for columns B and C:

Please Login or Register  to view this content.

The code is universal. Only the variables are named with German names. The code would work exactly the same if names of variables were variable1, variable2, etc. So you can change these names yourself using Change option - ctrl+H

I cannot post my code.

Please see the attachment.

As I understand, desired output for E24 when all ranges are selected could look like:

Car (Range A, Range B) / Apple (Range C)

Below is sample code for this task (I left the rest of the code (almost) untouched because I like structural programming used by mehmetcik.

Please Login or Register  to view this content.

it can be called at the end of MinMax sub:

Please Login or Register  to view this content.

In the attachment you will find a bit more advanced version, which shows only Item without listing Range(s) if all displayed Item types are the same.

Please Login or Register  to view this content.

Also included in code some measures to use large H in Hz abbreviation. like kHz, MHz, etc. but don't list it here - see the code in attachment.

Thank you Kaper & mehmetcik for the great help. B24 & E24 is the exact outcome i was looking for.
Your codes have improved my way of programming and I am learning alot from your code as a beginner.

I am now facing a slight issue. The column C24 & D24 should display the range instead of the highest/lowest Hz. The C24 outcome in "Range.xslm" is the desired outcome. However, from Range2 onwards it became slightly different.

What should i do? Thanks

The code for D24 shall look not for the smallest but for the largest value,
so not 10^307 as initial value but for instance 0 or -10^307
and of course checking if left limit (or may be one shall look on a right limit) is larger then largest so far value.

But as there is already nicely working MinMax procedure, it will be enough/better to change only parts of it, which are responsible for writing results into C24 and D24.
For instance that way:

Please Login or Register  to view this content.

The code now works perfect. However, i am finding some difficulty in understanding the code. Most of the unknown syntaxes i will be able to find on Google but there are lines that still leaves me confused..

What and where is MinMax declared?

Please Login or Register  to view this content.

Where is Cells(A,3)

Please Login or Register  to view this content.

what is NV/OV?

Please Login or Register  to view this content.

Could you also explain how this is derived?

Please Login or Register  to view this content.

I have also noticed that you have included both private subs (Show_by_type & Show_by_type_old) is that required?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

It would be of great help if there are extra explanation comments provided

Pardon my ignorance and questions. I personally find the first version (before MinMax) easier to understand. However, i'm always keen on learning a higher level of programming.

My VBA skills are limited but i am trying hard.

Thank you for your time!

I cannot post the Code.

I have inserted some comments in the code for you.

Hi.

On the Programs Sheet you Data is in B20, B21 or B22

However on the Properties Sheet this Data links to C3, C4, C5

So B20 links to C3 20 - 3 = 17. The 17 allows us to link the data on both sheets simply.

In MiniMax A =20 or A = 21 or A = 22 depending on which button you press. I used 20 as an example.

That is the beauty of the solution. you can have literally hundreds of buttons using the same code.


Try this:

On a new Sheet

Formula for A1 ="Private Sub Range" & CHAR(INT(ROW()/5)+65) &"_Click()"
Formula for A3 ="MinMax (" & INT(ROW()/5 +20) &")"
Formula for A5 End Sub

Copy A1 to A5

Paste into A1 to A40

Now for the neat bit.

Copy A1 to A40 and paste into a visual basic module.

Please Login or Register  to view this content.

-17 is the difference between locations of ranges in both sheets. The ranges start from row 3 in Properties and from row 20 in Program

A=20 for first range is because output goes to row 20 for first range and so on.

Basically - yes, you can add more ranges. Just note that you will probably need to move the summary from row 24 down (or may be above?)

As for explanation to last sub:

Please Login or Register  to view this content.

One last comment.

After some training you got here, I'd think of different approach than creating dozens of command buttons.
Some parts of code could be reused. some just slightly modified.
I'd load into one listbox a list of all available ranges
then the button would transfer the selected (in inbox) range into the table, writing it into first free row, and remove the selected range from the listbox
the second button wouuld do a reset - clearing output table and loading back all ranges into listbox.
The buttons and the listbox could be either on the worksheet (as they are now - and I'd probably opt for this solution) or cold be on a Userform to have a more "professional look".

PS.

Please Login or Register  to view this content.

is no longer used, and can be removed from the code (as was Show_by_type_old)

Please Login or Register  to view this content.

As the solution works, it would be a good idea to mark thread as Solved. (not just mentioning it in the text).
Above your first post you will find Thread Tools to do it.

It would be lovely if you could also "add some reputation" to those who helped by clicking a small star icon in lowerleft corner of our posts and then confirm in pop-up message (note that some browsers block this pop-up).
As opposite to many other sites, you can grant reputation to more than one users/posts.

Good day everyone,

I require to make some slight changes. Hence, i have decided to reopen this thread. I am now facing a little trouble with using the MinMax function.

I have uploaded the excel sheet with the new required output listed inside. All of the codes remains untouched from Zesyt5 .
What is different from in this new program is that the Properties are listed in a slightly more complicated format. e.g Merged cells etc.


I hope you guys are still willing to lend a hand. Thank you!

Best regards,

Christian

Ok. Try this Version.

The requirement is ill defined. I fyou need low values from both lower and upper limits then from

1khz-10Mhz
200Mhz-6Ghz
80Mhz-6Ghz

the result will be
1khz-10Mhz

not
1khz-6Ghz

200Mhz-20Ghz
for highest range seems to be OK as it's combination of
200Mhz-6Ghz
and
150Mhz-20Ghz


PS. Perhaps you don't care but the units are
Hz
kHz
MHz
GHz
...