Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel loop(?) coding to find and update all entries

Posted on 2011-02-28
8
Medium Priority
?
159 Views
Last Modified: 2012-08-13
How do I approach the following problem.  I used the record macro function to create the following code to bold and color the Months in a spreadsheet.  How can I modify it to "loop"(?) through the spreadsheet to find and update all the entries?

Thanks

Cells.Find(What:="January", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Replace What:="January", Replacement:="January", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
    Cells.FindNext(After:=ActiveCell).Activate
   
    Cells.Find(What:="February", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Replace What:="February", Replacement:="February", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
    Cells.FindNext(After:=ActiveCell).Activate


etc..........................
0
Comment
Question by:BulldogMike
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:sdwalker
ID: 35000949
It would help a LOT if you attached the spreadsheet, if possible.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35001020
That code should do it, assuming you like red.

THomas
Sub BoldMonths()
Dim i As Long, rg As Range, j As Long, strMonth As String, lgCount As Long
Application.ScreenUpdating = False

Set rg = [a1]

For i = 1 To 12

    strMonth = Format(DateValue(i & "/1/2010"), "mmmm")
    j = WorksheetFunction.CountIf(ActiveSheet.UsedRange, "*" & strMonth & "*")

    
    For lgCount = 1 To j
            
        Set rg = Cells.Find(What:=strMonth, After:=rg, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
                
        rg.Font.Bold = True
        rg.Interior.ColorIndex = 3
        
    Next lgCount
    
    Set rg = [a1]
    Err.Clear
Next i

Application.ScreenUpdating = True
   

End Sub

Open in new window

0
 

Author Comment

by:BulldogMike
ID: 35001425
attached is what I would like -  I don't like the red cels

Test-Months.xls
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:BulldogMike
ID: 35001563
Thomas

It would be helpful to me if you could comment your code so I can figure out what its doing.  I'm not a professionally trained programmer so sometimes need a little more help  to understand.  

Thank You

Mike  
0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 35001639
Mike,

Here is an update with comments. And BTW, I'm a trained professional, but definitely not a professionally trained programmer.

Thomas

Sub BoldMonths()
Dim i As Long, rg As Range, j As Long, strMonth As String, lgCount As Long

'disable screenupdate to speed up macro and avoid screen flickering
Application.ScreenUpdating = False

Set rg = [a1]

For i = 1 To 12 'loop for 12 possible months

    'generate a string with the month name in mmmm format
    strMonth = Format(DateValue(i & "/1/2010"), "mmmm")
    
    'count the number of cells with that month in them
    j = WorksheetFunction.CountIf(ActiveSheet.UsedRange, "*" & strMonth & "*")

    'loop for all instances found
    For lgCount = 1 To j
            
        'find range matching strMonth
        Set rg = Cells.Find(What:=strMonth, After:=rg, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        
        'bold and color font on that range
        rg.Font.Bold = True
        rg.Font.Color = 16711680
    
    'next instance
    Next lgCount
    
    'reset range to re-start find at first cell
    Set rg = [a1]
    Err.Clear
Next i

'reenable screenupdate
Application.ScreenUpdating = True
   

End Sub

Open in new window

0
 

Author Comment

by:BulldogMike
ID: 35007228
Thomas

Thanks for you help and advice - I'll be watching you.  I also am a trained professional, a pharmacist.  Your knowledge level is impressive.

Thanks Again

Mike
0
 

Author Closing Comment

by:BulldogMike
ID: 35007239
Very knowledgeable and helpful
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35009779
Glad to help Mike. Thanks for the grade,

Thomas
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question