| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Script fails in Excel was okay before.
I have a script that no longer runs in Excel 2003 under XP Pro. It was
working and now it tells me there is an error. I can see no error but Excel is determined. Any ideas? I tried before to send an Excel file but the group does not appear to accept .xls files. How can I post? Jonah |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
Please mail your workbook
Cheers, -- AP "Jonah" a écrit dans le message de ... I have a script that no longer runs in Excel 2003 under XP Pro. It was working and now it tells me there is an error. I can see no error but Excel is determined. Any ideas? I tried before to send an Excel file but the group does not appear to accept .xls files. How can I post? Jonah |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
Sub Button2_Click()
'Start at Row 3 RowIndex = 5 'Set up variables from Master List Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) 'We want to loop down list until we find a blank 'We need both bits of data to be there Do While (OurDate < "") And (Group < "") OurDate = Replace(OurDate, "/", "-") 'Combine the two variables, to get a full name FullName = OurDate & " " & Group 'Copy the Sheet and name it as the Date&Group Sheets("Lesson Template").Select Sheets("Lesson Template").Copy After:=Sheets(2) Sheets("Lesson Template (2)").Name = FullName 'Put the Data into the sheet at appropriate points 'This is done on a co-ordinate basis ' (1,1) means cell A1 I guess and (1,2) means B1 etc ' (2,4) means cell D2 Sheets(FullName).Cells(1, 1) = "Teacher: " + Forename + " " + Surname Sheets(FullName).Cells(1, 2) = "Group: " + Group Sheets(FullName).Cells(1, 3) = " Roll: " + Role Sheets(FullName).Cells(1, 4) = "Gender: M: " + GenderM Sheets(FullName).Cells(2, 4) = " F: " + GenderF Sheets(FullName).Cells(1, 5) = "Period: " + Period Sheets(FullName).Cells(1, 6) = "Date: " + OurDate Sheets(FullName).Cells(2, 5) = "Lesson: " + TLesson Sheets(FullName).Cells(33, 2) = "ScoreB: " + ScoreB Sheets(FullName).Cells(34, 2) = "ScoreC: " + ScoreC Sheets(FullName).Cells(3, 1) = "By the end of this lesson all students will: " + BTE Sheets(FullName).Cells(3, 2) = "Most students will: " + MSW Sheets(FullName).Cells(3, 3) = " " + SSW Sheets(FullName).Cells(5, 1) = "Key skills developed: " + KeySkills Sheets(FullName).Cells(5, 2) = "Keywords: " + Keywords 'Move to next row and get new data RowIndex = RowIndex - 1 Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) Loop End Sub Jonah wrote: I have a script that no longer runs in Excel 2003 under XP Pro. It was working and now it tells me there is an error. I can see no error but Excel is determined. Any ideas? I tried before to send an Excel file but the group does not appear to accept .xls files. How can I post? Jonah |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
You start at RowIndex = 5 and then decrease that by one on each loop. If
you have data all the way to row 1, then you will try to gather data with RowIndex = 0 which will raise an error. either change RowIndex = RowIndex - 1 to RowIndex = RowIndex - 1 if RowIndex < 1 then exit sub or more like like add 1 to RowIndex RowIndex = RowIndex + 1 -- Regards, Tom Ogilvy "Jonah" wrote in message ... Sub Button2_Click() 'Start at Row 3 RowIndex = 5 'Set up variables from Master List Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) 'We want to loop down list until we find a blank 'We need both bits of data to be there Do While (OurDate < "") And (Group < "") OurDate = Replace(OurDate, "/", "-") 'Combine the two variables, to get a full name FullName = OurDate & " " & Group 'Copy the Sheet and name it as the Date&Group Sheets("Lesson Template").Select Sheets("Lesson Template").Copy After:=Sheets(2) Sheets("Lesson Template (2)").Name = FullName 'Put the Data into the sheet at appropriate points 'This is done on a co-ordinate basis ' (1,1) means cell A1 I guess and (1,2) means B1 etc ' (2,4) means cell D2 Sheets(FullName).Cells(1, 1) = "Teacher: " + Forename + " " + Surname Sheets(FullName).Cells(1, 2) = "Group: " + Group Sheets(FullName).Cells(1, 3) = " Roll: " + Role Sheets(FullName).Cells(1, 4) = "Gender: M: " + GenderM Sheets(FullName).Cells(2, 4) = " F: " + GenderF Sheets(FullName).Cells(1, 5) = "Period: " + Period Sheets(FullName).Cells(1, 6) = "Date: " + OurDate Sheets(FullName).Cells(2, 5) = "Lesson: " + TLesson Sheets(FullName).Cells(33, 2) = "ScoreB: " + ScoreB Sheets(FullName).Cells(34, 2) = "ScoreC: " + ScoreC Sheets(FullName).Cells(3, 1) = "By the end of this lesson all students will: " + BTE Sheets(FullName).Cells(3, 2) = "Most students will: " + MSW Sheets(FullName).Cells(3, 3) = " " + SSW Sheets(FullName).Cells(5, 1) = "Key skills developed: " + KeySkills Sheets(FullName).Cells(5, 2) = "Keywords: " + Keywords 'Move to next row and get new data RowIndex = RowIndex - 1 Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) Loop End Sub Jonah wrote: I have a script that no longer runs in Excel 2003 under XP Pro. It was working and now it tells me there is an error. I can see no error but Excel is determined. Any ideas? I tried before to send an Excel file but the group does not appear to accept .xls files. How can I post? Jonah |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
Jonah,
My guess is that this line is failing because there is no sheet by that name... Sheets("Lesson Template (2)").Name = FullName Excel may be naming it using a higher number. You could work around that with... Set shtCopy = Sheets("Lesson Template").Copy(After:=Sheets(2)) shtCopy.Name = FullName I strongly recommend that you enter "Option Explicit" as the first line of your module and then declare all of your variables... Dim RowIndex as Long Dim shtCopy as Excel.Worksheet 'more of the same Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
What is the error message, and which line is yellow-highlighted?
-- AP |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
Runtime error message 13
Type mismatch. Sheets(FullName).Cells(1, 3) = "Roll: " + Role Jonah ----------------------- Ardus Petus wrote: What is the error message, and which line is yellow-highlighted? -- AP |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
You used "+" instead of "&" (this is VBA, not Javascript)
This is the amended version HTH, -- AP '---------------------------------------------- Sub Button2_Click() 'Start at Row 3 RowIndex = 5 'Set up variables from Master List Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) ourdate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) 'We want to loop down list until we find a blank 'We need both bits of data to be there Do While (ourdate < "") And (Group < "") ourdate = Replace(ourdate, "/", "-") 'Combine the two variables, to get a full name FullName = ourdate & " " & Group 'Copy the Sheet and name it as the Date&Group Sheets("Lesson Template").Select Sheets("Lesson Template").Copy After:=Sheets(2) Sheets("Lesson Template (2)").Name = FullName 'Put the Data into the sheet at appropriate points 'This is done on a co-ordinate basis ' (1,1) means cell A1 I guess and (1,2) means B1 etc ' (2,4) means cell D2 Sheets(FullName).Cells(1, 1) = "Teacher: " & _ Forename & " " & Surname Sheets(FullName).Cells(1, 2) = "Group: " & Group Sheets(FullName).Cells(1, 3) = " Roll: " & Role Sheets(FullName).Cells(1, 4) = "Gender: M: " & GenderM Sheets(FullName).Cells(2, 4) = " F: " & GenderF Sheets(FullName).Cells(1, 5) = "Period: " & Period Sheets(FullName).Cells(1, 6) = "Date: " & ourdate Sheets(FullName).Cells(2, 5) = "Lesson: " & TLesson Sheets(FullName).Cells(33, 2) = "ScoreB: " & ScoreB Sheets(FullName).Cells(34, 2) = "ScoreC: " & ScoreC Sheets(FullName).Cells(3, 1) = _ "By the end of this lesson all students will: " & BTE Sheets(FullName).Cells(3, 2) = "Most students will: " & MSW Sheets(FullName).Cells(3, 3) = " " & SSW Sheets(FullName).Cells(5, 1) = "Key skills developed: " & _ KeySkills Sheets(FullName).Cells(5, 2) = "Keywords: " & Keywords 'Move to next row and get new data RowIndex = RowIndex - 1 Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) ourdate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) Loop End Sub '------------------------------------- "Jonah" a écrit dans le message de ... Sub Button2_Click() 'Start at Row 3 RowIndex = 5 'Set up variables from Master List Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) 'We want to loop down list until we find a blank 'We need both bits of data to be there Do While (OurDate < "") And (Group < "") OurDate = Replace(OurDate, "/", "-") 'Combine the two variables, to get a full name FullName = OurDate & " " & Group 'Copy the Sheet and name it as the Date&Group Sheets("Lesson Template").Select Sheets("Lesson Template").Copy After:=Sheets(2) Sheets("Lesson Template (2)").Name = FullName 'Put the Data into the sheet at appropriate points 'This is done on a co-ordinate basis ' (1,1) means cell A1 I guess and (1,2) means B1 etc ' (2,4) means cell D2 Sheets(FullName).Cells(1, 1) = "Teacher: " + Forename + " " + Surname Sheets(FullName).Cells(1, 2) = "Group: " + Group Sheets(FullName).Cells(1, 3) = " Roll: " + Role Sheets(FullName).Cells(1, 4) = "Gender: M: " + GenderM Sheets(FullName).Cells(2, 4) = " F: " + GenderF Sheets(FullName).Cells(1, 5) = "Period: " + Period Sheets(FullName).Cells(1, 6) = "Date: " + OurDate Sheets(FullName).Cells(2, 5) = "Lesson: " + TLesson Sheets(FullName).Cells(33, 2) = "ScoreB: " + ScoreB Sheets(FullName).Cells(34, 2) = "ScoreC: " + ScoreC Sheets(FullName).Cells(3, 1) = "By the end of this lesson all students will: " + BTE Sheets(FullName).Cells(3, 2) = "Most students will: " + MSW Sheets(FullName).Cells(3, 3) = " " + SSW Sheets(FullName).Cells(5, 1) = "Key skills developed: " + KeySkills Sheets(FullName).Cells(5, 2) = "Keywords: " + Keywords 'Move to next row and get new data RowIndex = RowIndex - 1 Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) Loop End Sub Jonah wrote: I have a script that no longer runs in Excel 2003 under XP Pro. It was working and now it tells me there is an error. I can see no error but Excel is determined. Any ideas? I tried before to send an Excel file but the group does not appear to accept .xls files. How can I post? Jonah |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
Brilliant Ardus. Thankyou. As a newbie I am relieved, but how come it worked
earlier? Jonah -------------. Ardus Petus wrote: You used "+" instead of "&" (this is VBA, not Javascript) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
+ is a valid concatenation operator when used with strings:
From the immediate Window: role = "Manager" ? "Roll: " + Role Roll: Manage Although I agree that & is a better choice. -- Regards, Tom Ogilvy "Ardus Petus" wrote in message ... You used "+" instead of "&" (this is VBA, not Javascript) This is the amended version HTH, -- AP '---------------------------------------------- Sub Button2_Click() 'Start at Row 3 RowIndex = 5 'Set up variables from Master List Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) ourdate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) 'We want to loop down list until we find a blank 'We need both bits of data to be there Do While (ourdate < "") And (Group < "") ourdate = Replace(ourdate, "/", "-") 'Combine the two variables, to get a full name FullName = ourdate & " " & Group 'Copy the Sheet and name it as the Date&Group Sheets("Lesson Template").Select Sheets("Lesson Template").Copy After:=Sheets(2) Sheets("Lesson Template (2)").Name = FullName 'Put the Data into the sheet at appropriate points 'This is done on a co-ordinate basis ' (1,1) means cell A1 I guess and (1,2) means B1 etc ' (2,4) means cell D2 Sheets(FullName).Cells(1, 1) = "Teacher: " & _ Forename & " " & Surname Sheets(FullName).Cells(1, 2) = "Group: " & Group Sheets(FullName).Cells(1, 3) = " Roll: " & Role Sheets(FullName).Cells(1, 4) = "Gender: M: " & GenderM Sheets(FullName).Cells(2, 4) = " F: " & GenderF Sheets(FullName).Cells(1, 5) = "Period: " & Period Sheets(FullName).Cells(1, 6) = "Date: " & ourdate Sheets(FullName).Cells(2, 5) = "Lesson: " & TLesson Sheets(FullName).Cells(33, 2) = "ScoreB: " & ScoreB Sheets(FullName).Cells(34, 2) = "ScoreC: " & ScoreC Sheets(FullName).Cells(3, 1) = _ "By the end of this lesson all students will: " & BTE Sheets(FullName).Cells(3, 2) = "Most students will: " & MSW Sheets(FullName).Cells(3, 3) = " " & SSW Sheets(FullName).Cells(5, 1) = "Key skills developed: " & _ KeySkills Sheets(FullName).Cells(5, 2) = "Keywords: " & Keywords 'Move to next row and get new data RowIndex = RowIndex - 1 Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) ourdate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) Loop End Sub '------------------------------------- "Jonah" a écrit dans le message de ... Sub Button2_Click() 'Start at Row 3 RowIndex = 5 'Set up variables from Master List Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) 'We want to loop down list until we find a blank 'We need both bits of data to be there Do While (OurDate < "") And (Group < "") OurDate = Replace(OurDate, "/", "-") 'Combine the two variables, to get a full name FullName = OurDate & " " & Group 'Copy the Sheet and name it as the Date&Group Sheets("Lesson Template").Select Sheets("Lesson Template").Copy After:=Sheets(2) Sheets("Lesson Template (2)").Name = FullName 'Put the Data into the sheet at appropriate points 'This is done on a co-ordinate basis ' (1,1) means cell A1 I guess and (1,2) means B1 etc ' (2,4) means cell D2 Sheets(FullName).Cells(1, 1) = "Teacher: " + Forename + " " + Surname Sheets(FullName).Cells(1, 2) = "Group: " + Group Sheets(FullName).Cells(1, 3) = " Roll: " + Role Sheets(FullName).Cells(1, 4) = "Gender: M: " + GenderM Sheets(FullName).Cells(2, 4) = " F: " + GenderF Sheets(FullName).Cells(1, 5) = "Period: " + Period Sheets(FullName).Cells(1, 6) = "Date: " + OurDate Sheets(FullName).Cells(2, 5) = "Lesson: " + TLesson Sheets(FullName).Cells(33, 2) = "ScoreB: " + ScoreB Sheets(FullName).Cells(34, 2) = "ScoreC: " + ScoreC Sheets(FullName).Cells(3, 1) = "By the end of this lesson all students will: " + BTE Sheets(FullName).Cells(3, 2) = "Most students will: " + MSW Sheets(FullName).Cells(3, 3) = " " + SSW Sheets(FullName).Cells(5, 1) = "Key skills developed: " + KeySkills Sheets(FullName).Cells(5, 2) = "Keywords: " + Keywords 'Move to next row and get new data RowIndex = RowIndex - 1 Forename = Worksheets("Master List").Cells(RowIndex, 1) Surname = Worksheets("Master List").Cells(RowIndex, 2) Group = Worksheets("Master List").Cells(RowIndex, 3) Role = Worksheets("Master List").Cells(RowIndex, 4) GenderM = Worksheets("Master List").Cells(RowIndex, 5) GenderF = Worksheets("Master List").Cells(RowIndex, 6) Period = Worksheets("Master List").Cells(RowIndex, 7) OurDate = Worksheets("Master List").Cells(RowIndex, 8) TLesson = Worksheets("Master List").Cells(RowIndex, 9) ScoreB = Worksheets("Master List").Cells(RowIndex, 10) ScoreC = Worksheets("Master List").Cells(RowIndex, 11) BTE = Worksheets("Master List").Cells(RowIndex, 12) MSW = Worksheets("Master List").Cells(RowIndex, 13) SSW = Worksheets("Master List").Cells(RowIndex, 14) KeySkills = Worksheets("Master List").Cells(RowIndex, 15) Keywords = Worksheets("Master List").Cells(RowIndex, 16) Loop End Sub Jonah wrote: I have a script that no longer runs in Excel 2003 under XP Pro. It was working and now it tells me there is an error. I can see no error but Excel is determined. Any ideas? I tried before to send an Excel file but the group does not appear to accept .xls files. How can I post? Jonah |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
What is the value of Role when you get the error?
-- Regards, Tom Ogilvy "Jonah" wrote in message ... Runtime error message 13 Type mismatch. Sheets(FullName).Cells(1, 3) = "Roll: " + Role Jonah ----------------------- Ardus Petus wrote: What is the error message, and which line is yellow-highlighted? -- AP |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
The script works beautifully with & but on my PC it does not work with the +
anymore. Thats Microsoft! Jonah ======== Tom Ogilvy wrote: + is a valid concatenation operator when used with strings: role = "Manager" ? "Roll: " + Role Roll: Manage Ardus Petus wrote:- You used "+" instead of "&" (this is VBA, not Javascript) |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
No value. Runtime error message 13.
Jonah --------------- Tom Ogilvy wrote: What is the value of Role when you get the error? -- Regards, Tom Ogilvy "Jonah" wrote in message ... Runtime error message 13 Type mismatch. Sheets(FullName).Cells(1, 3) = "Roll: " + Role Jonah ----------------------- Ardus Petus wrote: What is the error message, and which line is yellow-highlighted? -- AP |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
If Role is empty, it would work.
I suspect Role is numeric -- Regards, Tom Ogilvy "Jonah" wrote in message ... No value. Runtime error message 13. Jonah --------------- Tom Ogilvy wrote: What is the value of Role when you get the error? -- Regards, Tom Ogilvy "Jonah" wrote in message ... Runtime error message 13 Type mismatch. Sheets(FullName).Cells(1, 3) = "Roll: " + Role Jonah ----------------------- Ardus Petus wrote: What is the error message, and which line is yellow-highlighted? -- AP |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Script fails in Excel was okay before.
Yes. And many of my cells are error checking automatically for some reason,
and thus throwing up error messages telling me that the cells contain text. I set them as decimal numbers with no decimal places. Now I cannot enter a formula in many of the cells despite this. Very odd. Jonah ------ Tom Ogilvy wrote: If Role is empty, it would work. I suspect Role is numeric -- Regards, Tom Ogilvy "Jonah" wrote in message ... No value. Runtime error message 13. Jonah --------------- Tom Ogilvy wrote: What is the value of Role when you get the error? -- Regards, Tom Ogilvy "Jonah" wrote in message ... Runtime error message 13 Type mismatch. Sheets(FullName).Cells(1, 3) = "Roll: " + Role Jonah ----------------------- Ardus Petus wrote: What is the error message, and which line is yellow-highlighted? -- AP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel fails to save | Excel Discussion (Misc queries) | |||
| excel fails to launch | Excel Discussion (Misc queries) | |||
| Excel Fails when add-in is selected | Excel Worksheet Functions | |||
| Excel 2000/XP script to Excel97 script | Excel Programming | |||
| Solver Fails in Excel XP | Excel Programming | |||
Linear Mode
