Evaluating values and displaying text

[RohanB]

Hi,

In Excel 2003, all I want my macro to do is look at two adjacent numbers and evaluate them according to a maximum and a minimum. If either is below or above the min and max respectively, it must return "...has not met measured parameters" and vice versa. If any one of a pair of numbers is not in the min-max range, it can disregard all other numbers. Sounds pretty simple so I'm sure there's something small I'm missing.

Here's the code:

Sub Roofbolter_evaluation()

Dim Result As String


For n = 1 To 5

Min = Range("D6").Offset(n).Value
Max = Range("E6").Offset(n).Value

If Min < Range("B6").Offset(n).Value < Max _
And Min < Range("C6").Offset(n).Value < Max Then
Result = "Roofbolter has met measured parameters"
Else
Result = "Roofbolter has not met measured parameters"
Exit For

End If

Next n

Range("A48").Value = Result

End Sub

Thanks

[johncassell]

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Can you upload an example workbook. At first glance it would seem you're overcomplicating this with VBA when a normal Excel compound formula would probably do the trick. I'm not quite clear what the .Offset is meant to be doing hence the request for sight of the workbook.


Regards

[johncassell]

I agree with Richard, maybe I was a bit quick with my post. Please post an example workbook.

[RohanB]

Hi Guys,

I did think that there may be a single compound formula that I could use, but I also thought considering how many numbers must be considered for a single output that a macro might be more convenient.

Below is an example of what I'm working with. I'm not sure if this is what you meant by an example of the workbook.

[code]

Left Boom Right Boom Min Max
520 563 250 750
653 692 250 750
452 498 250 750
300 262 240 280
455 321 300 600

[code]

The ".Offset" was there to use a For loop to scroll through the rows of numbers. Oh, and I forgot to mention that with the code that I posted I would always get "...has met measured parameters", as if it couldn't recognise if any number was out of the range. I thought maybe it was displaying the result of the last row, which was fine, but then adding "Exit For" didn't help either.

Thanks a lot for the help.

[RohanB]

Sorry about the editing issues. I'm trying to get the hang of it.

[johncassell]

a formula would be easier in my opinion.

in cell E2 you would put

Please Login or Register  to view this content.

[RohanB]

Thanks gentlemen, that did the trick. A simple formula was far better. Having done quite a lot of work involving macros at university, I tend to lean toward using them. Thing is, there's usually a formula that can do the same.

Also, I am very glad that I have found this forum and joined it. I can see that it will be invaluable in future!

[Richard Buttrey]

Hi,

As a general rule you should always use standard Excel functionality wherever possible rather than re-invent the wheel by writing VBA code. It will always be quicker.

Often of course this may simply involve getting a macro to perform standard Excel functionality like filtering data etc. And wherever possible try and use loops only as a last resort. You can often avoid the need for these with standard functionality anyway. Deleting rows containing certain values is a classic case in point. Rather than looping through a range of cells looking for a value you can just filter the data and delete a filtered range with one instruction.

Regards