Select case .... End select

[montecarlo2012]

I am trying to code the body metabolic rate by this code but I don't know where I make the mistake, please, somebody give me a hand here.

GENDER	F			BMR		BASAL METABOLIC RATE
WEIGHT	115	kilograms
HEIGHT	175	cm
AGE	59	year

Option Explicit
Sub bodymetabolic()
Dim gender As String
Dim weight, height, age As Integer
Dim bmr As Long

    gender Range("B1")
    weight Range("B3").Value
    height Range("B5").Value
    age Range("B7").Value
   
    Select Case UCase(genger)
           Case "F": bmr = 447.593 + (9.247 * Range("B3").Value) + (3.098 * Range("B5").Value) - (5.677 * Range("B7").Value)
           Case "M": bmr = 88.362 + (13.397 * Range("B3").Value) + (4.799 * Range("B5").Value) - (5.677 * Range("B7").Value)
          
    End Select
   
    Range("F1") = bmr
   
          
   
   
End Sub

 

[Stegus]

The line:
Select Case UCase(genger)

Word: genger

I suppose that should spell: gender
the same as the variable= Dim gender As String

 

[hiker95]

montecarlo2012,

Sample raw data:

Book1
	A	B	C	D	E	F	G
1	GENDER	F			BMR		BASAL METABOLIC RATE
2
3	WEIGHT	115	kilograms
4
5	HEIGHT	175	cm
6
7	AGE	59	year
Sheet1

After the macro:

Book1
	A	B	C	D	E	F	G
1	GENDER	F			BMR	1718.205	BASAL METABOLIC RATE
2
3	WEIGHT	115	kilograms
4
5	HEIGHT	175	cm
6
7	AGE	59	year
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub bodymetabolic_V2()
' hiker95, 10/30/2014, ME815166
Dim gender As String
Dim weight As Long, height As Long, age As Long, bmr As Double
gender = Range("B1").Value
weight = Range("B3").Value
height = Range("B5").Value
age = Range("B7").Value
Select Case UCase(gender)
  Case "F": bmr = 447.593 + (9.247 * Range("B3").Value) + (3.098 * Range("B5").Value) - (5.677 * Range("B7").Value)
  Case "M": bmr = 88.362 + (13.397 * Range("B3").Value) + (4.799 * Range("B5").Value) - (5.677 * Range("B7").Value)
End Select
With Range("F1")
  .Value = bmr
  .NumberFormat = "0.000"
  .Font.Bold = True
  .HorizontalAlignment = xlCenter
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the bodymetabolic_V2 macro.

 

[montecarlo2012]

thank you hiker95 work perfect.

 

[hiker95]

montecarlo2012,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Peter_SSs]

Dim weight, height, age As Integer

Two further remarks about the above line of code as advice for the future.

1. You have only declared "age" as integer. "weight" and "height" will be Variant type because you haven't stated a type for them.

2. vba converts Integer data type to Long data type to work with it so you might as well declare it as Long to start with (& it's shorter to type )

hiker has addressed both of those issues with this line:

Dim weight As Long, height As Long, age As Long

 

[montecarlo2012]

thank you Peter for your advise. see you soon