• 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.

Graph in Excel

M

m_jeyprakash@yahoo.com

New Member
I am new to this VBA project. I have an issue with my current project. any help by today EOD is appreciated.


The Issue.


I have an excel file which has sheets as "Report", "OverallProgram"...

In the "Report" sheet I have a chart(graph) which should show the graph of data from the sheet "OverallProgram" I have 45 rows of column in the "OverallProgram" sheet, but the graph in "Report" sheet shows only upto the 25 rows.


The graph shows only the data from the range B2:F24 but it should show the data from the range B2:F48


The code is

Public Sub OverallProgram_TestScript()

On Error Resume Next


x_TDconnect

Set Sh = ThisWorkbook.Sheets("OverallProgram")

'Set sh1 = ThisWorkbook.Sheets("Credentials")


ReleaseNames = "'" & Selected_App & "'"


Query = "select planned_date, re_forcasting, sum(executed) over (partition by rel_id order by planned_date ROWS BETWEEN UNBOUNDED PRECEDING AND current row) executed,Passed from (select rel_id, to_char(tc_plan_scheduling_date,'MM/DD/YYYY') planned_date, sum(case when tc_exec_date > tc_plan_scheduling_date then 1 else 0 end) Re_forcasting, sum(case when tc_status in ('Passed', 'Failed') then 1 else null end ) Executed, sum(case when tc_status in ('Passed') then 1 else 0 end) Passed from TESTCYCL tcl, releases rls, RELEASE_CYCLES rcl where tcl.TC_ASSIGN_RCYC= rcl.RCYC_ID and rcl.rcyc_parent_id=rls.rel_id and tc_plan_scheduling_date is not null and rls.rel_name = " & ReleaseNames & " group by to_char(tc_plan_scheduling_date,'MM/DD/YYYY') , rel_id ) order by planned_date"


Set comExecute = TDConnection.Command

comExecute.CommandText = Query

Set RecSet = comExecute.Execute

COUNT1 = RecSet.RecordCount


PrevCount = Sh.Cells(1, 1)

Sh.Cells(1, 1) = COUNT1


Sh.Range("B3:F" & PrevCount).Select

Selection.ClearContents


l = 3

m = 2

Reforecast = 0

Passed = 0


If COUNT1 > 0 Then

Sh.Range("B3:F" & l + COUNT1 - 1).Select

Selection.ClearContents

Sh.Range("B3:F" & l + COUNT1 - 1).Value = "0"


For k = 1 To COUNT1

For j = 0 To 10

If j = 0 Then

Sh.Cells(l, m) = RecSet(j)

Sh.Cells(l, m + 1) = ""

Else

Sh.Cells(l, m + 1) = RecSet(j)

If (m = 3) Then

Reforecast = Reforecast + RecSet(j)

Sh.Cells(l, m + 1) = Reforecast

ElseIf (m = 5) Then

Passed = Passed + RecSet(j)

Sh.Cells(l, m + 1) = Passed

End If

End If

m = m + 1

Next

l = l + 1

RecSet.Next

m = 2

Next

End If


'MsgBox "OverallProgramTestScript Report Generated Successfully !!! Please refer to Reports Sheet for Complete Report!!!", vbInformation, "Reports Generated"


TDConnection.Disconnect


TDConnection.ReleaseConnection

Set TDConnection = Nothing


ActiveSheet.ChartObjects("OverallProgramTestScript").Activate


ActiveChart.SetSourceData Source:=Sheets("OverallProgram").Range("C2:F" & COUNT1 + 3)

ActiveChart.SeriesCollection(1).XValues = "='OverallProgram'!$B$3:$F$ " & COUNT1 + 3

Cells.Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Selection.Borders(xlEdgeLeft).LineStyle = xlNone

Selection.Borders(xlEdgeTop).LineStyle = xlNone

Selection.Borders(xlEdgeBottom).LineStyle = xlNone

Selection.Borders(xlEdgeRight).LineStyle = xlNone

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("B2:F" & COUNT1 + 3).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Range("B2:F2").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("B2:F" & COUNT1 + 3).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0
>.TintAndShade = 0

.Weight = xlThin

End With

Err_Handler:

On Error Resume Next

TDConnection.Disconnect

TDConnection.Logout

TDConnection.ReleaseConnection

Set TDConnection = Nothing

On Error GoTo 0


End Sub


Query is working fine. only thing is the data in the graph is not showing upto the end.

could anybody help me with this issue its very urgent.
 
Hi Jeyprakash ,


You say you want the range to be B2:F48 ( 47 rows ) , but you also say your data is 45 rows ; which is correct ? The relevant statements are :

[pre]
Code: 
ActiveChart.SetSourceData Source:=Sheets("OverallProgram").Range("C2:F" & COUNT1 + 3)
ActiveChart.SeriesCollection(1).XValues = "='OverallProgram'!$B$3:$F$ " & COUNT1 + 3
[/pre]
What is the value of COUNT1 ?


Narayan
 
Hi, m_jeyprakash@yahoo.com!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd strongly recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


It seems to be the same as posted here:

http://chandoo.org/forums/topic/dynamic-graphs-with-named-ranges#post-26984


For your information (as I assume you haven't read yet the Posting Rules & Etiquette topic) you're incurring in two non well seen actions:

a) hijacking another user topic for asking a non-related question

b) cross-posting which stands for posting the same issue in different places.


No problem because you're a new user, but it'd be very nice if you can avoid this practices, as it's well described in the green topics.


Regards!
 
You must log in or register to reply here.
Back
Top