Macroproblem: Proceed another macro after end of loop

  • Thread starter Thread starter Snoopy
  • Start date Start date
S

Snoopy

Hey guys :)))
Specially thanks to Bob and Ian - who have been very kind and helped
me a lot
However; The more we learn - the more we know how little we know!

So - you guys have carried me over a threshold in using VBA-EXCEL!
Still I find VBA mysterious and difficult to understand some times.

The macroproblem this time is that when my (well.....actueally yours
modyfied:) ) macroloop has done its work, it will not "jump" to the
next macro - see below

Sub AVD()
displayallert = False
Dim MyFile
' Returns filename with specified extension. If more than one *.xls
' file exists, the first file found is returned.
Sheets("UserDATA").Activate
MyFile = Dir(Range("A6").Value & "\*.xls")
If MyFile = "" Then End ' End subroutine if no *.xls files found
Call aaa(MyFile) ' call your macro
Do While MyFile <> ""
' Call Dir again without arguments to return the next *.xls file in
the same directory.
MyFile = Dir
If MyFile = "" Then End ' End subroutine if no further *.xls files
found
Call aaa(MyFile) ' call your macro
Sheets("UserDATA").Visible = False
' So far - so good :)
Loop
' Here comes the problem :( - after "Loop" the macro wil nor proceed
thew BackUp-sequence

BackUpAVD ' will not activate
displayallert = True
End Sub

What can I do to smoothen up this macro?

Best regards
Snoopy
 
Nothing obvious jumps out at me.

Do you get an error, or does the macro just end with no action?
 
First, I don't think it's good practice to use "End" like this:
If MyFile = "" Then End

I'd use:
If MyFile = "" Then Exit Sub

Second, if you're using a shortcut key that starts your macro and that called
procedure opens another file, then remove the shift-key from your shortcut key
combination.
 
First, I don't think it's good practice to use "End" like this:
If MyFile = "" Then End

I'd use:
If MyFile = "" Then Exit Sub

Second, if you're using a shortcut key that starts your macro and that called
procedure opens another file, then remove the shift-key from your shortcut key
combination.












--

Dave Peterson– Skjul sitert tekst –

– Vis sitert tekst –
Click to expand...

Thanks
"A small step for the man - a giant step for mankind"
Thanks Dave
Regards
Snoopy
 
Hey guys :)))
Specially thanks to Bob and Ian - who have been very kind and helped
me a lot
However; The more we learn - the more we know how little we know!

So - you guys have carried me over a threshold in using VBA-EXCEL!
Still I find VBA mysterious and difficult to understand some times.

The macroproblem this time is that when my (well.....actueally yours
modyfied:) ) macroloop has done its work, it will not "jump" to the
next macro - see below

Sub AVD()
displayallert = False
Dim MyFile
' Returns filename with specified extension. If more than one *.xls
' file exists, the first file found is returned.
Sheets("UserDATA").Activate
MyFile = Dir(Range("A6").Value & "\*.xls")
If MyFile = "" Then End ' End subroutine if no *.xls files found
Call aaa(MyFile) ' call your macro
Do While MyFile <> ""
' Call Dir again without arguments to return the next *.xls file in
the same directory.
MyFile = Dir
If MyFile = "" Then End ' End subroutine if no further *.xls files
found
Call aaa(MyFile) ' call your macro
Sheets("UserDATA").Visible = False
' So far - so good :)
Loop
Click to expand...

Because the loop can never complete. If MyFile <> "" it loops. Then if the next
Myfile is "" your IF does an END. There is no way of falling through the LOOP.
The whole IF MyFile = "" is redundant. If you deleted it the Do While would fall
out the bottom of the LOOP as soon as it became "" and you following code would
be executed.
 
Back
Top