VBA code not finding values that contain numbers and letters....HELP!

[Panda]

Hi All,

I have a spreadsheet that I am working on for my place of work and I am having trouble with some code that i have found online and am trying to tweek. Unfortunatly for some reason I can not upload an example of the spreadsheet that I am working on (I am assuming it is my work IT's secuirty protocols that are preventing the upload taking place successfully) so I wil try to explain best I can.

I have a table (Event Log) that lists part numbers and all information about each of them an example of which is towards the bottom of this post. The vba code is supposed to search for the barcode that is entered on another worksheet and within the table below. Once it finds it, the code should enter the date, time and quantity all of which is copied over from another page.

However, the code seems to only work when the barcode simply consists of numbers, it fails to work when a mixture of numbers and text are used.

Here is the code that I am struggling with;



Sub Find_Bug()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim MyVal As String
Dim rFind As Range
Dim lLastRow As Long
Dim i As Long
Set Ws1 = Sheets("Book_In_Part")
Set Ws2 = Sheets("Event_Log_Test")
MyVal = Ws1.Range("B9")
Ws2.Select

'lLastRow = Ws2.Range("A" & Rows.Count).End(xlUp).Row
lLastRow = Ws2.Range("B65536").End(xlUp).Row


For i = 8 To lLastRow
If Cells(i, 1).Value = MyVal Then
If Cells(i, 1).Offset(0, 5) = "Out of Cabinet" Then
If Not IsDate(Cells(i, 1).Offset(0, 8)) Then

'Copy Qty used data from Book_In_Part and paste into column 4
Ws1.Range("F9").Copy
Cells(i, 1).Offset(0, 4).PasteSpecial xlPasteValues
Application.CutCopyMode = False

'Copy User ID from Book_In_Part and paste into column 11
Ws1.Range("M9").Copy
Cells(i, 1).Offset(0, 12).PasteSpecial xlPasteValues
Application.CutCopyMode = False

'Add Date and Time Part Booked Back in
Cells(i, 1).Offset(0, 8) = Now()

End If
End If
End If
Next i
End Sub



Below is a screenshot of the 'Event Log'

12082

Can anyone help me with this, or point me in the right direction?

Thanking you in advance

Phil

[westconn1]

have you checked for spaces (or other non-printing characters) in both sheets?
find the matching cell and compare lenths of string

if it is a large range of data it may be better to use excels find method, rather than looping all cells, your code loops all cells even if a match is found in the first row

without some actual sample data values, hard to test

[Panda]

have you checked for spaces (or other non-printing characters) in both sheets?
find the matching cell and compare lenths of string

if it is a large range of data it may be better to use excels find method, rather than looping all cells, your code loops all cells even if a match is found in the first row

without some actual sample data values, hard to test

Thanks for your reply, I have checked for spaces and there aren't any. the code seems to work when I have pure numbers, but as soon as there are wither letters or characters such as - or / is seems to fail. I have just tried to upload the file but for some reason on both my work computer and home computer i keep getting an error message from the forums uploading tool =:s.

Is there another way that I can upload an example?

Thanks

Phil

[Bob Phillips]

You seem to be testing a time cell for the value 'Out of Cabinet', that will never pass.

[Panda]

Ah sorry, there is a hidden column that contains whether it is in or out of the cabinet. I really want to add the example file for people to see but for some reason I keep getting an error when I try to upload it?

[westconn1]

I really want to add the example file for people to see but for some reason I keep getting an error when I try to upload it?if you can not upload here for some reason, try some web filehosting or dropbox

[Panda]

if you can not upload here for some reason, try some web filehosting or dropbox

This is a great idea. As I am still having trouble uploading the file using the forums uploader, below is a link to the file that I am trying to get to work.

http://www.filedropper.com/eventlogexample

Can anyone help me to get this working?

Thanks

Phil

[Paul_Hossler]

I'm wondering if the was just a data issue???? The B9 was in the event log 3 times, but as far as I can tell, it wouldn't have need found based on your tests

I tried to test your logic but I had to change some of the data (look for the orange)



Sub Find_Bug1()
Dim wsBookInPart As Worksheet
Dim wsEventLog As Worksheet
Dim MyVal As String
Dim rFind As Range
Dim lLastRow As Long
Dim i As Long

Set wsBookInPart = Sheets("Book_In_Part")
Set wsEventLog = Sheets("Event_Log_Test")

MyVal = wsBookInPart.Range("B9")

With wsEventLog

lLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row


For i = 10 To lLastRow
If .Cells(i, 1).Value = MyVal Then
If .Cells(i, 6).Value = "Out of Cabinet" Then
If Not IsDate(.Cells(i, 9).Value) Then

'Copy Qty used data from Book_In_Part and paste into column 4
wsBookInPart.Range("F9").Copy
.Cells(i, 5).PasteSpecial xlPasteValues

'Copy User ID from Book_In_Part and paste into column 11
wsBookInPart.Range("M9").Copy
.Cells(i, 13).PasteSpecial xlPasteValues

'Add Date and Time Part Booked Back in
.Cells(i, 9).Value = Now()
End If
End If
End If
Next i
End With

Application.CutCopyMode = False
End Sub



Take a look at this and see if it helps. The cells are in ORANGE that I changed to test

BTW, your file was 3.7 MB which is over the limit for the forum

[Panda]

Hi Paul,

Thanks for taking a look and getting back to me. The Event log is basically a record of all the 'transactions' that have occured in the system, so there will be many instances of the part numbers and lot ids detailed (some of which will be the same). When a user books out a part all the relavent details are copied and pasted into the event log. Then when the same part is booked back in, this code is supposed to search for the part number and lot id and then fill in the missing data, such as quantity used, time booked back in and user id of the person booking the part back in.

The issue that I have been having is that the code works when part numbers purely contain numbers, and seems not to work when the part number has a mixture of numbers and text.

I have had a look at the example you very kindly attached, and sorry to be dim, but I can seem to get it to work so that it would enter the missing information for S87654321 lot id 11111111?

Thanks again for trying to help, but is there a way I can tweak this code to do the above?

Phil

[snb]

Sub M_snb()
sn = Sheets("book_in_part").Cells(8, 2).CurrentRegion

For j = 2 To UBound(sn)
With Sheets("Event_Log_test").Columns(1).Find(sn(j, 1), , xlValues, 1)
.Offset(, 4) = sn(j, 5)
.Offset(, 12) = sn(j, 7)
End With
Next
End Sub

[Paul_Hossler]

I'm still not 100% sure I'm understanding but this was my thinking


1 -- eliminates row 12, 13, 14, 15 since they're not = B9 = 87654321/00000000 on Book In Part

If Cells(i, 1).Value = MyVal Then


2 -- eliminates row 10 since F10 NOT equal Out of Cabinet

If Cells(i, 1).Offset(0, 5) = "Out of Cabinet" Then



3 -- eliminates rows 11 and 16 since they Do have a date in I11 and I16

If Not IsDate(Cells(i, 1).Offset(0, 8)) Then

--------------- so there's no critera that matches to update using that B9



Applying the same logic to S87654321/11111111 in rows 12, 14, and 15 then row 15 seems to meet the criteria and it gets updated


"get it to work so that it would enter the missing information for S87654321 lot id 11111111?"

I counted wrong since I didn't notice that Col F was hidden. See if this is any better



Sub Find_Bug2()
Dim wsBookInPart As Worksheet
Dim wsEventLog As Worksheet
Dim MyVal As String
Dim rFind As Range
Dim lLastRow As Long
Dim i As Long

Set wsBookInPart = Sheets("Book_In_Part")
Set wsEventLog = Sheets("Event_Log_Test")

MyVal = wsBookInPart.Range("B9")

With wsEventLog

lLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row


For i = 10 To lLastRow
If .Cells(i, 1).Value = MyVal Then
If .Cells(i, 6).Value = "Out of Cabinet" Then ' Col F
If Not IsDate(.Cells(i, 9).Value) Then ' Col I

'Copy Qty used data from Book_In_Part and paste into column D
wsBookInPart.Range("F9").Copy
.Cells(i, 4).PasteSpecial xlPasteValues

'Copy User ID from Book_In_Part and paste into column L
wsBookInPart.Range("M9").Copy
.Cells(i, 12).PasteSpecial xlPasteValues

'Add Date and Time Part Booked Back in Col I
.Cells(i, 9).Value = Now()
End If
End If
End If
Next I
End With

Application.CutCopyMode = False
End Sub

[Panda]

I'm still not 100% sure I'm understanding but this was my thinking


1 -- eliminates row 12, 13, 14, 15 since they're not = B9 = 87654321/00000000 on Book In Part

If Cells(i, 1).Value = MyVal Then


2 -- eliminates row 10 since F10 NOT equal Out of Cabinet

If Cells(i, 1).Offset(0, 5) = "Out of Cabinet" Then



3 -- eliminates rows 11 and 16 since they Do have a date in I11 and I16

If Not IsDate(Cells(i, 1).Offset(0, 8)) Then

--------------- so there's no critera that matches to update using that B9



Applying the same logic to S87654321/11111111 in rows 12, 14, and 15 then row 15 seems to meet the criteria and it gets updated


"get it to work so that it would enter the missing information for S87654321 lot id 11111111?"

I counted wrong since I didn't notice that Col F was hidden. See if this is any better



Sub Find_Bug2()
Dim wsBookInPart As Worksheet
Dim wsEventLog As Worksheet
Dim MyVal As String
Dim rFind As Range
Dim lLastRow As Long
Dim i As Long

Set wsBookInPart = Sheets("Book_In_Part")
Set wsEventLog = Sheets("Event_Log_Test")

MyVal = wsBookInPart.Range("B9")

With wsEventLog

lLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row


For i = 10 To lLastRow
If .Cells(i, 1).Value = MyVal Then
If .Cells(i, 6).Value = "Out of Cabinet" Then ' Col F
If Not IsDate(.Cells(i, 9).Value) Then ' Col I

'Copy Qty used data from Book_In_Part and paste into column D
wsBookInPart.Range("F9").Copy
.Cells(i, 4).PasteSpecial xlPasteValues

'Copy User ID from Book_In_Part and paste into column L
wsBookInPart.Range("M9").Copy
.Cells(i, 12).PasteSpecial xlPasteValues

'Add Date and Time Part Booked Back in Col I
.Cells(i, 9).Value = Now()
End If
End If
End If
Next I
End With

Application.CutCopyMode = False
End Sub




Ah sorry thats a my bad, column A is a concatenation of the cells but is in white font so that it is hidden from the user, so in therory line 15 should be picked up by the code.

Thanks for your patience and effort in trying to help me btw.

Phil

[snb]

Did you overlook #10 ?