Megin
asked on
Is there a way to only code to format once and have that apply to many cells?
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:
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:
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?
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
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
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?
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.
You could use a style. They are underused in Excel, but might work in this case"
ASKER
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:
Then I added the name of that procedure here:
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.
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
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
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.
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
Call FormatTo(somecellreference
Also,
Public Sub FormatTO(somecellreference
in the code, format somecellreference
Have you considered using Excel's conditional formatting?
That can be used to format based on criteria, and it can be applied through code.
That can be used to format based on criteria, and it can be applied through code.
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.
ASKER
Imnorie: How would that work?
ASKER
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.
At least, those are what I think are my options.
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.
Perhaps you could create a procedure with a Select Case statement to apply the appropriate formatting depending on the contents of the cell.
ASKER
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:
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.
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That code looks fine to me, and I don't think you have any reason to apologise to anyone.
ASKER
I am choosing my answer because I got this solved outside of EE.