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

Please mail your workbook mailto:[email protected]

Cheers,
--
AP

"Jonah" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

What is the error message, and which line is yellow-highlighted?

--
AP

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

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" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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
>

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)

+ 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> a écrit dans le message de
> news:[email protected]...
> > 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
> >
>
>

What is the value of Role when you get the error?

--
Regards,
Tom Ogilvy



"Jonah" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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)

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

If Role is empty, it would work.

I suspect Role is numeric

--
Regards,
Tom Ogilvy

"Jonah" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> >