CODEWITHSUNDEEP
vbaexcelloopsvariables
mcadamsjustin
mcadamsjustin

Reputation: 361

Loop through rows and store cell values as variables to run fomrulas, end result in another cell

excel I'd like to scan each row and pull out certain cells and store them as variables. When I get to the end of the row, I want to run a series of formulas and vlookups. I have the formulas and vlookups all set up, but I need to scan each line and then take the result of the those formulas and put them back in another cell. Here's come example code

Dim height As Double
Dim weight As Double
Dim gender As String
Dim newWeight as Double

'I'd like to set the loop here
'for each row do this
height = d2.value
weight = f2.value
gender = k2.value

'then run through my equations
If gender = "F" and weight < 20 Then
 newWeight = weight + 5
ElseIf gender = "F" and weight > 20 Then
 newWeight = weight -2
End If

l2.value = newWeight.value
'Then clear variables and run through the new row

My equations are a little more complex then that, but I think that will get me started.

**EDIT: Do I need to reference what sheet I'm using? I bet I do

Upvotes: 1

Views: 4794

Answers (2)

KyloRen
KyloRen

Reputation: 2741

I am not going to mess with your code , but this should do what you need,

Dim height As Double
Dim weight As Double
Dim gender As String
Dim newWeight As Double
Dim lastrow As Long

 lastrow = Cells(Rows.Count, 1).End(xlUp).row

 For i = 2 To lastrow
    'I'd like to set the loop here
    'for each row do this
    height = CDbl(Range("D" & i).value)
    weight = CDbl(Range("F" & i).value)
    gender = Range("K" & i).value

    'then run through my equations
    If gender = "F" And weight < 20 Then
     newWeight = weight + 5
    ElseIf gender = "F" And weight > 20 Then
     newWeight = weight - 2
    End If

    Range("I" & i).value = newWeight
    'Then clear variables and run through the new row
    newWeight = 0
    height = 0
    weight = 0
    gender = 0
 Next

Upvotes: 2

nightcrawler23
nightcrawler23

Reputation: 2066

See below code

Sub temp()

    Dim height As Double
    Dim weight As Double
    Dim gender As String
    Dim newWeight As Double

    Dim lastRow As Integer
    lastRow = Range("C" & Rows.Count).End(xlUp).Row

    For i = 2 To lastRow
        'I'd like to set the loop here
        'for each row do this
        height = Range("D" & i).Value
        weight = Range("F" & i).Value
        gender = Range("K" & i).Value

        'then run through my equations
        If gender = "F" Then
            If weight < 20 Then
                newWeight = weight + 5
            ElseIf weight > 20 Then
                newWeight = weight - 2
            End If
        End If

        Range("L" & i).Value = newWeight
        'Then clear variables and run through the new row
    Next i

End Sub

You also need to figure out what will happen if weight = 20 :P

Upvotes: 3

Related Questions