Solved

Is there a way to only code to format once and have that apply to many cells?

Posted on 2014-09-25
14
115 Views
Last Modified: 2014-10-07
I have some code that moves data from an access database to an excel spreadsheet. Right now I am moving the data and then going back to each cell based on their content and formatting them. It works, but it is really bulky and I am thinking that there must be a better way to do this.
This is the code that loops through the data in access and puts it into the spreadsheet:

Do Until rs.EOF

    If rs!to = TheTO Then
        If rs!STO = TheSTOname Then
            If TheStaffName = rs!TeamName Then
                If theActDesc = rs!ActDesc Then
                    rs.MoveNext
                Else
                    theRow = theRow + 1
                    oSheet.Cells(theRow, 3).Value = rs!ActDesc
                    rs.MoveNext
                End If
            Else
                'theRow = theRow + 1
                oSheet.Cells(theRow, 2).Value = rs!TeamName
                TheStaffName = rs!TeamName
                
                rs.MoveNext
            End If
        Else
            theRow = theRow + 1
            oSheet.Cells(theRow, 1).Value = rs!STO
            TheSTOname = rs!STO
            TheStaffName = ""
            
            rs.MoveNext
        End If
    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!to
        TheTO = rs!to
        rs.MoveNext
    End If
skip:
Loop

Open in new window




I need all of the cells that were populated with TheTO to be formatted a particular way. Right now I have code that looks for the specific words in the cells and formats those cells. Here is an example:

With oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1)
For Each C In oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1).Cells
   If C.Value = "Thing number 1 it looks for" Then
    With C
        .Font.Name = "Arial"
        .Font.Bold = True
        .Font.Size = 12
        .Interior.ColorIndex = 15
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeTop).ColorIndex = xlAutomatic
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
        .Borders(xlEdgeRight).ColorIndex = xlAutomatic
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideVertical).Weight = xlThin
        .Borders(xlInsideVertical).ColorIndex = xlAutomatic
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
            


     End With
    End If
Next C

Open in new window


The problem is that, if I continue to find all of these cells individually based on their content, I will have to repeat it about 40 times.  

I am willing to do that, but it seems like this is excessive. I would love to just tell it to format every cell that was populated with TheTO.

Is there a way to do this?
0
Comment
Question by:Megin
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40344584
call the format procedure from the initial loop where you populate a cell.  Pass in the cell reference so it is not hard-coded in the format procedure.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40344677
You could use a style. They are underused in Excel, but might work in this case"
0
 

Author Comment

by:Megin
ID: 40344695
Unfortunately, it all has to be done from Access and all has to be coded.

PatHartman: I am not sure if this is what you meant at all.

What have done now is set up a public procedure called "FormatTO"

It is this:
Public Sub FormatTO()
Dim C As Object
 With C
        .Font.Name = "Arial"
        .Font.Bold = True
        .Font.Size = 12
        .Interior.ColorIndex = 15
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeTop).ColorIndex = xlAutomatic
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
        .Borders(xlEdgeRight).ColorIndex = xlAutomatic
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideVertical).Weight = xlThin
        .Borders(xlInsideVertical).ColorIndex = xlAutomatic
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
     End With
     
    End Sub

Open in new window




Then I added the name of that procedure here:

    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!to
        TheTO = rs!to
        FormatTO
        rs.MoveNext
    End If
skip:
Loop

Open in new window


This isn't working at all. In fact, it stops everything so that the data doesn't transfer completely.

Please forgive my being so dense. I am still really new to vba.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40344725
I don't do Excel or I would show you how to do it.  When you call FormatTo, you need to tell it what cell it is supposed to format so you need to add an argument that is a cell reference.

Call FormatTo(somecellreference)

Also,

Public Sub FormatTO(somecellreference)
in the code, format somecellreference
0
 
LVL 33

Expert Comment

by:Norie
ID: 40344746
Have you considered using Excel's conditional formatting?

That can be used to format based on criteria,  and it can be applied through code.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40346197
Your FormatTO procedure needs a Cell or Range argument -- then you can call it, giving it a cell or range to work with.  That may be all you need.
0
 

Author Comment

by:Megin
ID: 40346512
Imnorie: How would that work?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Megin
ID: 40346515
Helen: The thing is that the cell will probably change every time the report is run. I need the cell to be formatted based either on the text in the cell, which would require I code the formatting about 40 different times, or attached to the loop that adds the data to the document (every cell that is populated with TheTO).

At least, those are what I think are my options.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40346532
If you will refer back to my first response, that is exactly what I told you to do.  then in 40344725 I gave you pseudo code.  If the formatting is different for each cell, they you will need 40 different procedures.  If you can tell from the cell contents what the format should be, then you can determine it in the formatting procedure.  If you have a general format such as for dates, numbers, text, then in the calling routine where you know what you are putting into the cell, you can pass an additional argument to the format routine.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40346564
Perhaps you could create a procedure with a Select Case statement to apply the appropriate formatting depending on the contents of the cell.
0
 

Author Comment

by:Megin
ID: 40352892
I am sorry I am not catching on to any of this. I am not sure how to do what you all have suggested.
I would like my code to format those cells populated with TheTO. TheTO is going to be different every time. So I would like the code to format the cell when it puts that information in. I think that what I need is something to go in here:
            theRow = theRow + 1
            oSheet.Cells(theRow, 1).Value = rs!STO
            TheSTOname = rs!STO
            TheStaffName = ""
            
            rs.MoveNext

Open in new window


That is where it will add TheTO data to a cell. If it can just format the cell every time TheTO is added to it, I think that would fix everything. But when I try to add that, it stops the code or errors.

Again, I apologize for not understanding how to the other things.
0
 

Accepted Solution

by:
Megin earned 0 total points
ID: 40357781
I figured this out.

I had to put the code in like this:
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!to
       'FormatTO (oSheet.Cells(theRow,1),"Arial")
        Set C = oSheet.Cells(theRow, 1)
        With C
            .Font.Name = "Arial"
            .Font.Bold = True
            .Font.Size = 12
            .Interior.ColorIndex = 15
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideVertical).Weight = xlThin
            .Borders(xlInsideVertical).ColorIndex = xlAutomatic
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
Range(C, C.Offset(0, 1)).Merge
         End With
        TheTO = rs!to
        rs.MoveNext
        
    End If


skip:
Loop

Open in new window


Thank you for all of the suggestions. I am sorry I had so much trouble understanding.
0
 
LVL 33

Expert Comment

by:Norie
ID: 40358478
That code looks fine to me, and I don't think you have any reason to apologise to anyone.
0
 

Author Closing Comment

by:Megin
ID: 40365543
I am choosing my answer because I got this solved outside of EE.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now