CODEWITHSUNDEEP
excelvba
user2127447
user2127447

Reputation: 43

How to loop VBA function through multiple rows rather than hard code cells?

I have 3 functions:

The problem is, my functions read values from exact cells and place the calculated values into exact cells, which means I can't repeat the calculations for every row below.

See this screenshot:

BMI Calculator Data

This is my code:

Option Explicit

Const KgRate As Double = 0.45359237     'number of kg in one pound
Const PoundsInStone As Integer = 14     'number of pounds in one stone

Const InchesInFeet As Integer = 12      'number of inches in one foot
Const CmsInInch As Double = 2.54        'number of centimetres in an inch

Public weightInKilograms As Double

Public finalHeight As Double

**' FUNCTION 1**
Public Function heightInMetres()

Dim numberOfFeet As Integer
Dim numberOfInches As Integer
Dim heightInInches As Integer
Dim heightInCms As Integer

numberOfFeet = Range("C4").Value
numberOfInches = Range("C5").Value

heightInInches = (numberOfFeet * InchesInFeet) + (numberOfInches)
heightInCms = heightInInches * CmsInInch
finalHeight = heightInCms / 100

Range("C7") = finalHeight

End Function

**' FUNCTION 2**
Public Function weightInKilos()

Dim stonesEntered As Integer
Dim poundsEntered As Double
Dim stonesToPounds As Double

stonesEntered = Range("C10").Value
poundsEntered = Range("D10").Value

stonesToPounds = stonesEntered * PoundsInStone

weightInKilograms = ((stonesToPounds + poundsEntered) * KgRate)

Range("E10") = weightInKilograms

End Function

**' FUNCTION 3**
Public Function calculateBMI()

Dim BMI As Double

BMI = weightInKilograms / (finalHeight ^ 2)

Range("F10") = BMI

End Function

**' MAIN PROCEDURE**
Public Sub BMICalculator()

heightInMetres
weightInKilos
calculateBMI

End Sub

1) What is the simplest way to repeat the weight and BMI calculations for the 3 rows below week 1 (which is row 10 in Excel)?

2) Is it possible to (simply) run the code continuously, e.g. as soon as I update the height and weight cells, the calculations are redone?

3) If there are any (simple) enhancements to the code you can suggest, please do. :-)

Thanks in advance,

Pete

Upvotes: 1

Views: 184

Answers (1)

CLR
CLR

Reputation: 12289

I've changed one of your functions here, if you can follow it you should have no problem converting the others:

Public Function heightInMetres(numberOfFeet As Integer, numberOfInches As Integer)

 Dim heightInInches As Integer
 Dim heightInCms As Integer

 heightInInches = (numberOfFeet * InchesInFeet) + (numberOfInches)
 heightInCms = heightInInches * CmsInInch
 finalHeight = heightInCms / 100

 heightInMetres = finalHeight

End Function

So now, just enter the following into the cell eg. C7:

=heightinmetres(C4,C5)

Where the number of feet are in C4 and inches in C5 (in your example)

Upvotes: 3

Related Questions