CODEWITHSUNDEEP
vbams-access
Dvir Naim
Dvir Naim

Reputation: 149

access error "sub or function not defined"

I have an error in VBA access running. When I run this code

Option Compare Database

Function Ashray()

Dim last As String
Dim i As Integer
Dim column As Integer
Dim temp As String

column = 2
i = 1

While i > 0
temp = ws.Cells(i, column).Value
last = Right(temp, 4)
ws.Cells(i, 1).offset(, column).Value = last
i = i + 1
If ws.Cells(i, column).Value < 1 Then
i = 0
End If

Wend
End Function

I get the error Run-time Error '424': Object Required with the temp = ws.Cells(i, column).Value bold.

I will be happy for some help..

Thanks

Upvotes: 0

Views: 688

Answers (3)

Pierre Chevallier
Pierre Chevallier

Reputation: 754

I think you forgot to add an Microsoft Excel 15.0 Object Library reference in your project (Tools> References and then check the library I mentionned). Then try to instanciate an Excel Object as follow:

Option Compare Database

Function Ashray()

Dim last As String
Dim i As Integer
Dim column As Integer
Dim temp As String
Dim ws As Excel.Application
Dim wsht as ws.Workbook

Set wsht = Workbooks("yourWorkbook.xls").Sheets("yourSheet")
column = 2
i = 1

While i > 0
temp = wsht.Cells(i, column).Value
last = Right(temp, 4)
wsht.Cells(i, 1).offset(, column).Value = last
i = i + 1
If wsht.Cells(i, column).Value < 1 Then
i = 0
End If
Wend

End Function

You have to add the library to the Access project, else it will not find the Excel workbook you're trying to access.

Regards.

Upvotes: 0

Andre
Andre

Reputation: 27634

Cells is an Excel object, it doesn't exist in Access.

If you have a worksheet variable, use it as qualifier:

ws.Cells

Upvotes: 1

Jasbeer Singh
Jasbeer Singh

Reputation: 37

Please check your code 

While i
 temp = Cells(i, column).Value
 last = Right(temp, 4)
 Cells(i, 1).offset(, column).Value = last
 i = i + 1
 If Cells(i, column).Value < 1 Then
 i = 0
End If
**Wend**

This is not the Wend, this might be End While.

Regards Jasbeer Singh

Upvotes: 0

Related Questions