How to change all the cells value in the selected column?

Question

I'm trying to write some code that will change all the cells in the selected column under some conditions.

My code can change the selected cell but crushes when I try to change all the selected column.

Dim ActSheet As Worksheet  
Dim MyRange As Range

Set ActSheet = ActiveSheet  
Set MyRange = Selection

If MyRange.Cells.Value = "Clothes" Then    
    MyRange.Cells.Value = 2
ElseIf MyRange.Cells.Value = "Extra" Then
    MyRange.Value = 3
ElseIf MyRange.Cells.Value = "Shoes" Then
    MyRange.Value = 1
End If

It works fine when I'm trying to change all the column color for example, but not on the value.

You need a loop through the cells of that (selected) column and process each cell on it's own. Give it a try there are many tutorials how to loop through the data of a column. — Pᴇʜ, Jul 22 '19 at 07:31

Legxis · Answer 1 · 2019-07-22T07:55:26.050

You need a loop for that, try this?

Dim ActSheet As Worksheet  
Dim MyRange As Range
Dim TargetCell As Range 

Set ActSheet = ActiveSheet  
Set MyRange = Selection

For Each TargetCell In MyRange.Cells

    If TargetCell.Value = "Clothes" Then    
        TargetCell.Value = 2
    ElseIf TargetCell.Value = "Extra" Then
        TargetCell.Value = 3
    ElseIf TargetCell.Value = "Shoes" Then
        TargetCell.Value = 1
    End If

Next TargetCell

score 0 · Answer 2 · answered Jul 22 '19 at 07:55

Loop through selected cells and use a Select Case statement (alternatively to your If … ElseIf statement).

Dim MyRange As Range
Set MyRange = ActiveSheet.Selection

Dim TargetCell As Range 
For Each TargetCell In MyRange.Cells
    Select Case TargetCell.Value 
        Case "Clothes"  
            TargetCell.Value = 2
        Case "Extra"
            TargetCell.Value = 3
        Case "Shoes" 
            TargetCell.Value = 1
    End Select
Next TargetCell

score 0 · Answer 3 · answered Jul 22 '19 at 07:58

0

Thanks for the help,works great.i tried this code: Dim found As Boolean

  found = False

  Do Until IsEmpty(ActiveCell)

     If ActiveCell.Value = "Clothes" Then
      ActiveCell.Value = 2
      ElseIf ActiveCell.Value = "Shoes" Then
       ActiveCell.Value = 1
      ElseIf ActiveCell.Value = "Extra" Then
       ActiveCell.Value = 3
        found = True

     End If

     ActiveCell.Offset(1, 0).Select
  Loop

answered Jul 22 '19 at 07:58

j-r23

39
1
1
2

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Using select is the worst of all these solutions. Please have a look at the other answers. Your code is much faster if you don't use `.Select`! – Pᴇʜ Jul 22 '19 at 08:12

How to change all the cells value in the selected column?

3 Answers3