|
無二の親友から以下のプログラムのどこに間違いがあるのか?
聞かれたのですが、よくわからないのです。
どなたか、助け舟を出して頂けないでしょうか?
最初にエラーが出るのは、
Application.Workbooks(bookname).Select
が黄色のラインがかかってしまうのですが…。
本来なら、本人が質問するのが筋だと思いますが、
日本人ではないので、代理として私がお願いしました。よろしくお願いします。
Sub Q4()
Dim mydata() As Variant
Dim bookname As String, newbookname As String, i As Integer
Dim j As Integer, a As Integer, z As Integer, y As Integer, NoSheets As Integer
bookname = InputBox("Please inter data workbook's name")
Application.Workbooks.Open (bookname)
NoSheets = Application.Worksheets.Count
'MsgBox NoSheets
Application.SheetsInNewWorkbook = 3
Application.Workbooks.Add
newbookname = InputBox("Please inter new workbook's name")
ActiveWorkbook.SaveAs newbookname
Application.Workbooks(bookname).Select
Dim DataArray() As Variant
Dim Counter As Integer
'Dim NoSheets As Integer
Dim RowNo As Integer
Dim ColNo As Integer
Dim Address As String
Dim StringNo As Integer
Dim NumNo As Integer, sum As Variant
'Dim a As Integer, z As Integer, y As Integer
RowNo = 1
ColNo = 1
Address = "R" & RowNo & "C" & ColNo
Application.Workbooks.Add
NoSheets = Application.Worksheets.Count
ReDim DataArray(1 To NoSheets)
For Counter = 1 To NoSheets
DataArray(Counter) = Application.Worksheets(Counter).Cells(RowNo, ColNo).Value
' You cannot use Range because it is confined to an A1 address style
If TypeName(DataArray(Counter)) = "String" Then
StringNo = StringNo + 1
End If
If IsNumeric(DataArray(Counter)) Then
NumNo = NumNo + 1
End If
' Code for sum in Summary sheet goes here
ReDim mydata(1 To 10, 1 To 10) As Variant
For z = 1 To 10
For y = 1 To 10
'For a = 1 To NoSheets
Sheets(Counter).Select
mydata(z, y) = Sheets(Counter).Cells(z, y).Value
sum = mydata(z, y) + Sheets(Counter).Cells(z, y).Value
'Next a
Next y
Next z
Application.Workbooks(newbookname).Selected
Cells(z, y) = sum
If NumNo = 0 Then Cells(z, y) = "No Data"
Next Counter
End Sub
|
|