CODEWITHSUNDEEP
excelvba
apang
apang

Reputation: 103

If Function between 2 values is there a smarter way VBA Excel Macro?

Hey guys I apologize if this is vague: I'm wondering if there is a smarter way to write this code:

If i < 6 Then
j = 1
ElseIf i > 6 And i <= 9 Then
j = 2
ElseIf i > 9 And i <= 12 Then
j = 3
ElseIf i > 12 And i < 15 Then
j = 4
Else
End If

The issue is that the value 'i' is dependent on a user input. The max limit for i is 3*N where N is a user input. E.g. In this case the program works where n=5 as you can see it goes up to 15.

But what if I wanted an input of n=10? Sure I could repeat this code but it doesn't seem 'smart' or intuitive. There must be a smarter way to do this. Can anyone help? Thanks. Newb programmer btw.

Upvotes: 0

Views: 7584

Answers (4)

Chris Geatch
Chris Geatch

Reputation: 113

You could just do it as a simple calculation, which will catch all values of N, assuming that the gap between values of j (blocks of 3) isn't affected by N. You just have to deal with the initial block of 6 that converts to 1 (I'm assuming your first line should say <= 6, rather than <6, otherwise your code will ignore a value of i=6:

j = Int(WorksheetFunction.Max(3, i-1) / 3)

If you do really want to do nothing if your value is outside (greater than) the range, you could then test the value of j afterwards

If j <= 3*N Then
    'Your processing goes here, otherwise nothing happens
End If

Upvotes: 1

JohnnieL
JohnnieL

Reputation: 1231

If the number is integer then select case with To keyword

Select case i
  Case Is < 6
    j = 1
  Case 6 To 9
    j = 2
  Case 10 To 12
    j = 3
  Case 13, 14
    j = 4
  Case Else
End Select

Upvotes: -1

buran
buran

Reputation: 14233

If i <= 6 Then
    j = 1
Else
    j = Int((i - 1) / 3)
End If

Upvotes: 2

Gustav
Gustav

Reputation: 55816

Select Case is for such selections:

Select Case i
    Case <= 6 
        j = 1
    Case <= 9
        j = 2
    Case <= 12
        j = 3
    case <= 15 
        j = 4
    Case Else
        j = 5
End Select

Or perhaps simply:

j = i \ 3 - 1

Upvotes: 1

Related Questions