Getting compile error sub or function not define

Hi friends,
I’m getting compile error ‘sub or function not define’ in the attached workbook.

First I enter a register number in g7 LC sheet and then run the macro ‘LCRecord’.
I want to record the receipt number a8 and date-time b28 lc sheet on the register sheet in front of that particular register number which is entered in g7 lc sheet.

If it’s a first time then it should record in the column ‘AF’ in front of that particular register number entered in g7 lc sheet.

If column ‘ag’ is not blank then the second entry should be in column ‘Ag’ in front of that particular register number entered in g7 lc sheet.

If column ‘ah’ is not blank then the third entry should be in column ‘Ah’ in front of that particular register number entered in g7 lc sheet.

If column ‘ai’ is not blank then the fourth entry should be in column ‘Ai’ in front of that particular register number entered in g7 lc sheet.

If column ‘aj’ is not blank then the fifth entry should be in column ‘Aj’ in front of that particular register number entered in g7 lc sheet.


And so on endlessly as per the requirement.

In short the first entry should be in column ‘af’.
If there is a entry in column ‘af’ then the entries should be as:
Second on column ag
Third on column ah
Fourth on column ai
Fifth on column aj
And so on endlessly as per the requirement.

Any help will be highly appreciated.

Thanking you in anticipation.

Hi,

Maybe like this :

Sub LCRecord()

    Dim RegNum As String
    Dim str As String
    Dim rg As Range
        
    With Sheets("LC")
        RegNum = .Range("G7")
        str = .Range("A8") & vbNewLine & Format(.Range("B28"), "dd/mm/yyyy hh:mm:ss am/pm")
    End With
    
    With Sheets("Register")
        Set rg = .Range("A1:A1000").Find(RegNum)
        
        Set rg = .Range("AF" & rg.Row)
        Do Until rg.Value = ""
            Set rg = rg.Offset(0, 1)
        Loop
        rg = str
    End With
End Sub

Hi GC Excel,
Excellent!
It's working fine but only two revisions are require to complete it.

I want a remark in cell d7 lc sheet showing the number of duplicate lc.
For ex. first time in 'Duplicate1'; second time 'Duplicate 2'; third time 'Duplicate 3' and so on endlessly. This remark should be in red colour and bold font.
If a user enter a register number in g7 lc sheet which is not available in register sheet then a pop up message should show as 'The register number is not found!' and exit sub.

After this amendment the problem will be solved.

Thank you very much.

Ok, try this then :

Sub LCRecord()

    Dim RegNum As String
    Dim str As String
    Dim rg As Range
    Dim i As Integer
        
    With Sheets("LC")
        RegNum = .Range("G7")
        str = .Range("A8") & vbNewLine & Format(.Range("B28"), "dd/mm/yyyy hh:mm:ss am/pm")
    End With
    
    With Sheets("Register")
        On Error Resume Next
        Set rg = .Range("A1:A1000").Find(RegNum)
        
        If rg Is Nothing Then
            MsgBox "The register number is not found!"
            Exit Sub
        End If
        
        Set rg = .Range("AF" & rg.Row)
        i = 0
        Do Until rg.Value = ""
            Set rg = rg.Offset(0, 1)
            i = i + 1
        Loop
        rg = str
        
        Sheets("LC").Range("D7").Clear
        If i > 0 Then
            With Sheets("LC").Range("D7")
                .Font.Bold = True
                .Font.ColorIndex = 3
                .Value = "Duplicate " & i
            End With
        End If
    End With
    
End Sub

Hello GC Excel,

Excellent! Excellent! Excellent!

Absolutely perfect as per my requirement.

By heart I appreciate you for your kind cooperation to solve my problem.

Thank you.

All the best!

Hi gc excel,
Sorry! Today I do a little amendment in the code to show pop up message if a user run a macro if there is no register number in g7 lc sheet. I am getting compile error 'type mismatch' on the word 'RegNum'.

Please suggest me a correction.

