Macro run through excel sheet only returns false

C

cfcooper

New Member
Joined
Jan 11, 2019
Messages
7
I've never posted on here before so let me know if you need more information. I'm also not an expert in coding but have been doing basic coding for awhile.
I created an inventory tracking sheet but some of the products were being entered more than once. I'm trying to create a find system that uses the formula ID number and the date last created that will change the font color if the product already exists in the table.

The macro runs great if I click run in the visual basic editor but will only return false results when I run it from the command button on the excel sheet. No errors pop-up.
I am using excel 2007

Code: 
Sub FindItem()


Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Generated Samples")


Dim i As Long, Endrow As Long, answer As Integer, answer1 As Integer, answer2 As Integer


'Msg Box
If frmNewInv.ListFormulaID = "" Then
    answer = MsgBox("Please select a Formula ID number.", vbCritical, "Please Complete")
    Exit Sub
End If


Endrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 7 To Endrow
        If ws.Cells(i, 1) = frmNewInv.ListFormulaID And ws.Cells(i, "H") = frmNewInv.dtpFindDate Then
            ws.Cells(i, 1).EntireRow.Font.ColorIndex = 3
            frmNewInv.Hide
            answer1 = MsgBox("Item Found.", vbOKCancel + vbInformation, "Found Item")
                If answer1 = vbOK Then
                    ws.Cells(i, 1).EntireRow.Font.ColorIndex = 1
                    frmNewInv.Show
                Else
                    ws.Cells(i, 1).EntireRow.Font.ColorIndex = 1
                    Unload frmNewInv
                End If
        ElseIf ws.Cells(i, 1) = frmNewInv.ListFormulaID And ws.Cells(i, "H") <> frmNewInv.dtpFindDate Then
            answer2 = MsgBox("Item does not exist.", vbExclamation, "No Item Found")
        End If
    Next i

End Sub

When I run the code from the userform on the excel sheet, I choose a date and formulaID that at on the table and should return true (and does if run from the editor). So, i feel like the problem is in the "ws.Cells(i, "H") = frmNewInv.dtpFindDate" and possibly something with formatting?!?
The date is inputted into the table using the date-time picker and I am also using the dtpicker to choose a date to find with formaulaID in the table.

Thank you
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
I have the following doubts.
Is ListFormulaID a combobox?
Is dtpFindDate a DTPicker Control?
The ID number only has numbers?
If the above is correct, then try the following:


Code: 
Sub FindItem()


    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Generated Samples")
    Dim i As Long, Endrow As Long, answer As Integer, answer1 As Integer, answer2 As Integer


    'Msg Box
    If frmNewInv.ListFormulaID = "" Then
        answer = MsgBox("Please select a Formula ID number.", vbCritical, "Please Complete")
        Exit Sub
    End If
    
    Endrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
