Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conflict of one loop to another?
Hi Everybody, I am trying to figure out why my main script can't call a second script to run after done running without causes an error. I would like to combine it all into one smooth continuous step but it seems not possible here are the scripts. Dim ycps As Range Dim xconcen As Range Dim element As Range Dim slp As Double Dim ycept As Double Dim sample As Range Dim myactivecell As Range 'stores active cell position Set myactivecell = ActiveCell 'ug/l evaluation Set ycps = Application.InputBox(Prompt:="Select counts with the mouse(calibration y axis)", Type:=8) Set xconcen = Application.InputBox(Prompt:="Select respective concentrations with the mouse(calibration x axis)", Type:=8) Set element = Application.InputBox(Prompt:="Select the element to be calibrated with the mouse(chart title)", Type:=8) slp = Application.WorksheetFunction.Slope(ycps, xconcen) ycept = Application.WorksheetFunction.Intercept(ycps, xconcen) 'set up for do loop Dim i As Integer Dim C As Range Set sample = ActiveCell Set C = ActiveCell ActiveCell.Offset(0, 1) = (sample - ycept) / slp Do Until ActiveCell.Value = Empty Application.ScreenUpdating = False i = i + 1 C.Cells(i, 1).Select Set sample = ActiveCell C.Cells(i, 1).Offset(0, 1) = (sample - ycept) / slp Loop myactivecell.Activate 'returns to start activecell position Call Blanks 'here is where the second script is called but can't run End Sub Public Sub Blanks() 'Blanks subtraction Dim blks As Range Dim blk_avg As Double Dim ugl As Range Set blks = Application.InputBox(Prompt:="Select blanks with the mouse(blank average)", Type:=8) ActiveCell.EntireColumn.Offset(0, 2).Insert blk_avg = Application.WorksheetFunction.Average(blks) With ActiveCell .Offset(-1, 1) = blk_avg .Offset(-1, 0) = "Blank Avg" .Offset(-2, 1) = "ug/l" .Offset(-2, 2) = "ug/l - Blank" End With Set ugl = ActiveCell.Offset(0, 1) ActiveCell.Offset(0, 2) = ugl - blk_avg Dim z As Integer Dim B As Range Set B = ActiveCell Application.ScreenUpdating = False Do Until ActiveCell.Value = Empty z = z + 1 B.Cells(z, 1).Select Set ugl = ActiveCell.Offset(0, 1) B.Cells(z, 1).Offset(0, 2) = ugl - blk_avg Loop End Sub Would be really grateful for the help? thanks, brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
conflict of one loop to another?
have you tried.. Sub Demo() Call MainScript 'we don't see the heading SUB... Call Blanks End Sub "bri_lost" wrote in message ... Hi Everybody, I am trying to figure out why my main script can't call a second script to run after done running without causes an error. I would like to combine it all into one smooth continuous step but it seems not possible here are the scripts. Dim ycps As Range Dim xconcen As Range Dim element As Range Dim slp As Double Dim ycept As Double Dim sample As Range Dim myactivecell As Range 'stores active cell position Set myactivecell = ActiveCell 'ug/l evaluation Set ycps = Application.InputBox(Prompt:="Select counts with the mouse(calibration y axis)", Type:=8) Set xconcen = Application.InputBox(Prompt:="Select respective concentrations with the mouse(calibration x axis)", Type:=8) Set element = Application.InputBox(Prompt:="Select the element to be calibrated with the mouse(chart title)", Type:=8) slp = Application.WorksheetFunction.Slope(ycps, xconcen) ycept = Application.WorksheetFunction.Intercept(ycps, xconcen) 'set up for do loop Dim i As Integer Dim C As Range Set sample = ActiveCell Set C = ActiveCell ActiveCell.Offset(0, 1) = (sample - ycept) / slp Do Until ActiveCell.Value = Empty Application.ScreenUpdating = False i = i + 1 C.Cells(i, 1).Select Set sample = ActiveCell C.Cells(i, 1).Offset(0, 1) = (sample - ycept) / slp Loop myactivecell.Activate 'returns to start activecell position Call Blanks 'here is where the second script is called but can't run End Sub Public Sub Blanks() 'Blanks subtraction Dim blks As Range Dim blk_avg As Double Dim ugl As Range Set blks = Application.InputBox(Prompt:="Select blanks with the mouse(blank average)", Type:=8) ActiveCell.EntireColumn.Offset(0, 2).Insert blk_avg = Application.WorksheetFunction.Average(blks) With ActiveCell .Offset(-1, 1) = blk_avg .Offset(-1, 0) = "Blank Avg" .Offset(-2, 1) = "ug/l" .Offset(-2, 2) = "ug/l - Blank" End With Set ugl = ActiveCell.Offset(0, 1) ActiveCell.Offset(0, 2) = ugl - blk_avg Dim z As Integer Dim B As Range Set B = ActiveCell Application.ScreenUpdating = False Do Until ActiveCell.Value = Empty z = z + 1 B.Cells(z, 1).Select Set ugl = ActiveCell.Offset(0, 1) B.Cells(z, 1).Offset(0, 2) = ugl - blk_avg Loop End Sub Would be really grateful for the help? thanks, brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
conflict of one loop to another?
1. After the loop just before calling the procedure 'Blanks' set the screen updating to True and try again., 2. Place a msgbox just before calling the 'Blanks' Procedure. 'your code Loop Application.ScreenUpdating = True myactivecell.Activate 'returns to start activecell position Msgbox "Done" Call Blanks If this post helps click Yes --------------- Jacob Skaria "bri_lost" wrote: Hi Everybody, I am trying to figure out why my main script can't call a second script to run after done running without causes an error. I would like to combine it all into one smooth continuous step but it seems not possible here are the scripts. Dim ycps As Range Dim xconcen As Range Dim element As Range Dim slp As Double Dim ycept As Double Dim sample As Range Dim myactivecell As Range 'stores active cell position Set myactivecell = ActiveCell 'ug/l evaluation Set ycps = Application.InputBox(Prompt:="Select counts with the mouse(calibration y axis)", Type:=8) Set xconcen = Application.InputBox(Prompt:="Select respective concentrations with the mouse(calibration x axis)", Type:=8) Set element = Application.InputBox(Prompt:="Select the element to be calibrated with the mouse(chart title)", Type:=8) slp = Application.WorksheetFunction.Slope(ycps, xconcen) ycept = Application.WorksheetFunction.Intercept(ycps, xconcen) 'set up for do loop Dim i As Integer Dim C As Range Set sample = ActiveCell Set C = ActiveCell ActiveCell.Offset(0, 1) = (sample - ycept) / slp Do Until ActiveCell.Value = Empty Application.ScreenUpdating = False i = i + 1 C.Cells(i, 1).Select Set sample = ActiveCell C.Cells(i, 1).Offset(0, 1) = (sample - ycept) / slp Loop myactivecell.Activate 'returns to start activecell position Call Blanks 'here is where the second script is called but can't run End Sub Public Sub Blanks() 'Blanks subtraction Dim blks As Range Dim blk_avg As Double Dim ugl As Range Set blks = Application.InputBox(Prompt:="Select blanks with the mouse(blank average)", Type:=8) ActiveCell.EntireColumn.Offset(0, 2).Insert blk_avg = Application.WorksheetFunction.Average(blks) With ActiveCell .Offset(-1, 1) = blk_avg .Offset(-1, 0) = "Blank Avg" .Offset(-2, 1) = "ug/l" .Offset(-2, 2) = "ug/l - Blank" End With Set ugl = ActiveCell.Offset(0, 1) ActiveCell.Offset(0, 2) = ugl - blk_avg Dim z As Integer Dim B As Range Set B = ActiveCell Application.ScreenUpdating = False Do Until ActiveCell.Value = Empty z = z + 1 B.Cells(z, 1).Select Set ugl = ActiveCell.Offset(0, 1) B.Cells(z, 1).Offset(0, 2) = ugl - blk_avg Loop End Sub Would be really grateful for the help? thanks, brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
conflict of one loop to another?
Hey Jacob, Thanks a million I would have never thought that the problem lies in screenupdating. Thanks a lot functions perfect now. Brian "Jacob Skaria" wrote: 1. After the loop just before calling the procedure 'Blanks' set the screen updating to True and try again., 2. Place a msgbox just before calling the 'Blanks' Procedure. 'your code Loop Application.ScreenUpdating = True myactivecell.Activate 'returns to start activecell position Msgbox "Done" Call Blanks If this post helps click Yes --------------- Jacob Skaria "bri_lost" wrote: Hi Everybody, I am trying to figure out why my main script can't call a second script to run after done running without causes an error. I would like to combine it all into one smooth continuous step but it seems not possible here are the scripts. Dim ycps As Range Dim xconcen As Range Dim element As Range Dim slp As Double Dim ycept As Double Dim sample As Range Dim myactivecell As Range 'stores active cell position Set myactivecell = ActiveCell 'ug/l evaluation Set ycps = Application.InputBox(Prompt:="Select counts with the mouse(calibration y axis)", Type:=8) Set xconcen = Application.InputBox(Prompt:="Select respective concentrations with the mouse(calibration x axis)", Type:=8) Set element = Application.InputBox(Prompt:="Select the element to be calibrated with the mouse(chart title)", Type:=8) slp = Application.WorksheetFunction.Slope(ycps, xconcen) ycept = Application.WorksheetFunction.Intercept(ycps, xconcen) 'set up for do loop Dim i As Integer Dim C As Range Set sample = ActiveCell Set C = ActiveCell ActiveCell.Offset(0, 1) = (sample - ycept) / slp Do Until ActiveCell.Value = Empty Application.ScreenUpdating = False i = i + 1 C.Cells(i, 1).Select Set sample = ActiveCell C.Cells(i, 1).Offset(0, 1) = (sample - ycept) / slp Loop myactivecell.Activate 'returns to start activecell position Call Blanks 'here is where the second script is called but can't run End Sub Public Sub Blanks() 'Blanks subtraction Dim blks As Range Dim blk_avg As Double Dim ugl As Range Set blks = Application.InputBox(Prompt:="Select blanks with the mouse(blank average)", Type:=8) ActiveCell.EntireColumn.Offset(0, 2).Insert blk_avg = Application.WorksheetFunction.Average(blks) With ActiveCell .Offset(-1, 1) = blk_avg .Offset(-1, 0) = "Blank Avg" .Offset(-2, 1) = "ug/l" .Offset(-2, 2) = "ug/l - Blank" End With Set ugl = ActiveCell.Offset(0, 1) ActiveCell.Offset(0, 2) = ugl - blk_avg Dim z As Integer Dim B As Range Set B = ActiveCell Application.ScreenUpdating = False Do Until ActiveCell.Value = Empty z = z + 1 B.Cells(z, 1).Select Set ugl = ActiveCell.Offset(0, 1) B.Cells(z, 1).Offset(0, 2) = ugl - blk_avg Loop End Sub Would be really grateful for the help? thanks, brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conflict Between Two Add-Ins | Excel Programming | |||
Name conflict | Excel Discussion (Misc queries) | |||
Name Conflict | Excel Discussion (Misc queries) | |||
Conflict | Excel Programming | |||
Conflict | Excel Programming |