Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-28
8
Medium Priority
?
157 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

721 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