Where is the problem???

[Guest]

Hi,

i'm using this to change chart like this:

ActiveChart.SetSourceData Source:=Sheets("DATEN").Range("" & von & ":" & bis
& "1,A1:A11"), PlotBy:=xlRows

where von and bis are given by user from inputbox
it was working but than it gives me an error 1004. why?

Thanks
Henrich

 

[Jon Peltier]

Assuming von is a letter and number combination and bis is just a letter:

ActiveChart.SetSourceData Source:=Sheets("DATEN").Range(von & ":" & bis &
"1,A1:A11"), PlotBy:=xlRows

I would assume that this is fraught with errors in normal usage. Are your
variables somehow typed in? Do you have any validation?

- Jon

 

[Guest]

Jon, thanks for response, but the formula is OK. When i set breakpoints in MS
office Visual Basic editor the code stop at the breakpoint and when i resume
everything works well.

I send U on your email the whole code if that should help.

Henrich

 

[Jon Peltier]

I don't need the whole code, thanks, I wouldn't have time to read through
it. However, if you posted a dozen or so lines here to give a sense of
context and answer some questions. What kind of chart is it, what kind of
data are you trying to plot, is something different about the data when it
works and when it doesn't work?

- Jon

 

[Guest]

Hi,

belowe are to most important parts of the code, data arent changed they are
retrieved from another worksheet.

von = InputBox("Zobrazit prehlad od tyzdna:", "Tyzden od")
bis = InputBox("Zobrazit prehlad do tyzdna:", "Tyzden do")
Sheets("DATEN").Activate
Range("A1").Select
Cells.Find(What:=von, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
von = ActiveCell.Address
Range("A40").FormulaR1C1 = von
Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
von = Range("A40").FormulaR1C1

Range("A1").Select
Cells.Find(What:=bis, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
bis = ActiveCell.Address
Range("A40").FormulaR1C1 = bis
Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
bis = Range("A40").FormulaR1C1

Sheets("AUSLASTUNG SK").Select
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData Source:=Sheets("DATEN").Range("" & von &
":" & bis & "1,A1:A11"), _
PlotBy:=xlRows


i have checked the whole code with breakpoints and everything seems well and
as i said when i give breakpoint on tline with 'ActiveChart.SetSourceData...'
and then resume it gos without any error.

Henrich

 

[Jon Peltier]

von = InputBox("Zobrazit prehlad od tyzdna:", "Tyzden od")
bis = InputBox("Zobrazit prehlad do tyzdna:", "Tyzden do")

Are these ranges? If so, use

von = Application.InputBox("Zobrazit prehlad od tyzdna:", "Tyzden od", , , ,
, , 8)

The last parameter of 8 tells Excel only to accept a range selected by the
user.

But I can't tell what von and bis are, I mean, I know the German words, but
you haven't declared them as any particular type (string, range, whatever),
and then you assign values and reuse the variables many times:

von = InputBox("Zobrazit prehlad od tyzdna:", "Tyzden od")
Cells.Find(What:=von, ....
von = ActiveCell.Address
von = Range("A40").FormulaR1C1

I think if you went to the Application.InputBox, and think of what you can
do with the range or the range.Address, you could probably do away with all
the Cells.Find and Cells.Replace which slow things down as well as confuse
the code.

- Jon

 

[Guest]

Thanks, i made some changes as you supposed and solved it.