Solved: Excel: Macro to divide specific cells by 100 with a filter

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rotvik

Thread Starter
Joined
Jan 11, 2013
Messages
30
Hi

I have a following macro:

Code:
Sub BondPrices()

    Selection.AutoFilter Field:=16, Criteria1:="BONDS"
    
    Range("S2:S10000").Select
    For Each cell In Selection
    If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
    cell.Value = CDbl(cell.Value)
    cell.Value = cell.Value / 100
    End If
    Next cell
    
    Selection.NumberFormat = "0.00%"
    Selection.AutoFilter
End Sub
The problem is that every cell in the sheet gets divided by 100, not only the filtered cells (only BONDS).

Any help would be greatly appreciated.

Michael
 

rotvik

Thread Starter
Joined
Jan 11, 2013
Messages
30
I've solved the problem myself by adding a line to the code that selects only visible cells:
Code:
Selection.SpecialCells(xlCellTypeVisible).Select
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top