Sub RecordLC()

    Dim RegNum As String
    Dim str As String
    Dim rg As Range
    Dim i As Integer
        
    With Sheets("LC")
    On Error Resume Next
    
        RegNum = .Range("G7")
        str = format(.Range("A8") """?. No.""#000000") & vbNewLine & Format(.Range("B28"), "dd/mm/yyyy hh:mm:ss am/pm") 'Here I'm getting syntax error
        
        If RegNum Is Nothing Then 'Here I'm getting error type mismatch on RegNum
            MsgBox "Please enter a valid register number!"
select g7
            Exit Sub
        End If
    End With
    
    With Sheets("Register")
        On Error Resume Next
        Set rg = .Range("A6:A50000").Find(RegNum)
        
        If rg Is Nothing Then
            MsgBox "The register number is not found!"
            Exit Sub
        End If
        
        Set rg = .Range("AF" & rg.Row)
        i = 0
        Do Until rg.Value = ""
            Set rg = rg.Offset(0, 1)
            i = i + 1
        Loop
        rg = str
        
        Sheets("LC").Range("C7").Clear
        If i > 0 Then
            With Sheets("LC").Range("C7")
            .Font.Size = 20
                .Font.Bold = True
                .Font.ColorIndex = 3
                .Value = "Duplicate " & i
            End With
        End If
    End With
    MsgBox "The LC is recorded!", 64
End Sub

Thank you and sorry for trouble.

Have a nice day!

Hi mso3,

Here your revised code :

Sub RecordLC()

    Dim RegNum As String
    Dim str As String
    Dim rg As Range
    Dim i As Integer

    With Sheets("LC")

        RegNum = .Range("G7")
        str = "No." & Format(.Range("A8"), "#000000") & vbNewLine & Format(.Range("B28"), "dd/mm/yyyy hh:mm:ss am/pm")    'Here I'm getting syntax error

        If RegNum = "" Then
            MsgBox "Please enter a valid register number!"
            .Range("G7").Select
            Exit Sub
        End If
    End With

    With Sheets("Register")
        On Error Resume Next
        Set rg = .Range("A6:A50000").Find(RegNum)

        If rg Is Nothing Then
            MsgBox "The register number is not found!"
            Exit Sub
        End If

        Set rg = .Range("AF" & rg.Row)
        i = 0
        Do Until rg.Value = ""
            Set rg = rg.Offset(0, 1)
            i = i + 1
        Loop
        rg = str

        Sheets("LC").Range("C7").Clear
        If i > 0 Then
            With Sheets("LC").Range("C7")
                .Font.Size = 20
                .Font.Bold = True
                .Font.ColorIndex = 3
                .Value = "Duplicate " & i
            End With
        End If
    End With
    MsgBox "The LC is recorded!", 64
End Sub

Hi mso3,

Here your revised code :

Sub RecordLC()

    Dim RegNum As String
    Dim str As String
    Dim rg As Range
    Dim i As Integer

    With Sheets("LC")

        RegNum = .Range("G7")
        str = "No." & Format(.Range("A8"), "#000000") & vbNewLine & Format(.Range("B28"), "dd/mm/yyyy hh:mm:ss am/pm")    'Here I'm getting syntax error

        If RegNum = "" Then
            MsgBox "Please enter a valid register number!"
            .Range("G7").Select
            Exit Sub
        End If
    End With

    With Sheets("Register")
        On Error Resume Next
        Set rg = .Range("A6:A50000").Find(RegNum)

        If rg Is Nothing Then
            MsgBox "The register number is not found!"
            Exit Sub
        End If

        Set rg = .Range("AF" & rg.Row)
        i = 0
        Do Until rg.Value = ""
            Set rg = rg.Offset(0, 1)
            i = i + 1
        Loop
        rg = str

        Sheets("LC").Range("C7").Clear
        If i > 0 Then
            With Sheets("LC").Range("C7")
                .Font.Size = 20
                .Font.Bold = True
                .Font.ColorIndex = 3
                .Value = "Duplicate " & i
            End With
        End If
    End With
    MsgBox "The LC is recorded!", 64
End Sub

Hi GC excel,
Thank you for updated version to meet my requirement. It's working fine with correct output.

I appreciate you for the same.

Thank you and have a nice day.