One-line bar chart for %

[Kilcup]

Hello all,

I have a list of percentages that I hope can be represented by a singl
horizontal bar chart. Each bar would represent the percentage in th
cell to its left.

No comparisons or multibar are needed, just a one line bar that fit
the next cell horizontally, like so:

56% Chart
78% Chart
61% Chart
and so on...

The chart will be the same size for all entries.
If anyone can help or has any pointers, some direction would be greatl
appreciated.

Jef

 

[Earl Kiosterud]

Jeff,

You want something to the right of these cells that graphically shows
(chart-style, horizontally) the value in each cell? I don't know if I've
interpreted your question correctly. Try this:

=REPT("-",A2*100)

Copy down.

 

[Kilcup]

Thanks for the help with this, it gets the idea across, which is what
need. I still seem to have some issues formulating the same thin
within vba, though. This is what I have thus far:


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

Worksheets("_DeputationQuery").Range("F1").Activate
For i = 1 To rng.Row
ActiveCell.Formula = "=REPT(""|"",E" & i & "*100)"
ActiveCell.Offset(1, 0).Select
Next i


The error I receive is from the "ActiveCell.Formula" line. If you ca
help me finish this off, a director at my office will definitel
appreciate it!

Jef

 

[Dave Peterson]

Your code worked ok for me, but another way to do this is to populate the whole
range in one fell swoop.

Just like selecting a range, typing the formula (for the activecell) and hitting
ctrl-enter to fill the other cells in the selection.

Dim lastRow As Long
With Worksheets("_DeputationQuery")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("f1:f" & lastRow).Formula = "=REPT(""|"",E1*100)"
End With

 

[Kilcup]

Thank you all,

It looks like it should work fine!

Jef

 

[Dave Peterson]

Try this manually against a cell with a percentage in it (test worksheet):

=REPT(REPT(CHAR(134),4)&" ",INT(A1*100/5))&REPT("|",MOD(A1*100,5))

If you like that effect:

In your code:

..Range("f1:f" & lastRow).Formula _
= "=REPT(REPT(CHAR(134),4)&"" "",INT(E1*100/5))&REPT(""|"",MOD(E1*100,5))"