Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop works for x = 1 but not for x = 2 : gets Type Mismatch error
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 failu 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop works for x = 1 but not for x = 2 : gets Type Mismatch error
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" wrote: 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 failu 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop works for x = 1 but not for x = 2 : gets Type Mismatch er
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 "ker_01" wrote: 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" wrote: 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 failu 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DoWhile Loop & Type mismatch error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Help - Type mismatch when running loop with strings from arrays | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code works 1st time then Type Mismatch when checking for not blank cells | Excel Programming |