VBA 在文本中选择大小写循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11215790/
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 Select Case Loop in Text
提问by Jared
Trying to loop through a range of cells and assigned a label to them based off of the text value in another cell. So if Cell J2 = "This Text" Then Cell A2 = "This Label"
尝试遍历一系列单元格并根据另一个单元格中的文本值为它们分配标签。因此，如果单元格 J2 = "此文本" 那么单元格 A2 = "此标签"
As of now I keep getting a run time error number 424, stating object required
到目前为止，我一直收到运行时错误号 424，说明需要对象
Private Function getPhase(ByVal cell As Range) As String
Select Case cell.Text
Case "Text1"
getPhase = "Label1"
Case "Text2"
getPhase = "Label2"
End Select
End Function
Sub setPhase()
Dim cycle As Range
Dim phase As Range
Set cycle = Range("J2:J10")
Set phase = Range("A2:A10")
For Each cell In phase.Cells
phase.Text = getPhase(cycle)
Next cell
End Sub
回答by Siddharth Rout
You have already got your answers :) Let me do some explaining in my post though :)
你已经得到了答案:) 不过让我在我的帖子中做一些解释:)
You cannot use this.
你不能使用这个。
phase.Text = getPhase(cycle)
.Textis a Readonlyproperty. i.e you cannot write to it but only read from it. You have to use .Value
.Text是只读属性。即您不能写入它，而只能从中读取。你必须使用.Value
Secondly you don't need to define the 2nd range if you are picking values from the same row. You can always us the .Offsetproperty. See this
其次，如果您从同一行中选择值，则不需要定义第二个范围。你总是可以我们的.Offset财产。看到这个
Option Explicit
Sub setPhase()
Dim rng As Range, phase As Range
Set phase = Sheets("Sheet1").Range("A2:A10")
For Each rng In phase
rng.Value = getPhase(rng.Offset(, 9))
Next
End Sub
Function getPhase(ByVal cl As Range) As String
Select Case cl.Value
Case "Text1"
getPhase = "Label1"
Case "Text2"
getPhase = "Label2"
End Select
End Function
Also there is nothing wrong with Select Case cell.Textsince you are only reading from it. However, it is always good to use .Value. Reason being the .Valueproperty returns the actual value of the cell where as .Textproperty returns the text which is displayed on the screen. The limit of Text is approx 8k characters in higher versions of Excel. The .Valueon the other hand can store up to 32k characters.
也没有错，Select Case cell.Text因为您只是从中阅读。但是，使用.Value. Reason.Value属性返回单元格的实际值，而 as.Text属性返回显示在屏幕上的文本。在更高版本的 Excel 中，文本的限制约为 8k 个字符。在.Value另一方面，可以存储多达32K字符。
回答by whytheq
I've changed the loop. This assumes that the two ranges are the same lengths
我改变了循环。这假设两个范围的长度相同
Function getPhase(ByVal cell As Range) As String
Select Case cell.Value
Case "Text1"
getPhase = "Label1"
Case "Text2"
getPhase = "Label2"
End Select
End Function
Sub setPhase()
Dim cycle As Range
Dim phase As Range
Set cycle = ThisWorkbook.Sheets("myexample").Range("J2:J10")
Set phase = ThisWorkbook.Sheets("myexample").Range("A2:A10")
Dim i As Integer
For i = 1 To phase.Cells.Count
phase.Cells(i).Value = getPhase(cycle.Cells(i))
Next i
End Sub
...or as siddharth had suggested use a formula.
......或者正如悉达多建议使用一个公式。
Or do the formula via VBA:
或者通过 VBA 做公式：
Sub setPhase()
Dim phase As Range
Set phase = Excel.ThisWorkbook.Sheets("Sheet1").Range("A2:A10")
phase.Value = "=IF(J2=""Text1"",""Label1"",IF(J2=""Text2"",""Label2"",""""))"
End Sub
回答by Julian Knight
Here is my version:
这是我的版本：
Private Function getPhase(ByVal cell As Range) As String
Select Case cell.Text
Case "Text1"
getPhase = "Label1"
Case "Text2"
getPhase = "Label2"
End Select
End Function
Sub setPhase()
Dim cycle As Range
Dim phase As Range
Set cycle = ActiveSheet.Range("b2:b10")
Set phase = ActiveSheet.Range("A2:A10")
For Each cell In phase.Cells
cell.Value = getPhase(cycle.Cells(cell.Row, 1))
Next cell
End Sub
