• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search data in a column and stamp a string or "date" in another column of the same table

NeilCruz

New Member
Hello all,

I am working on a spreadsheet similar to the attached file everyday. The spreadsheet I worked with can create labels for the materials with a Record ID on it and once that materials were shipped, that label will return to me and the next step that I will do is to update the spreadsheet by filling the Date Shipped column on the corresponding Record ID.
This is a lot of searching and scrolling specially when the list is in hundreds just to find the Record ID.
With this, I think of a way to automate the process by using barcodes, so I did include a barcode of the Record ID of the corresponding labels, so that when the labels return back to me, I will just scan the barcode while on the spreadsheet, and there I have the Record ID in a click of a button. But I don't have enough skill in VBA and I'm asking your help to do the next process which is the VBA code in the command button. With the Record ID already on a particular cell by the aid of barcode scanner and manually input the date shipped on another cell and a command button to execute the process of searching and stamping the date on the corresponding column, I do believe that it is possible to create a simple VBA for that....It will be a great help for me. I thank everybody in reading this and I will appreciate very much for any help you can extend.
 

Attachments

  • Invetory.xlsx
    13.5 KB · Views: 4
Hi Neil

Give the following a go. Should do what you want. Alter the range to suit.

Code:
Private Sub CommandButton1_Click()
    Range("G" & [C3:C100].Find([j6]).Row) = [J8]
End Sub

File attached to show workings.

Take care

Smallman
 

Attachments

  • InvetorySmallman.xlsm
    21.6 KB · Views: 8
Hi Neil

Been doing this a long time now and that is one of the nicest replies I have seen. Thanks a lot and pleased you got your answer. :)

Take care

Smallman
 
To: Smallman,

I have use the code you supplied and it works as it intended to do. I even replaced modification such the data entry cell J6 to Textbox1 because everytime I hit the command button cursor shift down to the next cell and I have to press the Enter Button to recognize the entry. By using the textbox1, pressing Enter button is eliminated just hit the command button. Although it work as intended, the cursor always returns to the last cell position, I read some of the code to keep the cursor on the textbox like the Textbox.SetFocus, the set Cancel=false, but to no avail...Do you have an idea to keep the position of the cursor stay at the textbox?

Yours, NeilCruz
 
Hi Neil

Do you have your new file with the Text box. Might be easier to fix if I can see the problem rather than trying to recreate it.

Take care

Smallman
 
To: Smallman,

Yes, here it is..it seems that the entry on the textbox value does not recognized as an exact value, i.e , when I type 4, it goes to 104, when i type 104 it goes to 104 also..
 

Attachments

  • Invetory2.xlsm
    29.9 KB · Views: 3
Hi Neil

You just have to ensure an exact match. I will add some error trapping too in case of an input error.

Take care

Marcus
Code:
Private Sub CommandButton1_Click()
    On Error Resume Next
    Range("G" & [C3:C100].Find(TextBox1.Value, , , xlWhole).Row) = TextBox2
    On Error GoTo 0
    TextBox1.Value = ""
End Sub

See attached file.

Take care

Smallman
 

Attachments

  • Invetory2.xlsm
    30.6 KB · Views: 6
To Smallman

How nice of you!, the error for data entry has been fixed. Hope we could also fix the cursor location. That is, after hitting the command button, the cursor goes to the last cell position. Hope we could find a way to reposition the cursor at Texbox1 and ready to received data again without using a mouse.

Thanks again and take care too....

Neil
 
Hi Neil

No worries at all.

Before the end sub line put this line.

Code:
TextBox1.Activate

That will set the focus on the text box so you won't have to use your mouse.

Take care

Smallman
 
Hi Smallman,

I have had transfer the code to my file at work, and there seems to be a problem. I have typed exactly the same code but when I run it, pop up warning says "Compile Error! Can't find Project or Library".
Any ideas ?..

Neil
 
Hi Neil

If you post your workbook I will be able to spot the problem. Nothing wrong with the procedure so it will be something small.

Take care

Smallman
 
Yeah , .. but for the meantime..., I have made some improvements on my workbook with the use of "UserForm" (my first btw Lol!)with the same code that we work with.
An addition of errortrap works fine but its best if we cud add MsgBox to appear everytime the Record ID cannot be found. I wish also to add another two textbox, one for the counter (how many records that had been entered)and the other for the cummulative weight (total weight of records that had been entered). Hope we could capture the weight and add it cumulatively?
I have created some code but it does not behave that way..See attached..

Neil
 

Attachments

  • Invetory1.xlsm
    28.1 KB · Views: 1
Hi Smallman,

Attached is the file that I am referring to with a pop up warning of "Compile Error! Can't find Project or Library". I have no idea what is causing this problem.
Hope you can fix it.

Thanks and more power!

Neil
 

Attachments

  • Inventory6.xlsm
    54.9 KB · Views: 3
Hi Neil

I just ran 4 tests on your dataset. All of them ran and produced the desired result.

I did not get the error you spoke of.

Anyways Press Alt F11 - Goto Tools References.

Now see if any of the libraries say MISSING.

If this is the case you will need to add these libraries.

Hope this helps.

Smallman
 
Hi Smallman,

Yeah, I've found the MISSING library and I unchecked it, and it works now.
Thanks Again....


Neil
 
Back
Top