Solved

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

Posted on 2011-02-28
8
152 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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

831 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