Re: $$Excel-Macros$$ Re: Arrangement head of the table in the macro code
It would help if you indent your code so that you can easily see when you BEGIN
a block of code like WITH and IF statements, then have a matching CLOSING
statemtent.
>From your code, I see that you end up with a couple of "End IF" statements
>that
don't have an "IF" statement that they are ending! (hence the "End If without
Block If" error)
also, you seem to have an extra "Next Wks" statement.
I think you're going to hyave several other problems too.
I haven't followed the original thread, so I don't know what you're trying to
accomplish,
but if you've changed any numeric name to "Year " & wks.name,
then you cannot use: Wks.Name >= 2010 because Wks.Name is a STRING, not a
number!
Sub aaa()
Dim Wks As Worksheet
Dim i As Integer
With ThisWorkbook.Worksheets("table Z")
Dim OstW As Long: OstW = .Cells(Rows.Count, 4).End(xlUp).Row
If OstW > 14 Then
.Range("B14:E" & OstW).ClearContents
End If
.Range("D14:E14").Value = Array("A", "B")
.Range("B15:B19").Value = Application.Transpose(Array("X", "Y", "Z",
"G", "H"))
OstW = .Cells(Rows.Count, 4).End(xlUp).Row + 1
For i = 1 To 5
For Each Wks In ThisWorkbook.Worksheets
If IsNumeric(Wks.Name) Then
Wks.Name = "Year " & Wks.Name
End If
If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
End If
Next Wks
If i = 1 Then
Wks.Range("D15:E15").Copy .Range("D" & OstW)
.Range("C" & OstW).Value = Wks.Name
.Rows(OstW + 1).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 2 Then
Wks.Range("D19:E19").Copy .Range("D" & OstW + 1)
.Range("C" & OstW + 1).Value = Wks.Name
.Rows(OstW + 2).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 3 Then
Wks.Range("D23:E23").Copy .Range("D" & OstW + 2)
.Range("C" & OstW + 2).Value = Wks.Name
.Rows(OstW + 3).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 4 Then
Wks.Range("D27:E27").Copy .Range("D" & OstW + 3)
.Range("C" & OstW + 3).Value = Wks.Name
.Rows(OstW + 4).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 5 Then
Wks.Range("D31:E31").Copy .Range("D" & OstW + 4)
.Range("C" & OstW + 4).Value = Wks.Name
.Rows(OstW + 5).Insert Shift:=xlDown
OstW = OstW + 1
End If
End If
End If
Next Wks
Next i
End With
End Sub
Paul
-----------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-
From: artforart artforart
To: excel-macros@googlegroups.com
Sent: Wed, April 3, 2013 3:07:49 PM
Subject: $$Excel-Macros$$ Re: Arrangement head of the table in the macro code
Thank u for an answer. But unfortunately I have not been able to applied it.
Pls
find attached file. I've got again error: End if without block if.
W dniu środa, 3 kwietnia 2013 05:10:42 UTC+2 użytkownik Lalit Mohan Pandey
napisał:
You can change your code as mentioned below:
>
>
>
>For Each Wks In ThisWorkbook.Worksheets
>If IsNumeric(Wks.Name) Then
>Wks.Name = "Year " & Wks.Name
>end if
>If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
>end if
>next wks
>On Tuesday, 2 April 2013 20:01:41 UTC+5:30, artforart artforart wrote:
>If I add this bold part in code I've got End if without block if
>>
>>For Each Wks In ThisWorkbook.Worksheets
>>If IsNumeric(Wks.Name) Then Wks.Name = "Year " & Wks.Name
>>
>>If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
>>
>>Thank u for help
>>--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quic
$$Excel-Macros$$ Re: Arrangement head of the table in the macro code
Use it like this: For Each Wks In ThisWorkbook.Worksheets If IsNumeric(Wks.Name) Then Wks.Name = "Year " & Wks.Name end if If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then end if next wks On Tuesday, 2 April 2013 20:01:41 UTC+5:30, artforart artforart wrote: > > If I add this bold part in code I've got End if without block if > > For Each Wks In ThisWorkbook.Worksheets > If IsNumeric(Wks.Name) Then *Wks.Name = "Year " & Wks.Name* > > If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then > > Thank u for help > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Arrangement head of the table in the macro code
If I add this bold part in code I've got End if without block if For Each Wks In ThisWorkbook.Worksheets If IsNumeric(Wks.Name) Then *Wks.Name = "Year " & Wks.Name* If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then Thank u for help -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Arrangement head of the table in the macro code
To change worksheet name you are doing right and except this what you want is not clear so if there is something for which you need help you need to specify it clearly. On Tuesday, 2 April 2013 00:30:06 UTC+5:30, artforart artforart wrote: > > I must change a head in the original table. > I have some trouble with doing a change in code > > I've attached original file (macro it's ok) > and the second what I need > > > And another question what do I need to do to change worksheets names for > example "2010" into "year 2010" > I suppose I must doing something with this > If IsNumeric(Wks.Name) Then > > Thank u for help > Regards > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
