Loop works for x = 1 but not for x = 2 : gets Type Mismatch error

[Cinco]

I've included a portion of a Do loop below. It works (no Run Type Error 13,
Type Mismatch) for x = 1 but fails for x =2. The mismatch occurs on loop #2
with this statement highlighted: Range("$B$1").Value =
Cells(Range("$H$4").Value + x, "D").Value

The H4 cell contains a valid row number which was 270 for loop 1 and is now
271 for loop 2. The totalnumberofsessions value is 4. Here are the Watch
values at time of the failure:
Watch : : Range("$B$1").Value : 7/4/2009 11:24:44 PM : Variant/Date :
Module18.ChartSessions
Watch : : Range("$H$4").Value : 269 : Variant/Double :
Module18.ChartSessions
Watch : : Range("$H$4").Value + x : 271 : Variant/Double :
Module18.ChartSessions
Watch : : x : 2 : Integer : Module18.ChartSessions

I would appreciate someone letting me know what I am doing wrong and how I
can get this to work. Thanks and have a good day.
Jim



' Cycle through all the sessions to process and print charts for each
individual session
Dim x As Integer
Dim totalnumberofsessions As Integer
totalnumberofsessions = Range("$F$4")
x = 1
Do While x <= totalnumberofsessions

Range("$B$1").Value = Cells(Range("$H$4").Value + x, "D").Value
Range("$C$2").Value = Cells(Range("$H$4").Value + x, "F").Value

*** working code here **************

x = x + 1
Loop

 

[ker_01]

In your code, do you select another worksheet, a chart, or some other object?
My best guess is that you switch focus from your source worksheet, then the
code needs to know exactly where to go get your data (hence the error). My
guess is that this code would work on x=1 if you had that sheet selected when
you started the code, then the loss of focus would affect any subsequent
runs. You can easily test this; put a breakpoint in above the line that blows
up, then when it stops, go back into the main excel window and select the
main data sheet, then continue the code and see if it gets past x=2 and dies
on x=3.

If that does turn out to be the problem, try adding a sheet reference for
your ranges, e.g. instead of
Range("$B$1").Value = Cells(Range("$H$4").Value + x, "D").Value
something like
Sheet1.Range("$B$1").Value = Cells(Sheet1.Range("$H$4").Value + x, "D").Value

HTH,
Keith

 

[Cinco]

Keith,

Thanks for clearing up my "mystery". As part of the loop, I am adjusting
the X-Axis and Y-Axis on my charts before I print them. For example,

With Sheet2.ChartObjects(1).Chart.Axes(1, 1)
.MinimumScale = Range("$E$2")
.MaximumScale = Range("$F$2")
.CrossesAt = Range("$E$2")
End With

I changed the range commands to specifically refer to Sheet1 and everything
is now AOK. Thanks a lot on solving this one - I was at my wit's end.
Jim