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 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
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 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 ) ].
