trouble with basic code???????

[sd]

I am running a macro to dump timescaledata into excel. I want to bold each
subtotal row. I cannot for the life of me figure out what I've done wrong.


==================================================
everything is declared . . . .

Set xlRng = xlRng.Columns("a")
xlRng.Select

For Each cell In xlRng
If Right(cell.Value, 5) = "Total" Then
EntireRow.Select.Font.Bold = True
End If
Next cell

 

[Jan De Messemaeker]

Hi,

You are in a Microsoft Project Newsgroup, but I'll try to help you anyway -
I'm not an Excel specialist but I've done at least some VBA in it.

First, shouldn't that read:

For each cell in xlrng.cells

(IMHO For each runs through a collection, and xlrng is not a collection it
is a single object

Then, how does VBA know what entire row is? Shouldn't that read

cell.entirerow.font.bold=true

Didn't test it, but this comes to mind

 

[Buster]

I think the problem was that the export was comming from Project and being
programmatically sent to excel

JEff

 

[John]

sd,
First of all I would avoid using "cell" as an object. As I recall I
think it is a keyword in Excel. Why not simply use "c". Second you do
not need the "xlRng.Select" statement. It does nothing for you. Third,
within the loop, selecting the entire row is not linked to the current
cell object. In other words, the loop is running background code (i.e.
no direct selectiion), but any "select" statement needs to process in
the foreground. The two need to be linked. Without actually digging into
the Excel object library I don't know exactly which statement to use but
you need something similar to the following which relates to Project.
Assume the loop is running through all tasks in a Project. Once the "if"
statement becomes true, find out which task ID the loop is currently
operating on. Then select that ID and set the font format. As your code
is currently written I suspect the first row on the Worksheet is
selected and bolded each time instead of the row with "totals".

Hope this helps.
John

 

[Buster]

So I can I use c as the ID in the select statement such as
c.select.entirerow.

 

[Buster]

John thanks for the work, while you were doing your research, I was doing
mine and came up with the following.

For Each cell In xlRng.Range("A4:A400")
If Right(cell.Value, 5) = "Total" Then
cell.EntireRow.Font.Bold = True
End If
Next cell

Your code is much cleaner than mine, but I can say that I have learned
something from your code.

Jeff

 

[John]

Buster,
You're forcing me to do your homework (i.e. check the Excel object
library) but then that's what MVPs sometimes have to do. Actually as it
turns out Excel can reference font control a little easier than Project.
In Project the field cell must be selected to apply a font property
(i.e. foreground processing) but in Excel font properties can be applied
directly in background processing. What does all this mean. Simply that
you do not need any "select" statements. The following code should do
what you want (assuming the data you want to play with is on Worksheet
one of the active workbook.

Set xlRng = ActiveWorkbook.Sheets(1).Columns("A:A")
For Each c In xlRng
If Right(c.Value, 5) = "Total" Then c.Font.Bold = True
Next c

The code can't get much simpler than that. Just for reference I have
written many macros that export timescaled data from Project to Excel.
What I usually do is run a subroutine of code to pre-format the Excel
Worksheet before I dump the Project data. During formatting I can set up
cell alignment, font characteristics, column headings, time labels (e.g.
month) and other housekeeping items. Then when the code starts the data
dump I don't have to worry about what cells to format. Of course some
format cleanup is sometimes required (e.g. I need to add text for
"Totals" in a cell that is dependent on the number of items of data
exported).

Hope this helps.
John
Project MVP

 

[John]

Buster,
Great! Glad I could help. Sometimes learning can be a fun process,
especially when you can use what you learned to do something new.

I use background processing in all my macros (except when a particular
method requires selection). The code runs faster, although that isn't
really noticeable with today's PC speeds, and it also eliminates the
screen "flashing" while the code is selecting various objects. Sometimes
it's fun to watch what is going on but then it can also be annoying.

John