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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 21:25:57  来源:igfitidea点击:

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

excelvbaexcel-vbaexcel-2010

提问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 ) ]。