JKB
10-20-2015, 08:27 AM
Hi everybody.
I have posted this problem earlier without luck, but now i have some additional info, which will hopefully help solving the problem...
My problem is the following:
The function "Adjust_graphs" works just fine without the if statement. I.e. if i remove everything which is purple and call Adjust_Graphs using the button in overview i have no problems! But when i run the function within the if statement, it goes into debug mode here:
shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)
Hence i have concluded that there's something fishy about that if statement, and that the function works just fine. Can anybody help me?
Code:
Sub Button2_Click()
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
If shtHis.Range("A2").Value <> Date Then
shtHis.Range("A2").EntireRow.Insert
shtHis.Range("A2").Value = Date
shtOv.Range("H3:H6").Copy
shtHis.Range("B2").PasteSpecial Transpose:=True
Call Adjust_Graphs
End If
'Call Adjust_Graphs
End Sub
-----------
Sub Adjust_Graphs()
'Defining sheets/Variables
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lHorizon As Long: lHorizon = shtHis.Range("A2").End(xlDown).Row
'Adjusting graphs
shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)
shtHis.ChartObjects("Chart 2").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",C1:C" & lHorizon)
shtHis.ChartObjects("Chart 3").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",D1:D" & lHorizon)
shtHis.ChartObjects("Chart 4").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",E1:E" & lHorizon)
End Sub
--------
Additional Info:
I have two sheets.. "Overview" from which i copy some information into my other sheet "Historic". The launch-button is located in "Overview".
The code is supposed to do two things:
1) If the data in "Historic" is not up to date, it should copy the info, which is up to date from "Overview" and insert in Historic
2) If it inserted a new row, it should adjust the graphs (I have 4, which refer to five columns in the "Historic" sheet, A = Dates and B,C,D,E contains the y-axis data, such that it includes the new info, which has just been inserted. If the sheet is already up to date, it just exits the sub.
The point is that i want to be able to update the graph each day, and then it should at the new up to date info from "Overview".
I have posted this problem earlier without luck, but now i have some additional info, which will hopefully help solving the problem...
My problem is the following:
The function "Adjust_graphs" works just fine without the if statement. I.e. if i remove everything which is purple and call Adjust_Graphs using the button in overview i have no problems! But when i run the function within the if statement, it goes into debug mode here:
shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)
Hence i have concluded that there's something fishy about that if statement, and that the function works just fine. Can anybody help me?
Code:
Sub Button2_Click()
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
If shtHis.Range("A2").Value <> Date Then
shtHis.Range("A2").EntireRow.Insert
shtHis.Range("A2").Value = Date
shtOv.Range("H3:H6").Copy
shtHis.Range("B2").PasteSpecial Transpose:=True
Call Adjust_Graphs
End If
'Call Adjust_Graphs
End Sub
-----------
Sub Adjust_Graphs()
'Defining sheets/Variables
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lHorizon As Long: lHorizon = shtHis.Range("A2").End(xlDown).Row
'Adjusting graphs
shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)
shtHis.ChartObjects("Chart 2").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",C1:C" & lHorizon)
shtHis.ChartObjects("Chart 3").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",D1:D" & lHorizon)
shtHis.ChartObjects("Chart 4").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",E1:E" & lHorizon)
End Sub
--------
Additional Info:
I have two sheets.. "Overview" from which i copy some information into my other sheet "Historic". The launch-button is located in "Overview".
The code is supposed to do two things:
1) If the data in "Historic" is not up to date, it should copy the info, which is up to date from "Overview" and insert in Historic
2) If it inserted a new row, it should adjust the graphs (I have 4, which refer to five columns in the "Historic" sheet, A = Dates and B,C,D,E contains the y-axis data, such that it includes the new info, which has just been inserted. If the sheet is already up to date, it just exits the sub.
The point is that i want to be able to update the graph each day, and then it should at the new up to date info from "Overview".