Better way to code the following 1

[nelson97]

Hi, this is what I currently have and wanted to see if there was another way of writing this. What I'm trying to do with the line marked with *** is to basically make sure that cell AresX doesn't exceed 45 and go below 0.

With Sheets("Sheet3").Range("AresX")
*** If .Value >= 0 And .Value < 45 Then
.Value = .Value + 1
End If
End With

Thanks!

 

[Quandan]

Except for the fact that you implicitly assume that the range AresX does only contain 1 cell, I think there is not much efficiency to gain here...

You might want to make your code run smoothly even if the range contains more than one cell by using
Sheets("Sheet3").Range("AresX").Cells(1,1)

or

Dim rCell as Range
For each rCell in Sheets("Sheet3").Range("AresX").Cells
With [...]
End With
Next

Another thing you might consider is using variables or constants for "Sheet3" and "AresX" wherever possible to reduce maintenance costs of your code (if it changes, you only need to change it in one location (initiation of the variable or constant) in stead of throughout your code).

Kind regards,


Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - Dutch supportsite for spreadsheetusers)

http://www.quandan.nl

 

[MakeItSo]

You can rewrite it like this:

Range("AresX") = IIf(Range("AresX") > 0 And Range("AresX") < 45, Range("AresX") + 0.1, Range("AresX"))

(All in 1 line)
If you consider this shorter...
;-)

Regards,
Andy

[blue]An eye for an eye only ends up making the whole world blind. - "Mahatma" Mohandas K. Gandhi[/blue]

 

[TonyJollans]

Or you can coerce the condition to your increment ..

With Sheets("Sheet3").Range("AresX")
    .Value = .Value - (.Value >= 0 And .Value < 45)
End With

but it isn't an improvement - just different (which is what you asked).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Zathras]

Here is another variation:

  With WorksheetFunction
    [AresX] = .Max(0, .Min([AresX] + 1, 45))
  End With

{The range name already includes the sheet reference.)

 

[TonyJollans]

Zathras,

Nice thinking, but it fails for non-integer values (although that may not matter for the OP).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Zathras]

Tony, works for me with non-integer values (Excel 97). What set-up did you use to induce failure?

 

[TonyJollans]

Well, I guess it depends what you mean by works - you may be correct .

The spec is unclear (or unknown), but if the cell contains 44.5, your code gives a result of 45 while the original code gives aresult of 45.5 (which violates the stated condition).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Zathras]

I would say that the original code was buggy since the spec from the same post said

to basically make sure that cell AresX doesn't exceed 45 and go below 0.

As you said, the original code will take 44.5 and turn it into 45.5, but that exceeds 45 which violates the spec (as I read it).

 

[TonyJollans]

Agreed


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[nelson97]

Thanks all for the informative posts. Zathras, thanks, you nailed it.