CODEWITHSUNDEEP
excelvba
j-r23
j-r23

Reputation: 59

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

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.

Upvotes: 0

Views: 104

Answers (3)

j-r23
j-r23

Reputation: 59

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

Upvotes: 0

Legxis
Legxis

Reputation: 916

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

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 0

Related Questions