Sub or Function not Defined

D

Drimacus

New Member
Joined
Dec 12, 2013
Messages
24
Hello Everyone,

This is my first post hope it will fit in with the guidelines... I am getting this "Sub or Function not Defined" Compile time error. I don't understand, I give up. What realy bothers me here is that the below macro seems to work fine on my home computer with Excel 2013 but at work I get the Compile time error message.

Code: 
Sub runMacro()
        Dim first As Integer, last As Integer, counter As Integer, firstcol As Integer, lastcol As Integer
        Dim user As String
        Dim i As Integer
            For i = 2 To Cells(Rows.counter, "A").End(xlUp).Row
            user = ThisWorkbook.Sheets("Sheet4").Range("A" & i).Value
        
            Call searching(user, first, last, counter, firstcol, lastcol)
        
        ThisWorkbook.Sheets("Sheet4").Range("B" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("D" & first).Value
        ThisWorkbook.Sheets("Sheet4").Range("C" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("A" & first).Value
        If firstcol = 2 Then
            ThisWorkbook.Sheets("Sheet4").Range("D" & i).Value = "SentByUser"
        End If
        
        If firstcol = 4 Then
            ThisWorkbook.Sheets("Sheet4").Range("D" & i).Value = "ReceivedFromUser"
        End If
        ThisWorkbook.Sheets("Sheet4").Range("E" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("D" & last).Value
        ThisWorkbook.Sheets("Sheet4").Range("F" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("A" & last).Value
        If lastcol = 3 Then
            ThisWorkbook.Sheets("Sheet4").Range("G" & i).Value = "SentByUser"
        End If
        If lastcol = 5 Then
            ThisWorkbook.Sheets("Sheet4").Range("G" & i).Value = "ReceivedFromUser"
        End If
        ThisWorkbook.Sheets("Sheet4").Range("H" & i).Value = counter
        Next
End Sub

Private Sub searching(user As String, first As Integer, last As Integer, counter As Integer, firstcol As Integer, lastcol As Integer)
  counter = 1
    searching = True
    Dim notfound As Boolean
    notfound = False
    ThisWorkbook.Sheets("Sheet2").Activate
    On Error GoTo errhandler
    Cells.Find(What:=user, After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        first = ActiveCell.Row
        firstcol = ActiveCell.Column
        Cells.FindPrevious(After:=ActiveCell).Activate
        last = ActiveCell.Row
        lastcol = ActiveCell.Column
        
        Cells.Find(What:=user, After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
       first = ActiveCell.Row
        
        Do Until ActiveCell.Row = last
            counter = counter + 1
            Cells.FindNext(After:=ActiveCell).Activate
        Loop
            
        If notfound = True Then
            first = 1
            last = 1
            counter = 0
            firstcol = 1
            lastcol = 1
        End If
       
errhandler:
        notfound = True
        Resume Next
End Sub


Anyone have an idea what could be wrong? This macro simply goes through a list on Sheet4 looking up the data on Sheet2.
 
Sort by date Sort by votes
In your sub searching you have a variable called searching (searching = true)

Change the name of the variablke searching so it isn't the same as the sub
 
Upvote 0
Hi Stiuart, Thanks a lot for having a look at my issue. I had this variable left because I tried to change the Sub into a Function and assign a true value... I have updated the code here after, but I still get the Compile time error...

Here is the upadated code : I think the error is on


Code: 
Sub runMacro()
Dim first As Integer, last As Integer, counter As Integer, firstcol As Integer, lastcol As Integer
Dim user As String
Dim i As Integer
Dim enRow As Integer
enRow = ThisWorkbook.Sheets("Sheet4").Cells(Rows.count, "A").End(xlUp).Row
For i = 2 To enRow
user = ThisWorkbook.Sheets("Sheet4").Range("A" & i).Value

searching user, first, last, counter, firstcol, lastcol

ThisWorkbook.Sheets("Sheet4").Range("B" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("D" & first).Value
ThisWorkbook.Sheets("Sheet4").Range("C" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("A" & first).Value

If firstcol = 2 Then
ThisWorkbook.Sheets("Sheet4").Range("D" & i).Value = "SentByUser"
End If

If firstcol = 4 Then
ThisWorkbook.Sheets("Sheet4").Range("D" & i).Value = "ReceivedFromUser"
End If

ThisWorkbook.Sheets("Sheet4").Range("E" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("D" & last).Value
ThisWorkbook.Sheets("Sheet4").Range("F" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("A" & last).Value

If lastcol = 3 Then
ThisWorkbook.Sheets("Sheet4").Range("G" & i).Value = "SentByUser"
End If

If lastcol = 5 Then
ThisWorkbook.Sheets("Sheet4").Range("G" & i).Value = "ReceivedFromUser"
End If

ThisWorkbook.Sheets("Sheet4").Range("H" & i).Value = counter

Next
End Sub
Private Sub searching(user As String, first As Integer, last As Integer, counter As Integer, firstcol As Integer, lastcol As Integer)
counter = 1
Dim notfound As Boolean
notfound = False
ThisWorkbook.Sheets("Sheet2").Activate
On Error GoTo errhandler
Cells.Find(What:=user, After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
first = ActiveCell.Row
firstcol = ActiveCell.Column
Cells.FindPrevious(After:=ActiveCell).Activate
last = ActiveCell.Row
lastcol = ActiveCell.Column
Cells.Find(What:=user, After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
first = ActiveCell.Row
Do Until ActiveCell.Row = last
counter = counter + 1
Cells.FindNext(After:=ActiveCell).Activate
Loop

If notfound = True Then
first = 1
last = 1
counter = 0
firstcol = 1
lastcol = 1
End If
errhandler:
notfound = True
Resume Next
End Sub
Sub manual()
Dim first As Integer, last As Integer, counter As Integer, firstcol As Integer, lastcol As Integer
Dim user As String
Dim i As Integer, enRow As Integer
enRow = ThisWorkbook.Sheets("Sheet4").Cells(Rows.counter, "A").End(xlUp).Row
For i = 2 To enRow
user = ThisWorkbook.Sheets("Sheet4").Range("A" & i).Value

''searching user, first, last, counter, firstcol, lastcol
counter = 1
Dim notfound As Boolean
notfound = False
ThisWorkbook.Sheets("Sheet2").Activate
On Error GoTo errhandler
Cells.Find(What:=user, After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
first = ActiveCell.Row
firstcol = ActiveCell.Column
Cells.FindPrevious(After:=ActiveCell).Activate
last = ActiveCell.Row
lastcol = ActiveCell.Column

Cells.Find(What:=user, After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
first = ActiveCell.Row

Do Until ActiveCell.Row = last
counter = counter + 1
Cells.FindNext(After:=ActiveCell).Activate
Loop

If notfound = True Then
first = 1
last = 1
counter = 0
firstcol = 1
lastcol = 1
End If


ThisWorkbook.Sheets("Sheet4").Range("B" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("D" & first).Value
ThisWorkbook.Sheets("Sheet4").Range("C" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("A" & first).Value
If firstcol = 2 Then
ThisWorkbook.Sheets("Sheet4").Range("D" & i).Value = "SentByUser"
End If

If firstcol = 4 Then
ThisWorkbook.Sheets("Sheet4").Range("D" & i).Value = "ReceivedFromUser"
End If
ThisWorkbook.Sheets("Sheet4").Range("E" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("D" & last).Value
ThisWorkbook.Sheets("Sheet4").Range("F" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("A" & last).Value
If lastcol = 3 Then
ThisWorkbook.Sheets("Sheet4").Range("G" & i).Value = "SentByUser"
End If
If lastcol = 5 Then
ThisWorkbook.Sheets("Sheet4").Range("G" & i).Value = "ReceivedFromUser"
End If
ThisWorkbook.Sheets("Sheet4").Range("H" & i).Value = counter
Next
errhandler:
notfound = True
Resume
End Sub

I think the erroe might be on line : ThisWorkbook.Sheets("Sheet4").Range("B" & i).Value = ThisWorkbook.Sheets("Sheet2").Range("D" & first).Value


this line does not want to indent properly in my editor
 
Upvote 0
I can copy/paste your code just fine. I can indent it as well.
Do you have other code above or below the code you posted, in the same module?
 
Upvote 0
Any update / feedback on ths please?
 
Upvote 0
wigi said:
Any update / feedback on ths please?
Click to expand...

Hi Wigi,

sorry for late reply. It I didn't have anything else in this project a part from the two functions.

It turned out that somehow the white space was interpreted as something else :confused: . I realized that by deleting the white space everywhere possible and then re-indenting the code as usual. The macro was then working.

It is very strange, i had copied and pasted the code from an email into the module.
 
Upvote 0
Glad it's solved. Thank you for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Compile error: Variable not defined
Replies
3
Views
350
lharr28
L
J
  • Question Question
Send bulk email attachment error
Replies
3
Views
288
JOHATUP
J
M
  • Question Question
Not running code
Replies
0
Views
242
MagicCarpet
M
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
700
MagicCarpet
M
sharky12345
  • Question Question
Error 1004 - Copy method of Worksheet class failed
Replies
1
Views
255
yoseeeph
Y

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top