Applying function to all Cells in a Column

Question

My code works only for the selected cell. I want it to work for every cell in Column A.

Here is my code so far:

Sub Splitter()

Dim i As Integer
Dim Text As String
Dim Item As Variant

Text = ActiveCell.Select
Item = Split(ActiveCell, "-")

For i = 0 To UBound(Item)
    Cells(3, i + 2).Value = Item(i)
Next i

If Range("B3").Value = "CHOP" Then
    Range("D3").Value = "chopsticks"
ElseIf Range("B3").Value = "NAPK" Then
    Range("D3").Value = "napkins"
ElseIf Range("B3").Value = "RICE" Then
    Range("D3").Value = "white rice"
ElseIf Range("B3").Value = "SOYS" Then
    Range("D3").Value = "soy sauce"
ElseIf IsEmpty(Range("D3").Value) = True Then
    Range("D3").Value = "other"
End If

End Sub

I am also trying to get it to continue to work if rows are added.

What sort of data do you have in column A? Is it just something like "ABCD-xxx", or do you sometimes have "ABCD-xxx-xxx-xxx"? (If there is more than one hyphen, it will affect what you want put into column D, i.e. "other" or what was in the 3rd "part" of the original cell in column A.) — YowE3K, Dec 10 '16 at 17:37
your code is screaming for `Select Case Range("B3").Value` , then on the following lines `Case "CHOP"` , etc.. — Shai Rado, Dec 10 '16 at 17:43
My Data just has ABCD-xxxx is all. So I have figured out how to do the right function, but am only able to apply it to the selected cell and I am not sure how to apply it do the entire row — Travis William Carter, Dec 10 '16 at 17:49
Create a loop from 3 to "LastRow" (See this: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) — Porcupine911, Dec 10 '16 at 17:51

Shai Rado · Accepted Answer · 2016-12-10T18:44:35.330

The code below will loop through all rows in Column A, starting from row 3 until last row with data.

I am using Select Case .Range("B" & LRow).Value instead of multiple ElseIf.

I am assuming your last ElseIf IsEmpty(Range("D3").Value) = True Then was a type error, and you meant ElseIf IsEmpty(Range("B3").Value) = True Then.

Code

Option Explicit

Sub Splitter()

Dim i As Integer
Dim Text As String
Dim Item As Variant
Dim LastRow As Long
Dim LRow As Long

' modify "sheet1" to your sheet's name
With Sheets("Sheet1")

    ' find last row in Column A
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    For LRow = 3 To LastRow

        Item = Split(.Range("A" & LRow), "-")            
        For i = 0 To UBound(Item)
            .Cells(LRow, i + 2).Value = Item(i)
        Next i

        Select Case .Range("B" & LRow).Value
            Case "CHOP"
                .Range("D" & LRow).Value = "chopsticks"

            Case "NAPK"
                .Range("D" & LRow).Value = "napkins"

            Case "RICE"
                .Range("D" & LRow).Value = "white rice"

            Case "SOYS"
                .Range("D" & LRow).Value = "soy sauce"

            Case "CHOP"
                .Range("D" & LRow).Value = "chopsticks"

            Case Else
                .Range("D3").Value = "other"

        End Select

    Next LRow        
End With

End Sub

This is pretty much perfect! The only part that is not working is in Column D, if Column B is anything but CHOP,NAPK,SOYS,RICE then it needs to be "other". I originally put isempty because it would be empty if it was not any of those things. — Travis William Carter, Dec 10 '16 at 18:39
@TravisWilliamCarter try edited code and let me know if that's what u meant — Shai Rado, Dec 10 '16 at 18:45

Applying function to all Cells in a Column

1 Answers1