Update a new sheet from Userform

[francobonell]

Hi everyone :hi:

I am new here and this is my 1st post(so please be gentle with me :rotlaugh:), but will be a frequent user/visitor since my new job will be a lot easier with VBA!

I have a little issue with updating a new sheet with some scanned data from a Userform.
The Userform has 2 textboxes and a button to activate the code.

The idea is for the running code to check the scanned inputs and set an "X" in a new sheet in the cell that corresponds to the location of the row and column that corresponds to the scanned inputs.

So far, I have been able to set an "X" in a defined location(in this case the 6th column), but ideally the "X" should appear in the yellow marked cell - please see attached picture and code example.

I could of course hard code it to each desired cell but the worksheet will be used by multiple people and constantly updated with multiple rows and columns.

Any help anyone can offer is greatly appreciated :bow:.

Regards Franco.


Sub RegisterButton1_Click()

Dim ID As String


ID = Trim(TextBox2.Text)
lastrow = Worksheets("Docnums").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow


If Worksheets("Docnums").Cells(i, 1).Value = ID Then
Worksheets("Docnums").Cells(i, 6).Value = "X"
End If
Next


TextBox1.Value = ""
TextBox2.Value = ""


Call DisplayMessage


Unload UserForm1


End Sub

[p45cal]

Attach a workbook rather than a picture of one.

[francobonell]

Hi p45cal

Yes of course, thank you.

Sorry I didn't do it already...I'm such a newbie :crying:.

Should be there now.

Regards
Franco.

[p45cal]

Your macro tweaked (see comments in it too):

Public Sub UpdateCompMatrix()

Dim SOPtest As Workbook
Dim SOP_kompetence_matrix As Worksheet

Dim LastRowID As Long
Dim LastColumnSOP As Long
Dim c As Long
Dim r As Long

Set SOPtest = ThisWorkbook
Set SOP_kompetence_matrix = SOPtest.Sheets("SOP_kompetence_matrix") 'ActiveSheet

LastRowID = SOP_kompetence_matrix.Cells(Rows.Count, "A").End(xlUp).Row
LastColumnSOP = SOP_kompetence_matrix.Cells(2, Columns.Count).End(xlToLeft).Column

With SOP_kompetence_matrix
''Either this:
Set rw = .Range("A3:A" & LastRowID).Find(TextBox2.Text, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
If Not rw Is Nothing Then
Set colm = .Range("B2").Resize(, LastColumnSOP - 1).Find(TextBox1.Text, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
If Not colm Is Nothing Then
Intersect(rw.EntireRow, colm.EntireColumn).Value = "X"
Else
MsgBox "SOP not found"
End If
Else
MsgBox "ID not found"
End If

''or this:
' For c = 2 To LastColumnSOP
' If .Cells(2, c).Value = TextBox1.Text Then
' For r = 3 To LastRowID
' If .Cells(r, 1).Value = TextBox2.Text Then
' .Cells(r, c).Value = "X"
' Exit For
' End If
' Next r
' Exit For
' End If
' Next c

End With
End SubCopy the whole code across, there are changes that you won't spot easily.

[francobonell]

Hi p45cal

Thank you so much for your help : pray2:.

I see where I went wrong. I just tried it and it is exactly what I need for the next step.

Thank you so much, you are awesome :bow:.

Regards

Franco