Method 'Rows' Of Object '_Global' Failed ???

[wmbb]

Running the macro below for the first time there is no problem but running the macro the second time I get an error "Method 'Rows' Of Object '_Global' Failed" in the function "lastrow" ...
Can anyone explain why this happens the second time ?

Sub test()
read_NCdata "4022.635.94672"
End Sub

rest of the code:

Sub read_NCdata(NC As String)
'Declare the Excel workbook and application objects
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim NCrow, i, LR, LC As Integer
Const WBname As String = "T:\Admin\TOC\12NCdatabase.xlsx"
Const WSinfo As String = "12NC Info"
Const WSautotext As String = "Autotext Info"
Set xlApp = New Excel.Application
xlApp.Visible = True       'excel visible yes or no
Set xlWB = xlApp.Workbooks.Open(WBname)
With xlWB.Sheets(WSinfo)
    LR = lastrow(1)
    NCrow = WorksheetFunction.Match(NC, xlWB.Sheets(WSinfo).Range("A1:A" & LR), 0)
    LC = lastcolumn(NCrow)
End With
ReDim NCdata(LC)
For i = 1 To LC
    NCdata(i) = xlWB.Sheets(WSinfo).Cells(NCrow, i).Value
    Debug.Print NCdata(i)
Next i
xlWB.Close
xlApp.Quit
Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing
End Sub

Function lastrow(Clm As Integer) As Long
    lastrow = Cells(Rows.count, Clm).End(xlUp).Offset(Abs(Cells(Rows.count, Clm).End(xlUp).Value <> ""), 0).Row - 1
End Function

Function lastcolumn(Rw) As Long
    lastcolumn = Cells(Rw, Columns.count).End(xlToLeft).Offset(Abs(Cells(Rw, Columns.count).End(xlToLeft).Value <> ""), 0).Column
End Function

 

[combo]

You should never reference outside worksheet. If you apply row Offset to cell (1,1) equal to Rows.Count, you may have such case.

The block [tt]With xlWB.Sheets(WSinfo)...End With[/tt] is useless, you nowhere inside continue with "."

The code would be more solid if you more precisely reference ranges, for instance declare worksheet:

Set xlWS = xlWB.Sheets(WSinfo)
LR = lastrow(xlWS,1)

and

Function lastrow(xlWS as Excel.Worksheet, Clm As Integer) As Long
With xlWS    
    lastrow = .Cells(.Rows.count, Clm).End(xlUp).Offset(Abs(.Cells(.Rows.count, Clm).End(xlUp).Value <> ""), 0).Row - 1
End With
End Function

combo

 

[Andrzejek]

You declared xlWB As Excel.Workbook in Sub read_NCdata, but your functions lastrow and lastcolumn do not know anything about xlWB, it is out of scope. Why it runs first time? Who knows? It shouldn't.


Also, just a small point:
in[tt]
Dim NCrow, i, LR, LC As Integer[/tt]
only LC is an Integer, all other variables (NCrow, i, LR) are Variants :-(

The same goes for:[tt]
Function lastcolumn(Rw) As Long[/tt]
Rw is a Variant

 

[wmbb]

Thank you for this info.
I'll try to implement your suggestions and see if it works.
I'm learning every day and this forum is the best option to get help !

 

[wmbb]

I still need help....

I changed the code to

Sub read_NCdata(NC As String)
'Declare the Excel workbook and application objects
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim NCrow As Integer
Dim i As Integer
Dim LR As Integer
Dim LC As Integer
Const WBname As String = "T:\Admin\TOC\12NCdatabase.xlsx"
Const WSinfo As String = "12NC Info"
Const WSautotext As String = "Autotext Info"

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(WBname)
Set xlWS = xlWB.Sheets(WSinfo)

xlApp.Visible = True       'excel visible yes or no
    
LR = lastrow(xlWS, 1)
NCrow = WorksheetFunction.Match(NC, xlWB.Sheets(WSinfo).Range("A1:A" & LR), 0)
LC = lastcolumn(xlWS, NCrow)

ReDim NCdata(LC)
For i = 1 To LC
    NCdata(i) = xlWB.Sheets(WSinfo).Cells(NCrow, i).Value
    Debug.Print NCdata(i)
Next i

xlWB.Close
xlApp.Quit
Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing

End Sub

Function lastrow(xlWS As Excel.Worksheet, Clm As Integer) As Long
With xlWS
    lastrow = .Cells(Rows.count, Clm).End(xlUp).Offset(Abs(Cells(Rows.count, Clm).End(xlUp).Value <> ""), 0).Row - 1
End With
End Function

Function lastcolumn(xlWS As Excel.Worksheet, Rw As Integer) As Long
With xlWS
    lastcolumn = .Cells(Rw, Columns.count).End(xlToLeft).Offset(Abs(Cells(Rw, Columns.count).End(xlToLeft).Value <> ""), 0).Column
End With
End Function

But it doesn't work the second time...???

 

[Andrzejek]

Since both of your functions are one-liners and both are called just once (as far as I can see), why not plug the code where it needs to be (eliminate functions):

...
xlApp.Visible = True       'excel visible yes or no
    
LR = xlWS.Cells(Rows.count, 1).End(xlUp).Offset(Abs(Cells(Rows.count, 1).End(xlUp).Value <> ""), 0).Row - 1
...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Here's a problem:

You are using With xlWS...
...BUT you are not consistent referencing (. Reference) this worksheet with each and every child OBJECT.

I see ROWS, COLUMNS, CELLS that are not referenced to this worksheet object variable!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Apart of missing dots check input and output values in lastrow and lastcolumn functions.
For empty row/column those functions returh 0, that will cause the error in line:
[tt]NCrow = WorksheetFunction.Match(NC, xlWB.Sheets(WSinfo).Range("A1:A" & LR), 0)
[/tt]
You could add some watches in sensitive parts of code and observe values when executing code line by line.

combo

 

[wmbb]

The error occurs the second time I run the code [ read_NCdata "4022.635.94672" ]
The error occurs in the function lastrow !

End Sub

Okay, this is what I'm trying to do...
There is a excel-sheet with info of product codes.
From WORD I try to read this info and put it into an array NCdata().
I'm using functions for finding the lastrow and lastcolumn because I want to use this for more purposes later in the macro again.

What I'm trying to do is read data from the excel-sheet by lookup the chosen value of the product code (12NC number) in column A and read the variable amount of info stored in the followed columns B to ......
Subsequently this info is stored in the array NCdata().

So I'm using the function lastrow to find the amount of productcodes to lookup the selected product code.
I'm using the function lastcolumn to find the amount of product info items.

I have attached the test file 12NCdatabase.xlsx to give you an idea of the database file we're going to build.

 

[wmbb]

I think it works now...
It was a matter of dots !

I changed the functions to the code below and it seems to work...

Function lastrow(xlWS As Excel.Worksheet, Clm As Integer) As Long
With xlWS
    lastrow = .Cells(.Rows.count, Clm).End(xlUp).Offset(Abs(.Cells(.Rows.count, Clm).End(xlUp).Value <> ""), 0).Row - 1
End With
End Function

Function lastcolumn(xlWS As Excel.Worksheet, Rw As Integer) As Long
With xlWS
    lastcolumn = .Cells(Rw, .Columns.count).End(xlToLeft).Offset(Abs(.Cells(Rw, .Columns.count).End(xlToLeft).Value <> ""), 0).Column
End With
End Function

Thanks all for your support.

 

[SkipVought]

Dots wut I was talkin' 'bout!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]