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

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


End Sub

Would be really grateful for the help?




Patrick Molloy

have you tried..

Sub Demo()
Call MainScript 'we don't see the heading SUB...
Call Blanks
End Sub

Jacob Skaria

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
Application.ScreenUpdating = True
myactivecell.Activate 'returns to start activecell position
Msgbox "Done"
Call Blanks

If this post helps click Yes




Hey Jacob,

Thanks a million I would have never thought that the problem lies in
screenupdating. Thanks a lot functions perfect now.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question