VBA 循环遍历列中的值并根据该值更改下一列
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/16900044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Loop through values in a column and changing the next column based on the value
提问by mad5245
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
回答by matzone
You don't need VBA yet
你还不需要 VBA
set formula in your E2 '= IIF(D2<=7.8;"Neutral";"Alkaline")'
在 E2 中设置公式 '= IIF(D2<=7.8;"Neutral";"Alkaline")'
And you can copy it to below E columns
您可以将其复制到 E 列下方
回答by andrewmours
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:
如果您想在 VBA 中执行此操作,您可以创建一个“执行直到”循环,直到列中没有更多值为止:
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
回答by MrPandav
this might be the optimum solution using VBA :
这可能是使用 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 ) ].
块引用: myCell(1,2) 指的是当前行和 E 列..它将使用来自当前单元格地址的相对地址..所以如果当前单元格地址是 D2 ..myCell(1,2) 是等效的到 E2 就像明智的 myCell(1,3) 表示 F2, [ myCell(row , column ) ]。