Solved Set column focus in VBA

October 3, 2012 at 07:25:47
Specs: Windows XP
I am using VBA in Excel.
How do I get my code to run only for Column E. Right now it is running for the entire worksheet. How do I set the focus for Column E.

See More: Set column focus in VBA

Report •

✔ Best Answer
October 3, 2012 at 11:49:50
This should work.

Sub ChgStatus()

    Sheets(1).Columns("E").Replace What:="Pending", Replacement:="Done", _
        LookAt:=xlPart, MatchCase:=False
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 3, 2012 at 08:03:18
Since there are so many different ways to have VBA work on a specific range, it would help if we knew what the code was doing. It might be as simple as using the With method or a For...Next loop or it might require something more complex than that.

Please click on the following line, read the instructions found via that link and then post your code so we can see what's going on.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 3, 2012 at 09:52:41
My code is below, it works for the entire worksheet and I would like it only work for a specific Column.


Sub ChgStatus()

Dim WS As Worksheet
For Each WS In Worksheets
WS.Cells.Replace What:="Pending", Replacement:="Done", _
LookAt:=xlPart, MatchCase:=False
Next
End Sub


Report •

#3
October 3, 2012 at 10:41:00
Two points to mention:

1 - It does not appear that you honored my request to follow the instructions found via the link at the end of my post. Had you done that, your code would have looked something like this:

Sub ChgStatus()

Dim WS As Worksheet
  For Each WS In Worksheets
      WS.Cells.Replace What:="Pending", Replacement:="Done", _
        LookAt:=xlPart, MatchCase:=False
  Next
End Sub

Since proper indentation makes it easier for us to read and follow the code, please try to post it in that manner in the future. I realize that it doesn't make much difference with the short macro that you posted, but it's a good habit to get into should you ever need to post longer, more complex code.

2 - You said: My code is below, it works for the entire worksheet

In reality, it works for the entire workbook since it loops through every sheet.

 For Each WS In Worksheets

I'm not sure if you are asking for it to work on Column E for one sheet or on Column E for all sheets.

In any case, the reason it works on the entire sheet(s) is because you are using the Cells property:

WS.Cells

The Cells property tells VBA to look at every cell in the sheet. If you want to limit it to a specific range, simply replace the Cells property with a Range (or Column) property.


This will work for the entire workbook, but only on Column E of each sheet.

Sub ChgStatus()

Dim WS As Worksheet
  For Each WS In Worksheets
      WS.Columns("E").Replace What:="Pending", Replacement:="Done", _
        LookAt:=xlPart, MatchCase:=False
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 3, 2012 at 11:07:35
I obviously messed on your posting instructions somewhere, but I will make sure it is correct the next time.

I am looking for it to work only on column E of Worksheet 1.

Thanks for clearing that up. I understand what I was doing wrong.

Thanks for your help


Report •

#5
October 3, 2012 at 11:49:50
✔ Best Answer
This should work.

Sub ChgStatus()

    Sheets(1).Columns("E").Replace What:="Pending", Replacement:="Done", _
        LookAt:=xlPart, MatchCase:=False
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 3, 2012 at 13:41:16
Thanks a bunch
That work beautifully.

Report •

Ask Question