-1

I'm trying to make my life at work a bit easier, but I can't get past the basic VBA issues.

After some research I got to this point:

Sub testowe()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim y As Variant
Dim sht As Worksheet

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Open("U:\ZBROJARNIA\_WSPOLNE\test.xlsx")

Set sht = wbk2.Worksheets("Sheet1")

y = wbk2.sht.Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext).Row

End Sub

To begin I need to find the last empty cell in "test.xlsx" to start there. All of this while having only another workbook active (I want to reduce the ammount on interaction as much as possible ie. opening the test.xlsx workbook by myself). But somehow it doesn't work.

Having an error of "Object not supporting this property or method". It's probably some basic reference mistake, but I've got no idea how to handle it.

Edit: After a suggestion below I changed the y = to

y = sht.Columns("A").Find("", sht.Cells(sht.Rows.Count, "A"), xlValues, xlWhole,, xlNext).Row

But I'm getting a "Subscript out of range" error.

Edit2:

Changed the sht reference to

Set sht = wbk2.Sheets(1)

And it works ! Thanks for the tips below.

Edit3: Okay, not everything works... Current code looks like this:

Sub testowe()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim y As Variant
Dim sht As Worksheet
Dim LA As Integer
Dim Z As Variant

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Open("U:\ZBROJARNIA\_WSPOLNE\Przeroby-podsumowanie.xlsx")

Set sht = wbk2.Sheets(1)

y = sht.Columns("A").Find("", sht.Cells(sht.Rows.Count, "A"), xlValues, xlWhole, , xlNext).Row
x = Application.Sheets.Count

 LA = 2

Do While LA < x

Z = wbk1.Sheets(LA).Cell("C10").Value

LA = LA + 1

Loop

End Sub

Getting an automation error - have no idea what it means.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Radosław Zakrzewski
  • 1
  • 1
  • 5

2 Answers2

0

In my opinion there is at least an error to the last code line:

Try this:

Sub testowe()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim y As Variant
Dim sht As Worksheet

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Open("U:\ZBROJARNIA\_WSPOLNE\test.xlsx")

Set sht = wbk2.Worksheets("Sheet1")

y = sht.Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext).Row

End Sub

because sht is already a wbk2.Worksheets("Sheet1")

UBEX
  • 122
  • 1
  • 13
0

The problem is that your cells object are not qualified correctly. They are still referring to the current workbook. Also you don't need wbk2.sht.

Is this what you are trying?

y = sht.Columns("A").Find("", sht.Cells(sht.Rows.Count, "A"), xlValues, xlWhole,, xlNext).Row

I have explained it Here as well on how to find the last row.

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Yes, my main purpose at the moment is to find the first empty row. To ilustrate the whole process: I open a "a.xls" workbook which consists of several worksheets. Next step is to copy the information from all worksheets except the first and last one (this one I've got covered). – Radosław Zakrzewski Aug 04 '15 at 12:26
  • Also did you see the link in my above post? – Siddharth Rout Aug 04 '15 at 12:48
  • I did not. I tested it with various other names that were in the workbook, but it was before I tried your reference above. Now, everything is running. Got another problem thou, Have a loop: Dim Z As Variant LA = 2 Do While LA < x Z = wbk1.Sheets(LA).Cell("C10").Value LA = LA + 1 Loop Can't get that Z value anyway ... Any ideas what can be wrong ? – Radosław Zakrzewski Aug 04 '15 at 13:05