CODEWITHSUNDEEP
vbaexcelexcel-2010
mad5245
mad5245

Reputation: 394

VBA Loop through values in a column and changing the next column based on the value

I have a piece of code that takes the value in a cell and sets a different cell based on the value. It is a simple code, I just can not figure out how to loop it to go through the whole column. Can some one guide me to how this can be done? Thanks in advance!

Here is the code I have:

Dim pH As Single, Program As String
pH = Range("D2").Value

If pH <= 7.8 Then
     Program = "Neutral"
Else
     Program = "Alkaline"

Range("E2").Value = Program

Upvotes: 3

Views: 8088

Answers (3)

MrPandav
MrPandav

Reputation: 1861

this might be the optimum solution using VBA :

Sub mysub()

    Set Wksht = ThisWorkbook.Sheets("Sheet1")  'the Worksheet name you want to fetch data from
    Wksht.Select
    Set myRange = Wksht.Range("D2", Range("D65536").End(xlUp))

    For Each myCell In myRange.Cells

        If myCell > 7.8 Then
            myCell(1, 2) = "Natural" 
        Else
            myCell(1, 2) = "Alkaline"
        End If

    Next myCell

End Sub

Blockquote : myCell(1,2) is refering to the current row and E column ..it will use relative address from the current cell address....so if Current cell address is D2 ..myCell(1,2) is eqvivalent to E2 like wise myCell(1,3) means F2, [ myCell(row , column ) ].

Upvotes: 2

andrewmours
andrewmours

Reputation: 139

If you want to do this in VBA, you can create a 'Do Until' Loop to go until there are no more values in the column:

Sub Looper()

Dim i as Integer
Dim Sel as String
Dim MoveDown as String
Dim pH as Single
Dim Program as String

i = 2
MoveDown = "YES"

Do Until MoveDown = "DONE"
    Sel = "D" + Replace(Str(i), " ", "")
    ph = Range(Sel).Value

    If pH <= 7.8 Then
        Program = "Neutral"
    Else
        Program = "Alkaline"
    End If

    Sel = "E" + Replace(Str(i), " ", "")
    Range(Sel).Value = Program
    i = i + 1
    Sel = "D" + Replace(Str(i), " ", "")
    If Range(Sel).Value = "" Then
        MoveDown = "DONE"
    End If
Loop
End Sub

Upvotes: 3

matzone
matzone

Reputation: 5719

You don't need VBA yet

set formula in your E2 '= IIF(D2<=7.8;"Neutral";"Alkaline")'

And you can copy it to below E columns

Upvotes: 7

Related Questions