Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
Apparently I have told the Code to skip this step I just don't know what i
did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
what does Vlbl say (coding)?
susan On Jul 18, 1:19*pm, KWhamill wrote: * Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") * * * * Set Crnc = .Find(what:="Currency", LookIn:=xlValues) * * * * * * If Crnc Is Nothing Then * * * * * * * * GoTo Vlbl <--------It goes straight here * * * * * * * *Curr *<------This is the Sub it's skipping * * * * * * End If * * End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
Goto is a very bad coding practice. What goto does is it takes you to a
different section of code. It does not return you back when that section of code is complete. Goto statements are good for error handling or in the very rare (and I mean extremely rare) case where it improves performance. I personally have never had a large enough performance gain using it that I ever put it into production code. Perhaps a procedure call or such would be more approptiate... -- HTH... Jim Thomlinson "KWhamill" wrote: Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
The whole goal of goto in this application is Error handling. If the Find
doesn't locate "curr" then we skip the Sub "Crnc" and go to the next sub. Here is a little bit more of it to help clear things up: MCA <----- A Sub which finds data on the source worksheet and transfer it to the target worksheet With Worksheets("Entries").Rows("1:7") <-------The source worksheet Set C = .Find(what:="Curr", LookIn:=xlValues) If C Is Nothing Then GoTo Vlbl <----label for The next Sub Crnc <-------- Sub Looks for "Curr" on the source and returns any values in that column End If End With Vlbl: ValName Sorry about the changes i'm cleaning up and making changes as i work. I would appreciate any ideas that might make this thing work. there are several of these THnaks, Karl "Jim Thomlinson" wrote: Goto is a very bad coding practice. What goto does is it takes you to a different section of code. It does not return you back when that section of code is complete. Goto statements are good for error handling or in the very rare (and I mean extremely rare) case where it improves performance. I personally have never had a large enough performance gain using it that I ever put it into production code. Perhaps a procedure call or such would be more approptiate... -- HTH... Jim Thomlinson "KWhamill" wrote: Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
if you change
GoTo Vlbl to Call Vlbl and add Call Crnc then it will call & run Vlbl, and when finished, return here to call & run Crnc. if it doesn't find "Curr", it skips both subs and goes to End If. i think that's what you want. (personally i hate to see a macro name just hanging out there like "Crnc" with no indication of what it is or what you're doing with it. that's why i always use "Call" in front of it - it's clearer, but not necessary.) hope it helps susan On Jul 18, 1:54*pm, KWhamill wrote: The whole goal of goto in this application is Error handling. If the Find doesn't locate "curr" then we skip the Sub "Crnc" and go to the next sub. Here is a little bit more of it to help clear things up: *MCA <----- A Sub which finds data on the source worksheet and transfer it to the target worksheet * * With Worksheets("Entries").Rows("1:7") *<-------The source worksheet * * * * Set C = .Find(what:="Curr", LookIn:=xlValues) * * * * * * If C Is Nothing Then * * * * * * * * GoTo Vlbl *<----label for The next Sub * * * * * * * Crnc *<-------- Sub Looks for "Curr" on the source and returns any values in that column * * * * * * End If * * End With Vlbl: * * ValName Sorry about the changes i'm cleaning up and making changes as i work. I would appreciate any ideas that might make this thing work. there are several of these THnaks, Karl "Jim Thomlinson" wrote: Goto is a very bad coding practice. What goto does is it takes you to a different section of code. It does not return you back when that section of code is complete. Goto statements are good for error handling or in the very rare (and I mean extremely rare) case where it improves performance. I personally have never had a large enough performance gain using it that I ever put it into production code. Perhaps a procedure call or such would be more approptiate... -- HTH... Jim Thomlinson "KWhamill" wrote: * Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") * * * * Set Crnc = .Find(what:="Currency", LookIn:=xlValues) * * * * * * If Crnc Is Nothing Then * * * * * * * * GoTo Vlbl <--------It goes straight here * * * * * * * *Curr *<------This is the Sub it's skipping * * * * * * End If * * End With- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
Thank you for those tips, I'm in the process of cleaning up this Macro and
making it work and I have made some of the following changes. 1 the If statement should be If/then/else. the Else is what was missing so now it Reads Else Crnc and it works. So in a sence you're right Vba just Didn't know what todo with Crnc. 2 I'm in the process of going through and adding comments its a long code so i am not done yet So for clarity here is the corrected bit of script: With Worksheets("Entries").Rows("1:7") Set Cr = .Find(what:="Currency", LookIn:=xlValues) If Cr Is Nothing Then GoTo Vs Else: Crnc 'Copy Currency if applicable End If End With Also because you asked here is what the Sub Does. it looks on the source worksheet for the Column Label Currency and Copies what ever it finds below that and pastes it to the target worksheet. Vlbl does the same but looks for something else. Any siggestions on how to make this thing work better would be vastly appreciated Thank you, Karl "Susan" wrote: if you change GoTo Vlbl to Call Vlbl and add Call Crnc then it will call & run Vlbl, and when finished, return here to call & run Crnc. if it doesn't find "Curr", it skips both subs and goes to End If. i think that's what you want. (personally i hate to see a macro name just hanging out there like "Crnc" with no indication of what it is or what you're doing with it. that's why i always use "Call" in front of it - it's clearer, but not necessary.) hope it helps susan On Jul 18, 1:54 pm, KWhamill wrote: The whole goal of goto in this application is Error handling. If the Find doesn't locate "curr" then we skip the Sub "Crnc" and go to the next sub. Here is a little bit more of it to help clear things up: MCA <----- A Sub which finds data on the source worksheet and transfer it to the target worksheet With Worksheets("Entries").Rows("1:7") <-------The source worksheet Set C = .Find(what:="Curr", LookIn:=xlValues) If C Is Nothing Then GoTo Vlbl <----label for The next Sub Crnc <-------- Sub Looks for "Curr" on the source and returns any values in that column End If End With Vlbl: ValName Sorry about the changes i'm cleaning up and making changes as i work. I would appreciate any ideas that might make this thing work. there are several of these THnaks, Karl "Jim Thomlinson" wrote: Goto is a very bad coding practice. What goto does is it takes you to a different section of code. It does not return you back when that section of code is complete. Goto statements are good for error handling or in the very rare (and I mean extremely rare) case where it improves performance. I personally have never had a large enough performance gain using it that I ever put it into production code. Perhaps a procedure call or such would be more approptiate... -- HTH... Jim Thomlinson "KWhamill" wrote: Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
Going by what code you chose to put in your post, if Crnc is, in fact,
nothing, then you would have gotten an error in the "Set Crnc =" line. If Crnc is something, which it is because you didn't get the error, then your IF statement will ALWAYS skip the call to "Curr". ALWAYS. But, then again, the GoTo statement that you have immediately before the call to "Curr" guarantees that the code will never get to the call to "Curr". I don't know the logic of your code, but maybe you need an "Else" before the call to "Curr". HTH Otto "KWhamill" wrote in message ... Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
Actually that was precisely the right thing to do. I explain it fully in a
prior post. Thank you, Karl "Otto Moehrbach" wrote: Going by what code you chose to put in your post, if Crnc is, in fact, nothing, then you would have gotten an error in the "Set Crnc =" line. If Crnc is something, which it is because you didn't get the error, then your IF statement will ALWAYS skip the call to "Curr". ALWAYS. But, then again, the GoTo statement that you have immediately before the call to "Curr" guarantees that the code will never get to the call to "Curr". I don't know the logic of your code, but maybe you need an "Else" before the call to "Curr". HTH Otto "KWhamill" wrote in message ... Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
It is only an error if the system throws an error. What you have is a
potentialy expected result. An error would be something like Runtime Error: 1004 Subsctript out of range... or such. Errors halt execution. IMO you are using Goto in a very bad way. -- HTH... Jim Thomlinson "KWhamill" wrote: The whole goal of goto in this application is Error handling. If the Find doesn't locate "curr" then we skip the Sub "Crnc" and go to the next sub. Here is a little bit more of it to help clear things up: MCA <----- A Sub which finds data on the source worksheet and transfer it to the target worksheet With Worksheets("Entries").Rows("1:7") <-------The source worksheet Set C = .Find(what:="Curr", LookIn:=xlValues) If C Is Nothing Then GoTo Vlbl <----label for The next Sub Crnc <-------- Sub Looks for "Curr" on the source and returns any values in that column End If End With Vlbl: ValName Sorry about the changes i'm cleaning up and making changes as i work. I would appreciate any ideas that might make this thing work. there are several of these THnaks, Karl "Jim Thomlinson" wrote: Goto is a very bad coding practice. What goto does is it takes you to a different section of code. It does not return you back when that section of code is complete. Goto statements are good for error handling or in the very rare (and I mean extremely rare) case where it improves performance. I personally have never had a large enough performance gain using it that I ever put it into production code. Perhaps a procedure call or such would be more approptiate... -- HTH... Jim Thomlinson "KWhamill" wrote: Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code is skipping stepps
Jim,
You make a good point but, when you search for something or you use Find, the system treats it like an error when it can't find it, You'll get a run time error about an Unspecified Variable or something like that. So i need an if statement to handle the error and just skip that sub. obviously if the thing i am looking for is not there then I don't need to do anything with it. I'm not trying to argue that the GoTo is the best or only choice, I am hardly an expert. I'm all about learning so if there is an alternative I'm all ears, or in this case eyes. R, Karl "Jim Thomlinson" wrote: It is only an error if the system throws an error. What you have is a potentialy expected result. An error would be something like Runtime Error: 1004 Subsctript out of range... or such. Errors halt execution. IMO you are using Goto in a very bad way. -- HTH... Jim Thomlinson "KWhamill" wrote: The whole goal of goto in this application is Error handling. If the Find doesn't locate "curr" then we skip the Sub "Crnc" and go to the next sub. Here is a little bit more of it to help clear things up: MCA <----- A Sub which finds data on the source worksheet and transfer it to the target worksheet With Worksheets("Entries").Rows("1:7") <-------The source worksheet Set C = .Find(what:="Curr", LookIn:=xlValues) If C Is Nothing Then GoTo Vlbl <----label for The next Sub Crnc <-------- Sub Looks for "Curr" on the source and returns any values in that column End If End With Vlbl: ValName Sorry about the changes i'm cleaning up and making changes as i work. I would appreciate any ideas that might make this thing work. there are several of these THnaks, Karl "Jim Thomlinson" wrote: Goto is a very bad coding practice. What goto does is it takes you to a different section of code. It does not return you back when that section of code is complete. Goto statements are good for error handling or in the very rare (and I mean extremely rare) case where it improves performance. I personally have never had a large enough performance gain using it that I ever put it into production code. Perhaps a procedure call or such would be more approptiate... -- HTH... Jim Thomlinson "KWhamill" wrote: Apparently I have told the Code to skip this step I just don't know what i did wrong: With Worksheets("Entries").Rows("1:7") Set Crnc = .Find(what:="Currency", LookIn:=xlValues) If Crnc Is Nothing Then GoTo Vlbl <--------It goes straight here Curr <------This is the Sub it's skipping End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference row on another sheet skipping zeros but not skipping li. | Excel Discussion (Misc queries) | |||
VBA Email Macro Skipping code | Excel Programming | |||
skipping cells | Excel Programming | |||
Code skipping when screenupdating is turned off | Excel Programming | |||
Skipping sections of code | Excel Programming |