End if w/o if?
I've combined 2 macros that I got help writing here.
Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
Hi,
If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
|
End if w/o if?
Hi Dave
check my comments in line ######### -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "davegb" wrote in message ups.com... I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" ########where is this IF's END IF???############ Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
No, he has a line continuation, so no End If needed.
-- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
But it could be End If to If Not TopCell Is Nothing which admittedly could
come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
I see your point. As I say, you should indent Ifs, and don't use single line
Ifs, it all gets too confusing. Bob "Toppers" wrote in message ... But it could be End If to If Not TopCell Is Nothing which admittedly could come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
Agreed!
"Bob Phillips" wrote: I see your point. As I say, you should indent Ifs, and don't use single line Ifs, it all gets too confusing. Bob "Toppers" wrote in message ... But it could be End If to If Not TopCell Is Nothing which admittedly could come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
|
End if w/o if?
Right, properly indenting the lines after
If Not TopCell Is Nothing Then ' if it found "top" would've helped. P.S. One line If statements are OK with me if they fit nicely on one line. "Toppers" wrote: But it could be End If to If Not TopCell Is Nothing which admittedly could come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
I avoid them like the plague Don, spent too much wasted time trying to line
up Ifs ... End Ifs. I think the lack of clarity and potential maintenance overhead dwarfs the initial effort. The only time I use anything like that is with Iif. Regards Bob "Don Guillett" wrote in message ... I use single line ifs much of the time -- Don Guillett SalesAid Software "Toppers" wrote in message ... Agreed! "Bob Phillips" wrote: I see your point. As I say, you should indent Ifs, and don't use single line Ifs, it all gets too confusing. Bob "Toppers" wrote in message ... But it could be End If to If Not TopCell Is Nothing which admittedly could come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
Boy, is my face red! Somehow, as I wrote the message, I put down that
there was an "End If w/o an If" error. It's an "End with w/o with" error, which is what I indicated in the code I posted. Does that make more sense? Sorry for the confusion, working on too many things at once. |
End if w/o if?
Hi Dave
but as far as i can tell you do need this extra END IF and if you put it in does your code then run correctly ... error messages aren't always 100% accurate :) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "davegb" wrote in message ups.com... Boy, is my face red! Somehow, as I wrote the message, I put down that there was an "End If w/o an If" error. It's an "End with w/o with" error, which is what I indicated in the code I posted. Does that make more sense? Sorry for the confusion, working on too many things at once. |
End if w/o if?
I agree with Don. Single-line If's save space and there's no lack of clarity
to me. I only indent the If -- Then -- Else -- End If constructs. Your indenting problems MAY be due to ElseIf's. Those I avoid like the plague. They are difficult to balance and indent (not one End If per If) But these are just personal style preferences. "Bob Phillips" wrote: I avoid them like the plague Don, spent too much wasted time trying to line up Ifs ... End Ifs. I think the lack of clarity and potential maintenance overhead dwarfs the initial effort. The only time I use anything like that is with Iif. Regards Bob "Don Guillett" wrote in message ... I use single line ifs much of the time -- Don Guillett SalesAid Software "Toppers" wrote in message ... Agreed! "Bob Phillips" wrote: I see your point. As I say, you should indent Ifs, and don't use single line Ifs, it all gets too confusing. Bob "Toppers" wrote in message ... But it could be End If to If Not TopCell Is Nothing which admittedly could come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
True, the compiler/interpreter can't see the "missing" With statement. The
End With appears to be inside the unclosed If statement. "JulieD" wrote: Hi Dave but as far as i can tell you do need this extra END IF and if you put it in does your code then run correctly ... error messages aren't always 100% accurate :) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "davegb" wrote in message ups.com... Boy, is my face red! Somehow, as I wrote the message, I put down that there was an "End If w/o an If" error. It's an "End with w/o with" error, which is what I indicated in the code I posted. Does that make more sense? Sorry for the confusion, working on too many things at once. |
End if w/o if?
It worked! Thanks a lot, Julie!
|
End if w/o if?
you're welcome
-- Cheers JulieD "davegb" wrote in message oups.com... It worked! Thanks a lot, Julie! |
End if w/o if?
Well we are completely at odds. The lack of clarity comes when you have
single line Ifs and Multi line Ifs combined, as you lose some of the power of indenting then. I don't have indenting problems, because I indent. And I use ElseIf liberally, it makes logical sense, and it avoids a myriad of indents in those cases. Of course, I am just as likely to use Case statements, but I would never rule out ElseIf. There is one End If per If, just an unlimited number of ElseIf. Bob "Charlie" wrote in message ... I agree with Don. Single-line If's save space and there's no lack of clarity to me. I only indent the If -- Then -- Else -- End If constructs. Your indenting problems MAY be due to ElseIf's. Those I avoid like the plague. They are difficult to balance and indent (not one End If per If) But these are just personal style preferences. "Bob Phillips" wrote: I avoid them like the plague Don, spent too much wasted time trying to line up Ifs ... End Ifs. I think the lack of clarity and potential maintenance overhead dwarfs the initial effort. The only time I use anything like that is with Iif. Regards Bob "Don Guillett" wrote in message ... I use single line ifs much of the time -- Don Guillett SalesAid Software "Toppers" wrote in message ... Agreed! "Bob Phillips" wrote: I see your point. As I say, you should indent Ifs, and don't use single line Ifs, it all gets too confusing. Bob "Toppers" wrote in message ... But it could be End If to If Not TopCell Is Nothing which admittedly could come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, ..Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
Yes, the Case statement is best when there's a multitude of ElseIf's.
As I said, it's a personal style thing, considering that either way is logically correct. "Bob Phillips" wrote: Well we are completely at odds. The lack of clarity comes when you have single line Ifs and Multi line Ifs combined, as you lose some of the power of indenting then. I don't have indenting problems, because I indent. And I use ElseIf liberally, it makes logical sense, and it avoids a myriad of indents in those cases. Of course, I am just as likely to use Case statements, but I would never rule out ElseIf. There is one End If per If, just an unlimited number of ElseIf. Bob "Charlie" wrote in message ... I agree with Don. Single-line If's save space and there's no lack of clarity to me. I only indent the If -- Then -- Else -- End If constructs. Your indenting problems MAY be due to ElseIf's. Those I avoid like the plague. They are difficult to balance and indent (not one End If per If) But these are just personal style preferences. "Bob Phillips" wrote: I avoid them like the plague Don, spent too much wasted time trying to line up Ifs ... End Ifs. I think the lack of clarity and potential maintenance overhead dwarfs the initial effort. The only time I use anything like that is with Iif. Regards Bob "Don Guillett" wrote in message ... I use single line ifs much of the time -- Don Guillett SalesAid Software "Toppers" wrote in message ... Agreed! "Bob Phillips" wrote: I see your point. As I say, you should indent Ifs, and don't use single line Ifs, it all gets too confusing. Bob "Toppers" wrote in message ... But it could be End If to If Not TopCell Is Nothing which admittedly could come before If .name as per Don's reply. Both are valid I think. "Bob Phillips" wrote: No, he has a line continuation, so no End If needed. -- HTH Bob Phillips "Toppers" wrote in message ... Hi, If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If ...... Missing here HTh "davegb" wrote: I've combined 2 macros that I got help writing here. Sub AllSheetsToggleProtectWInd() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht If .ProtectContents Then .Unprotect Password:=PWORD .Name = .Name & "##" .Columns.Hidden = False Else Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, ..Columns("AC")) Cols2Hide.Hidden = True .Protect Password:=PWORD If .Name Like "*[##]" Then _ .Name = Left(.Name, Len(.Name) - 2) End If End With<-----[end with without with error] Next wkSht End Sub But when I run it, I get an error of "End with without if", even though there's very clearly a "with wksht" statement at the top. If I remark out the End with, I get a "Next without For" error, even though there's a "For" statement. So any ideas on why VBA can't see my with or For each statements? Thanks in advance. |
End if w/o if?
Not sure I'm following some of your comments re indenting properly. I
imagine there are different approaches, just like naming variables, but can someone give me some guidelines to help avoid this type of problem? For instance, post how you would have indented my macro? Thanks! |
End if w/o if?
Hi Dave
the way i teach it is, consider statements like SUB / END SUB; WITH / END WITH, IF / ELSEIF / END IF etc as brackets ... everything inside the brackets should be indented one tab each time ... this way, you can easily read down a column and spot missing END IF or END WITH etc .. so your macro (not knowing exactly where your IF statment finishes), i would write as Sub AllSheetsToggleProtectWInd() {BLANK} {TAB}'for all sheets in currently active workbook, assigned to button {TAB}Dim TopCell As Range {TAB}Dim TopCol As Range {TAB}Dim Cols2Hide As Range {TAB}Dim wkSht As Worksheet {BLANK} {TAB}FOR EACH wkSht INActiveWorkbook.Worksheets {TAB}{TAB}WITH wkSht {TAB}{TAB}{TAB}IF.ProtectContents Then {TAB}{TAB}{TAB}{TAB}.Unprotect Password:=PWORD {TAB}{TAB}{TAB}{TAB}.Name = .Name & "##" {TAB}{TAB}{TAB}{TAB}.Columns.Hidden = False {TAB}{TAB}{TAB}ELSE {TAB}{TAB}{TAB}{TAB}Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) {TAB}{TAB}{TAB}{TAB}If Not TopCell Is Nothing Then ' if it found "top" {TAB}{TAB}{TAB}{TAB}{TAB}Set TopCol = .Columns(TopCell.Column) {TAB}{TAB}{TAB}{TAB}{TAB}Set Cols2Hide = .Range(TopCol, .Columns("AC")) {TAB}{TAB}{TAB}{TAB}{TAB}Cols2Hide.Hidden = True {TAB}{TAB}{TAB}{TAB}{TAB}.Protect Password:=PWORD {TAB}{TAB}{TAB}{TAB}END IF 'assuming it goes here {TAB}{TAB}{TAB}{TAB}IF .Name Like "*[##]" Then {TAB}{TAB}{TAB}{TAB}{TAB}.Name = Left(.Name, Len(.Name) - 2) {TAB}{TAB}{TAB}{TAB}END IF 'i'm not a big fan of one line IF statements either {TAB}{TAB}{TAB}END IF {TAB}{TAB}END WITH {TAB}NEXT wkSht End Sub -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "davegb" wrote in message ups.com... Not sure I'm following some of your comments re indenting properly. I imagine there are different approaches, just like naming variables, but can someone give me some guidelines to help avoid this type of problem? For instance, post how you would have indented my macro? Thanks! |
End if w/o if?
Thanks, Julie.
|
All times are GMT +1. The time now is 01:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com