1

Topic: Help to understand an error in the code (excel vba)

Macro even in process, but I can not understand, in what now a problem. In a dialog box I take files different an ex-fur-tree, and glancing over them by turns I search for the information in them and I add on sheet shTotal. (In the future still it would be desirable that the data from each new book rushed in sample shTotal which at me in the code is added if there will be some books. Yet I do not know as it to implement)
Produces an error "subscript out of range", help to understand with it.

Sub Smeta ()
Dim oFD As FileDialog
Dim sFolder $, Filename $, MyName $
Dim OutRow&, KolSmet&
Dim sh As Worksheet
Dim y, z, w As Range
Dim cr, cr2, cr3 As Integer
MyName = ThisWorkbook. Name
Set oFD = Application. FileDialog (msoFileDialogFilePicker)
With oFD
.Title = "Nazvanie"
.Filters. Add "Excel files", "*.xls *;*.xla *"
.InitialFileName = ThisWorkbook. Path AND "\"
If.Show = 0 Then Exit Sub
Application. ScreenUpdating = False
Application. DisplayAlerts = False
Application. StatusBar = "Status......."
For cr = 1 To oFD.SelectedItems. Count
If cr <oFD.SelectedItems. Count Then Sheets (Sheets. Count).Copy after: = Sheets (Sheets. Count)
sFolder =.SelectedItems (cr)
Filename = Dir (sFolder, vbNormal)
' Workbooks ("Filename") whether.Activate--------- it is necessary generally here?
For cr2 = 1 To Workbooks ("Filename").Worksheets (Sheets. Count)----------- Complains of this place and on similar further
Set sh = Workbooks (Filename).Worksheets (cr2)
Set y = sh. UsedRange. Find ("* (nevazhnochto)", LookIn: = xlValues, LookAt: = xlWhole, MatchCase: = False, SearchOrder: = xlByColumns)
shTotal. Range ("R2") = y. Offset (2, 0)
shTotal. Cells (4, 2).EntireRow. HorizontalAlignment = xlCenter
Set z = sh. UsedRange. Find ("* (nevazhnochto) *", LookIn: = xlValues, LookAt: = xlWhole, MatchCase: = False, SearchOrder: = xlByColumns)
shTotal. Cells (4, 3) = z. Offset (-1, 0)
Set w = sh. UsedRange. Find ("* (nevazhnochto) *", LookIn: = xlValues, LookAt: = xlWhole, MatchCase: = False, SearchOrder: = xlByColumns)
shTotal. Range ("R1") = w. Offset (-1, 0)
Next cr2
Workbooks (Filename).Close
KolSmet = KolSmet + 1
Filename = Dir ()
Next
End With
Application. ScreenUpdating = True
Application. StatusBar = False
MsgBox "Smet:" AND KolSmet
Application. DisplayAlerts = True
End Sub

2

Re: Help to understand an error in the code (excel vba)

pomogitepls wrote:

Workbooks ("Filename").Worksheets...

pomogitepls wrote:

Workbooks (Filename).Close...

find ten differences. Well or at least one))

3

Re: Help to understand an error in the code (excel vba)

And still I did not find, actually, book discovery

4

Re: Help to understand an error in the code (excel vba)

pomogitepls wrote:

Filename = Dir (sFolder, vbNormal)

here still a jamb - each time will be used the same file.
In general, jambs, probably, full, laziness to do the full analysis of the code

5

Re: Help to understand an error in the code (excel vba)

Shocker. Pro;
I use after all Filename = Dir () in the end of a cycle that take the following file.

6

Re: Help to understand an error in the code (excel vba)

Shocker. Pro;
And what for to open the book if all becomes in a background

7

Re: Help to understand an error in the code (excel vba)

UPD
Understood with some moments. Whether prompt, please, in that place and whether correctly I close opened books.
And still, as in the given code to make so that the data from the new book was transferred at first on sample sheet of the initial book shTotal (it goes at number 2, on 1 sheet there are buttons), and then on its copy, each book to new sheet.

Sub Smeta ()
Dim oFD As FileDialog
Dim sFolder $, Filename $, MyName $
Dim OutRow&, KolSmet&
Dim sh As Worksheet
Dim y, z, w As Range
Dim cr, cr2, cr3 As Integer
MyName = ThisWorkbook. Name
Set oFD = Application. FileDialog (msoFileDialogFilePicker)
With oFD
.Title = "Select estimates"
.Filters. Add "Excel files", "*.xls *;*.xla *"
.InitialFileName = ThisWorkbook. Path AND "\"
If.Show = 0 Then Exit Sub
Application. ScreenUpdating = False
Application. DisplayAlerts = False
Application. StatusBar = "expect. There is a process......."
For cr = 1 To oFD.SelectedItems. Count Step 1
If cr <oFD.SelectedItems. Count Then Sheets (Sheets. Count).Copy after: = Sheets (Sheets. Count)
With shTotal
sFolder = oFD.SelectedItems (cr)
Filename = Dir (sFolder, vbNormal)
FileData = sFolder
Workbooks. Open FileData
Workbooks (Filename).Activate
For Each sh In Workbooks (Filename).Worksheets
Set y = sh. UsedRange. Find ("* (the building and-or object name)", LookIn: = xlValues, LookAt: = xlWhole, MatchCase: = False, SearchOrder: = xlByColumns)
Set z = sh. UsedRange. Find ("* (the name of operations and expenses) *", LookIn: = xlValues, LookAt: = xlWhole, MatchCase: = False, SearchOrder: = xlByColumns)
.Cells (2, 1).Value = y. Offset (2, 0) AND "" AND z. Offset (-1, 0)
Set w = sh. UsedRange. Find ("* (the building and-or object name) *", LookIn: = xlValues, LookAt: = xlWhole, MatchCase: = False, SearchOrder: = xlByColumns)
.Cells (1, 1) = w. Offset (-1, 0)
Next sh
Workbooks (Filename).Close
End With
KolSmet = KolSmet + 1
Next cr
End With
Application. ScreenUpdating = True
Application. StatusBar = False
MsgBox "the Total table is generated. Kol-in the processed files with estimates:" and KolSmet
Application. DisplayAlerts = True
End Sub

8

Re: Help to understand an error in the code (excel vba)

Here examples of files (the main and two files (their infinite amount as a result can be), will undertake whence the information):
https://www.dropbox.com/sh/386bymwrs543 … UInja?dl=0
(Saved on , in the attached files is not located a little)

9

Re: Help to understand an error in the code (excel vba)

pomogitepls wrote:

I Use after all Filename = Dir () in the end of a cycle that take the following file.

and in the cycle beginning again Filename = Dir (sFolder, vbNormal) that overwrites Filename = Dir (). However, in new code Dir is not present generally.
Whether

pomogitepls wrote:

in that place and whether correctly I close opened books.

well now like yes. Activate it is not necessary.

pomogitepls wrote:

Dim y, z, w As Range
Dim cr, cr2, cr3 As Integer

declarations wrong, esteem as to use operator Dim

pomogitepls wrote:

And still as in given code to make so that the data of the new book were transferred at first on sample sheet of the initial book shTotal (it goes at number 2, on 1 sheet are buttons), and then on its copy, each book to new sheet.

That means "at first" and "then"?