Bar Chart with Filled colors in corresponding cells

[ashokkumarkolla]

Dear All,

The Input data contains 5 Columns.

1. Sl No - Number from 1 to 1000 (max)
2. From - Number from 0 to 1,00,000
3. To - Number from 0 to 1,00,000
4. Side - Only "LHS" or "RHS"
5. Code -
12 types of different codes specified in Column "J" and corresponding color in column "K"

The format of Bar chart has been shown in tab "BAR CHART".


For Example:

In "INPUT" sheet, Sl No. 1

from "4" To "98" on LHS side the code is "EMBTOP".

The cells 10, 20,..., 90 on "LHS" side (i.e. row 10 in tab :BARCHART") to be filled with corresponding color of "EMBTOP".


The same has been highlighted for your reference.

Need a macro and Thanks in advance.

Ashok.

 

[Hui]

Do you mean like this:

see attached file:

 

[ashokkumarkolla]

Sir,
Thanks for your reply.

The format for output bar chart is in tab "BARCHART" which was given to update by my higher authority which i am unable to change (format).

At present, I am filling the cells with corresponding color for the particular code manually.

Please check the tab "BARCHART" for reference.

Need a macro.

Thanks,
Ashok.

 

[Hui]

Why isn't the 0-10 Cell colored when it is has a minimum value of 4?

See attached:


Press F9 for a good time

 

[ashokkumarkolla]

Sir,

0 is not between 4 to 48.

Please find enclosed the Modified File for your reference.

In Sl No.1 data is below.
From=4, To=48, Side=LHS, Code=EMBTOP
The following cells in sheet "Barchart" is to be colored with corresponding color.
4 to 48 means, the values in chainage row no 13 (i.e. 10,20,30,40).


After coloring Sl No.1,

Remaining data (Sl no.2 to End) to be colored like wise.

Thanks for your help.
Ashok

 

[Hui]

Two queries

1.

Using your example 4 to 48 in Row 10

Cell C10 corresponds to 0 to 9
Cell D10 corresponds to 10 to 19
Cell E10 corresponds to 20 to 29
Cell F10 corresponds to 30 to 39
Cell G10 corresponds to 40 to 49
Cell H10 corresponds to 50 to 59

4 is between 0 and 9 But is not highlighted in cell C10
But 48 is between 40 and 49 but is highlighted in G10

2.

In your input worksheet you have the Four LHS Embtop lines all together,
I assume that in real life they aren't but are spread amongst the data?
Please clarify

 

[ashokkumarkolla]

Sir,

Thanks for your precious help.

1. Cell C10 corresponds to value 0 only, like all.
2. I am working in a road project, The bar chart view will show the layer (Codes) wise status of road.
In execution, the work will be done in bits and pieces where there is no hindrances.

I am explaining manual procedure i am doing.

For Example: Sl. No. 1

Data is From= 9, To= 48, side= LHS, Code= Embtop.

Steps:

1. LHS & Emb Top is ROW no. 10 in sheet "Barchart"
2. Check From value 9 in chainage (Row no. 13 in sheet "Barchart").
3. As value 9 is not available, I will skip to next value i.e. 10.
4. Now value "10" is in chainage (D13 in sheet "Barchart").
5. The Intersecting cell D10 is filled with corresponding color of code "Embtop".
6. Next value in chainage is "20"
7. Check the value "20" is less than To value i.e. "48"
8. As 20<48, steps 4 & 5 will be repeated.
9. Steps 6, 7 & 8 will be repeated up to value "40".

10. Now Next value in chainage is "50"
11. As value "50" is larger than To value "48", The process will be stopped.

After Sl No.1 is completed, Sl No.2 to "end" is to be done in same manner.

Thanks
Ashok.

 

[ashokkumarkolla]

Sir,

I need a macro to automate the process.

Thanks in advance
Ashok

 

[ashokkumarkolla]

Dear all,

I wrote vba code for the desired output by searching internet (spent 3 days).

While running the code, run time error "5" is showing.

Please check and correct.

Thanks in advance.
Ashok

 

[Chihiro]

There are multiple issues...

1. Line below has variables nested in quotes and being treated as string

ThisWorkbook.Worksheets("bar chart").Range(Cells("CodeRow,ChCol1"), Cells("CodeRow,Chcol2")).Value = 1

2. Below line inside the loop forces active sheet to "bar chart" and will cause issue in second iteration (since 4 variables; ChFrom, ChTo, Side & Code should be taken from "input"). Either nest 4 variables inside WITH statement and add period before "Cells". Or modify your code structure.

    ChFrom = Cells(i, 2)
    ChTo = Cells(i, 3)
    Side = Cells(i, 4)
    Code = Cells(i, 5)

Sheets("BAR CHART").Activate

3. CodeRow is declared as Range. But you are trying to use it as Row Number

 

[ashokkumarkolla]

Hi chihiro,

Thanks for your help.

I had tried to modify the code as per your advice.

1. changed code row as integer and quotes are removed.
2. I am trying to understand "With" function. Meanwhile i had added the first line thinking this will not cause issue in subsequent iterations.

Sheets("input").Activate

    Dim ChFrom As Long
    Dim ChTo As Long
    Dim Side As String
    Dim Code As String

3. Coderow is now declared as integer, while debugging

I found two mistakes

CodeRow = Sheets("bar chart").Range("b1:b12").Find(what:=Code, LookIn:=xlValues, lookat:=xlWhole)

ThisWorkbook.Worksheets("bar chart").Range(Cells(CodeRow, ChCol1), Cells(CodeRow, Chcol2)).Value = 1

This is the first code i am trying to write.

Thanks for your help
Ashok.

 

[Chihiro]

On point 3. CodeRow should remain as Range. As "Range.Find" will return a cell.

What you'd need to do is get ".Row" property of the CodeRow.

So... "CodeRow.Row" will give you row# of the cell found.

 

[Chihiro]

As for "WITH". It's used like below.

With Sheets("input")
  ChFrom = .Cells(i, 2)
    ChTo = .Cells(i, 3)
    Side = .Cells(i, 4)
    Code = .Cells(i, 5)
End With

Sheets("BAR CHART").Activate

 

[ashokkumarkolla]

Hi chihiro,

Sorry. I am bit confused.

I had modified CodeRow as CodeRow.Row

CodeRow.Row = Sheets("bar chart").Range("b1:b12").Find(what:=Code, LookIn:=xlValues, lookat:=xlWhole)

but same problem is coming.

I had changed the "with" as per your post#13

Attaching the file for your ref.

Thanks for your precious time & help.
Ashok

 

[Chihiro]

No, no. You don't change that line. You should use "Set CodeRow =" as is. Since you are getting cell containing the ".Find" string.

But when you use it in following line, use ".Row".

ThisWorkbook.Worksheets("bar chart").Range(Cells(CodeRow.Row,ChCol1), Cells(CodeRow.Row,Chcol2)).Value = 1

 

[ashokkumarkolla]

Hi chihiro,

Found some mistakes in the code and modified bit.

1. used match function for finding row number & column number.

Mistake found in code line below.

Set ThisWorkbook.Worksheets("bar chart").Range(Cells(CodeRow, Chcol1), Cells(CodeRow, chcol2)).Value = 1

Uploaded the modified file for your inspection.

Thanks
Ashok

 

[ashokkumarkolla]

Hi,

Modified the code of last line with "with" function, now the code works fine for side "LHS" only.

when i change the code to "RHS" in "input" sheet the values has to be in Row no. 16. But they are showing in row no.3

I am unable to find the mistake. Pl help.

Thanks
Ashok