Solved

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

Posted on 2011-02-28
8
154 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

756 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