Sub or Function not defined

[jfabro]

Hello again.
I am running into a compile error saying “Sub or Function Not Defined” for the following:
LastRowInOneColumn
Could you please help me out!
Here is my code:

Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).Select
LastRow = LastRowInOneColumn(STOCK#)
Cells(LastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop


Thank you again for your assistance.

JFabro

 

[Norie]

Do you have a sub/function named LastRowInOneColumn?

 

[jfabro]

Here is the complete code:
Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As String
strListSheet = "LIST"
On Error GoTo ErrH
Sheets(strListSheet).Select
Range("B2").Select
'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> ""
strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).Select
LastRow = LastRowInOneColumn("STOCK#")
Cells(LastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub
ErrH:
MsgBox "It seems some file was missing. The data copy operation is not complete."
Exit Sub
End Sub

It does look like there may be a missing Sub but where would i insert it?

 

[Rick Rothstein]

It does look like there may be a missing Sub but where would i insert it?

You are missing a Function, not a Sub. But even if you supply the function, you will have another problem with that line of code... you do not have any lines of code assigning a value to STOCK# which is to be passed into the function. Do you have or know the column letter or column number (either would work) that STOCK# should be assigned?

 

[jfabro]

Wow, yes the column would be "B" or 2. and the Function?

 

[Rick Rothstein]

Wow, yes the column would be "B" or 2. and the Function?

Actually, I do not think you need a function, rather, I think you can do it inline with normal VB code. You are looking for the last row with data in Column B, correct? Try replacing the line of code that is causing the error with this one..

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

Note: I have not looked at the rest of your code for correctness of operation... I simply gave you an inline method to calculate the late data row in a given column.

 

[jfabro]

Thank you, that worked. Now if I can just get the files to open correctly I will be done.

 

[Norie]

What do you mean 'correctly?

Are you having problems with opening the files, or how the code is referring to the files you are opening?

 

[jfabro]

I was having issues getting the file to open at all. I have managed to fix the issue and the Macro works.
Thank you for all your assistance.