VBA or macros placing value in right plae

[tinki]

Hello,

I'm really new to the developer side of Excel and I wish to solve a problem with macros or VBA.

I have a table of items placed in colums (there are around 50 columns). In this table i scan a DMC (data matrix code) and place it in cell that it needs to be in. I'm kinda getting tired of clicking cell by cell.

I want to create a code/formula in one cell where i scan things and match them into right column. I ahve products form column A to column AY, and I've taken out part of serial no. that is specific to each product into row 2(right below name of that product). I want a code to run from cell A2 see if it the part of serial code matches, if not it continues to B2 and so on. When it finds a match i want it to write into first free cell in that column (example if part of serial codes match in C column, then the program writes scanned serial no. into first free cell in collumn C).

can anybody help me?

 

[Candyman8019]

Try something like this:

Private Sub test4()
Dim rng As Range
Dim Thiscell As Range
Dim myserial As String
Dim lastrow As Long

Set rng = Range("A2:AY2")

For Each Thiscell In rng
'you may want to specify what value to lookup in a cell somewhere, but for this demonstration, I will hardcode it.
myserial = "1234"
If Thiscell.Value = myserial Then
'paste value in last row
lastrow = ActiveSheet.Columns(Thiscell.Column).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
Cells(lastrow, Thiscell.Column).Value = myserial
Exit Sub
End If
Next Thiscell

End Sub

 

[Candyman8019]

and if you only need to find if the scanned serial is part of the cell value, you could try:
Private Sub test4()
Dim rng As Range
Dim Thiscell As Range
Dim myserial As String
Dim lastrow As Long

Set rng = Range("A2:AY2")

For Each Thiscell In rng
'you may want to specify what value to lookup in a cell somewhere, but for this demonstration, I will hardcode it.
myserial = "1234"
If InStr(1, Thiscell.Value, myserial, vbTextCompare) > 0 Then
'paste value in last row
lastrow = ActiveSheet.Columns(Thiscell.Column).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
Cells(lastrow, Thiscell.Column).Value = myserial
Exit Sub
End If
Next Thiscell

End Sub

 

[tinki]

and if you only need to find if the scanned serial is part of the cell value, you could try:
Private Sub test4()
Dim rng As Range
Dim Thiscell As Range
Dim myserial As String
Dim lastrow As Long

Set rng = Range("A2:AY2")

For Each Thiscell In rng
'you may want to specify what value to lookup in a cell somewhere, but for this demonstration, I will hardcode it.
myserial = "1234"
If InStr(1, Thiscell.Value, myserial, vbTextCompare) > 0 Then
'paste value in last row
lastrow = ActiveSheet.Columns(Thiscell.Column).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
Cells(lastrow, Thiscell.Column).Value = myserial
Exit Sub
End If
Next Thiscell

End Sub

Thank you soo much !!!

With just a little chabge to the code, the code I managed to make it work as I wanted it. I'm really new to this and this helped me a lot, and also i understand a bit more of the programming language for excel.

I'll also share the code:

Private Sub TextBox1_Change()

Dim rng As Range
Dim Thiscell As Range
Dim myserial As String
Dim lastrow As Long


Set rng = Range("A3:AY3")

For Each Thiscell In rng
'you may want to specify what value to lookup in a cell somewhere, but for this demonstration, I will hardcode it.

myserial = TextBox1.Value

If InStr(1, Thiscell.Value, Left(myserial, Len(Thiscell.Value)), vbTextCompare) > 0 Then
'paste value in last row
lastrow = ActiveSheet.Columns(Thiscell.Column).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
Cells(lastrow, Thiscell.Column).Value = myserial
TextBox1.Text = ""
Exit Sub
End If
Next Thiscell
TextBox1.Text = ""

End Sub

 

[Candyman8019]

Happy to help.

 

[Peter_SSs]

@Candyman8019 @tinki

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details.

Private Sub test4()
Dim rng As Range
Dim Thiscell As Range
Dim myserial As String
Dim lastrow As Long

Set rng = Range("A2:AY2")

For Each Thiscell In rng
'you may want to specify what value to lookup in a cell somewhere, but for this demonstration, I will hardcode it.
myserial = "1234"
  If Thiscell.Value = myserial Then
    'paste value in last row
    lastrow = ActiveSheet.Columns(Thiscell.Column).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
    Cells(lastrow, Thiscell.Column).Value = myserial
    Exit Sub
  End If
Next Thiscell

End Sub

The above code is much easier to read/debug than the below code.

Private Sub test4()
Dim rng As Range
Dim Thiscell As Range
Dim myserial As String
Dim lastrow As Long

Set rng = Range("A2:AY2")

For Each Thiscell In rng
'you may want to specify what value to lookup in a cell somewhere, but for this demonstration, I will hardcode it.
myserial = "1234"
If Thiscell.Value = myserial Then
'paste value in last row
lastrow = ActiveSheet.Columns(Thiscell.Column).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
Cells(lastrow, Thiscell.Column).Value = myserial
Exit Sub
End If
Next Thiscell

End Sub

 

[Candyman8019]

Thanks Peter. I was looking for that initially, but all of my icons are greyed out. I just realized now that there is a button called "[] toggle bb code" to activate them.