CODEWITHSUNDEEP
vbaexcel
Jared
Jared

Reputation: 2954

VBA Select Case Loop in Text

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"

As of now I keep getting a run time error number 424, stating object required

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

Upvotes: 1

Views: 18017

Answers (3)

whytheq
whytheq

Reputation: 35557

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:

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

Upvotes: 6

Siddharth Rout
Siddharth Rout

Reputation: 149287

You have already got your answers :) Let me do some explaining in my post though :)

You cannot use this.

phase.Text = getPhase(cycle)

.Text is a Readonly property. i.e you cannot write to it but only read from it. You have to use .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 .Offset property. See this

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.Text since you are only reading from it. However, it is always good to use .Value. Reason being the .Value property returns the actual value of the cell where as .Text property returns the text which is displayed on the screen. The limit of Text is approx 8k characters in higher versions of Excel. The .Value on the other hand can store up to 32k characters.

Upvotes: 7

Julian Knight
Julian Knight

Reputation: 4923

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

Upvotes: 5

Related Questions