[COLOR=#0000ff]    If IsNumeric(frmNewInv.ListFormulaID) Then[/COLOR]
[COLOR=#0000ff]        numID = Val(frmNewInv.ListFormulaID)[/COLOR]
[COLOR=#0000ff]    Else[/COLOR]
[COLOR=#0000ff]        numID = frmNewInv.ListFormulaID[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
    For i = 7 To Endrow
        If ws.Cells(i, 1) = [COLOR=#0000ff]numID [/COLOR]And ws.Cells(i, "H") = frmNewInv.dtpFindDate Then
            ws.Cells(i, 1).EntireRow.Font.ColorIndex = 3
            frmNewInv.Hide
            answer1 = MsgBox("Item Found.", vbOKCancel + vbInformation, "Found Item")
            If answer1 = vbOK Then
                ws.Cells(i, 1).EntireRow.Font.ColorIndex = [COLOR=#0000ff]3[/COLOR]
                frmNewInv.Show
            Else
                ws.Cells(i, 1).EntireRow.Font.ColorIndex = 1
                Unload frmNewInv
            End If
        ElseIf ws.Cells(i, 1) = [COLOR=#0000ff]numID [/COLOR]And ws.Cells(i, "H") <> frmNewInv.dtpFindDate Then
            answer2 = MsgBox("Item does not exist.", vbExclamation, "No Item Found")
        End If
    Next i


End Sub
 
Upvote 0
The ListFormulaID is a combobox and is a mixture of letters and numbers (i.e. BCC-02 and CC-CG-04). The rowsource is a dynamic range created from the Formula ID column in the table so the user has the option of selecting an ID number that only exists in the table.

When a new item is entered into the table the user picks the date using the dtpicker box. The date is then entered into the table in a short format as 1/14/2019 which should equal ws.cells(i, "H").
When the user is trying to find the item, they select the formulaID number from the combobox then select the date using the dtpicker and click the command button named Find.

I've been using the same coding for the formulaID in other macros in the same sheet and have not had any problems. I'm going to try that the user just enter the date manually in the short format and will let you know if that works.

I know that the sub name does not have "Click" that is because I am calling the sub.
 
Upvote 0
DanteAmor,

I just changed the code for the user to enter the date manual as 1/14/2019 but the results still only return as false.
How I know is that I know the item exists in the table but the message box replies "Item does not exist." So it is finding the formulaID but cannot find a matching date.

Thank you for your reply.
 
Upvote 0
Try only with the number, if it works then we check what happens with the date.

Code: 
Sub FindItem()




    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Generated Samples")
    Dim i As Long, Endrow As Long, answer As Integer, answer1 As Integer, answer2 As Integer




    'Msg Box
    If frmNewInv.ListFormulaID = "" Then
        answer = MsgBox("Please select a Formula ID number.", vbCritical, "Please Complete")
        Exit Sub
    End If
    
    Endrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    If IsNumeric(frmNewInv.ListFormulaID) Then
        numID = Val(frmNewInv.ListFormulaID)
    Else
        numID = frmNewInv.ListFormulaID
    End If
    For i = 7 To Endrow
[COLOR=#0000ff]        If ws.Cells(i, 1) = numID  Then[/COLOR]
            ws.Cells(i, 1).EntireRow.Font.ColorIndex = 3
            frmNewInv.Hide
            answer1 = MsgBox("Item Found.", vbOKCancel + vbInformation, "Found Item")
            If answer1 = vbOK Then
                ws.Cells(i, 1).EntireRow.Font.ColorIndex = 3
                frmNewInv.Show
            Else
                ws.Cells(i, 1).EntireRow.Font.ColorIndex = 1
                Unload frmNewInv
            End If
        ElseIf ws.Cells(i, 1) = numID And ws.Cells(i, "H") <> frmNewInv.dtpFindDate Then
            answer2 = MsgBox("Item does not exist.", vbExclamation, "No Item Found")
        End If
    Next i




End Sub
 
Upvote 0
DanteAmor,

The numID code came back with an error.

I took out the dtpicker from all my codes so when the user inserts a new item into the table they enter the date in manually and the Find Item code worked.
 
Upvote 0
cfcooper said:
DanteAmor,

The numID code came back with an error.

I took out the dtpicker from all my codes so when the user inserts a new item into the table they enter the date in manually and the Find Item code worked.
Click to expand...


What error appears in the NumId?

What do you have in the numid, only numbers, then the search must be with numbers

Let's try only with the search of the numid.

Could you give an example of what you have on your sheet? Or, upload the file to a cloud to review the code and your values.
 
Upvote 0
Thank you for your help DanteAmor,

I figured it out

Rich (BB code): 
Sub FindItem()


Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Generated Samples")


Dim i As Long, Endrow As Long, answer As Integer, answer1 As Integer, answer2 As Integer


'Msg Box
If frmNewInv.ListFormulaID = "" Then
    answer = MsgBox("Please select a Formula ID number.", vbCritical, "Please Complete")
    Exit Sub
End If


frmNewInv.dtpFindDate.Value = Format(frmNewInv.dtpFindDate, "mm/dd/yyyy")
Endrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 7 To Endrow
        If ws.Cells(i, 1) = frmNewInv.ListFormulaID And ws.Cells(i, "H") = frmNewInv.dtpFindDate Then
            ws.Cells(i, 1).EntireRow.Font.ColorIndex = 3
            frmNewInv.Hide
            answer1 = MsgBox("Item Found.", vbOKCancel + vbInformation, "Found Item")
                If answer1 = vbOK Then
                    ws.Cells(i, 1).EntireRow.Font.ColorIndex = 1
                    frmNewInv.Show
                Else
                    ws.Cells(i, 1).EntireRow.Font.ColorIndex = 1
                    Unload frmNewInv
                End If
        ElseIf ws.Cells(i, 1) = frmNewInv.ListFormulaID And ws.Cells(i, "H") <> frmNewInv.dtpFindDate Then
            answer2 = MsgBox("Item does not exist.", vbExclamation, "No Item Found")
        End If
    Next i




End Sub

All I had to do was adjust the format the the datetime picker to get rid of the time that was after the date.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Can you please explain why this code getting error while running
Replies
2
Views
225
Joe4
Joe4
D
  • Solved
Excel Macro Runs With Breakpoint but Fails Without It
Replies
3
Views
293
Domer82
D
L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
458
mumps
mumps
G
  • Question Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
389
GK039
G
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
800
Agnarr
Agnarr

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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