• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell graphic representation

S

Sinu

New Member
I have 2 cells
Budget: X
Allocated: Y

I want to show in cell 3 how much % of budget is allocated.
i can get the percent value by doing (Y/X)%. i can get the number value here.
i want to show something similar to progress bar wherein i can fill certain characters inside a cell to show the allocation.

Solution so far:
REPT("",MAX(0,H11*(CELL("width",INDIRECT("C",0))-5)))
Click to expand...
The problem is that i get library error on some excel versions.

Problems: i can fill a cell with certain characters like
|
Click to expand...
but cant use it if user changes the cell width.
 
Sinu
Try something like:
=REPT("n",(B8/B7)*CELL("width",INDIRECT("C",0))*0.64)

Where "n" is a character
B8 is Allocated
B7 is Budget
0.64 is a factor

Copy and paste the formula as required
Apply the Wingding Font to the cell
Set Allocated to the same value as Budget
Adjust the 0.64 factor so that the cell is filled
You will need to press F9 every time you adjust a cell width

upload_2014-8-12_14-49-3.png

then
upload_2014-8-12_14-49-28.png

Adjust the Indirect("c" to suit the column your using
 
Hui said:
Sinu
Try something like:
=REPT("n",(B8/B7)*CELL("width",INDIRECT("C",0))*0.64)

Where "n" is a character
B8 is Allocated
B7 is Budget
0.64 is a factor

Copy and paste the formula as required
Apply the Wingding Font to the cell
Set Allocated to the same value as Budget
Adjust the 0.64 factor so that the cell is filled
You will need to press F9 every time you adjust a cell width

View attachment 9348

then
View attachment 9349

Adjust the Indirect("c" to suit the column your using
Click to expand...
Thank you for your reply.

I was using a similar formula. But it throws cannot find library for rept function.
 
Sinu

Your original question never mentioned that it was a VBA statement you were using

Can you please post the entire line or subroutine that is involved
 
on the worksheet load, i'm pasting this formula in a particular cell.
REPT("",MAX(0,H11*(CELL("width",INDIRECT("C",0))-5)))
 
Your screen shot above shows Msgbox(... in green
and the next line is obsured by the Error Box
and this formula isn't the one I suggested above

Please post the file or the whole line that is in error
Drip feeding us information makes it so much slower to assist you
 
the error is on the line below the commented line.
i have tried replicating that error but in vain. Hence i'm trying to find a way where i can change the code that i'm using
 
Which we can't read !!!!

Please copy the whole line here so we can see it.
 
Hui said:
Which we can't read !!!!
Click to expand...
its the same code
Code: 
Range("F11:G11").Formula = "=IF(ISERROR(REPT(""" & Chr(8) & """,MAX(0,H11*(CELL(""width"",INDIRECT(""C"",0))-5)))),0,(REPT(""" & Chr(8) & """,MAX(0,H11*(CELL(""width"",INDIRECT(""C"",0))-5)))))"

This code works perfectly for all the machines i've tested. But few clients have come back saying they got the compiler error
 
So they will either be using different versions of Excel and

Also check that they have the same References installed in VBA
upload_2014-8-12_19-11-33.png
 
Thanks a lot for your time and effort @Hui . They're using the same excel version that i have. i'll surely check for the references.
 
I won't show you mine as I am using a different version of excel and so it will look different to yours
 
Hui said:
I won't show you mine as I am using a different version of excel and so it will look different to yours
Click to expand...
That's ok. But is there any other solution for this? any other idea you can think of. Because it might not be feasible for me to check in all the machines.
 
I think that you need to have the Visual basic for Applications and
Microsoft xx Object library enabled in the References, but what ever you do replicate the references that you have on the working PC.

Start with one machine first and see if that is the problem, before tackling them all
This can happen when different components are installed with excel at Installation time or subsequently
 
You must log in or register to reply here.
Back
Top