Bolding variable cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using this code to add the words "Total" and "Items" in column A at the
end of a report I download each day with variable number of rows each day.
In column B I have the dollar amount on the same row as Total and the count
of rows next to Items. I need to add code to the below to convert these four
cells (which I can not identifiy the location of since the report varies each
day) to bold and the count number in column B needs to be text, it is
currently coming in as currency since that is how the column is formatted.
Your help is appreciated, thank you

Set rng = Cells(Rows.Count, 2).End(xlUp)

rng.Offset(2, -1).Value = "Total"
rng.Offset(3, -1).Value = "Items"

rng.Offset(2, 0).Value = Application.Sum(Range("B1", rng))
rng.Offset(3, 0).Value = Application.Count(Range("B1", rng))

Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
 
Hi there,

If I understand correctly you've done hte hard part already and already
identified your cells when you enter your headigns and values. All you
need do further is change the format of the cell.

Set rng = Cells(Rows.Count, 2).End(xlUp)

rng.Offset(2, -1).Value = "Total"
rng.Offset(2, -1).Font.Bold = True

rng.Offset(3, -1).Value = "Items"
rng.Offset(3, -1).Font.Bold = True

rng.Offset(2, 0).Value = Application.Sum(Range("B1", rng))
rng.Offset(2, 0).Font.Bold = True

rng.Offset(3, 0).Value = Application.Count(Range("B1", rng))
rng.Offset(3, 0).Font.Bold = True

Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit


I think that does it.

Good luck, Tris
 
Oops

Sorry forgot about the currency thing....


rng.Offset(3, 0).Value = Application.Count(Range("B1", rng))
rng.Offset(3, 0).Font.Bold = True
rng.Offset(3, 0).NumberFormat = "General"

If general doesn't do the job try swapping it for "Text"
 
This worked great, thanks a bunch. I just realized I have one other
spreadsheet that I just need the sum in column A for the entire column. I've
tried manipulating this code, but it's not working for me. Again it is an
unknown cell because the report varies in length each day.
 
I need to show on an invoice, the total amount of the transaction in dollars,
but also must have this amount written our in words. Example:

125.00 ONE HUNDRED TWENTY FIVE AND ZERO CENTS

thanks,

Peter Hoffmann
 
Back
Top