User Form entry in a second sheet - need help with VBA code

**DocAElstein** · 02-16-2022, 03:19 AM

Hello Atlantis764
Welcome to ExcelFox

I don’t have any experience with UserForms
and
I don’t really understand the full picture of what you are trying to do.

Here is a screenshot of the file you uploaded: https://excelfox.com/forum/showthrea...ll=1#post16365

I can see that there is some correlation in the coloured cells, but all I can understand from your explanation is that you want to match Name and Project and Task as you showed in the workbook

The best I can do is that I can get you started on doing that matching:

We can make an array which has as many elements as there are data rows in Database1
Each element will contain a string made up from each row of the
Name & Project & Task

We can do something similar for worksheet Database

Then you can match the strings in the two arrays.

Run this demo macro and I think you will see what I mean

Code:

'    https://excelfox.com/forum/showthread.php/2783-User-Form-entry-in-a-second-sheet-need-help-with-VBA-code?p=16371&viewfull=1#post16371
Sub MatchNameProjectTask()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
 Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
 Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
'Dim v: v = WsD.Evaluate("=D2:D4&E2:E4&F2:F4"): v = WsD.Evaluate("=D2:D" & LrD & "&E2:E" & LrD & "&F2:F" & LrD & "")
Dim arrD() As Variant: Let arrD() = WsD.Evaluate("=D2:D" & LrD & "&E2:E" & LrD & "&F2:F" & LrD & "")
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A2:A" & LrD1 & "&B2:B" & LrD1 & "&C2:C" & LrD1 & "")
Rem 3 compare arrays
Dim rwD As Long, rwD1 As Long
    For rwD = 2 To LrD
        For rwD1 = 2 To LrD1
            If arrD(rwD - 1, 1) = arrD1(rwD1 - 1, 1) Then MsgBox prompt:="match for " & arrD(rwD - 1, 1) & " at Database row " & rwD & " Database1 row " & rwD1
        
        Next rwD1
    Next rwD
End Sub

Here's the same basic macro done slightly differently

Code:

Sub MatchNameProjectTask2()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
 Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
 Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
Dim arrD() As String, arrD1() As String
 ReDim arrD(2 To LrD): ReDim arrD1(2 To LrD1)
Dim rwD As Long, rwD1 As Long
    For rwD = 2 To LrD
     Let arrD(rwD) = WsD.Range("D" & rwD & "") & WsD.Range("E" & rwD & "") & WsD.Range("F" & rwD & "")
    Next rwD
    For rwD1 = 2 To LrD1
     Let arrD1(rwD1) = WsD1.Range("A" & rwD1 & "") & WsD1.Range("B" & rwD1 & "") & WsD1.Range("C" & rwD1 & "")
    Next rwD1
Rem 3 compare arrays
    For rwD = 2 To LrD
        For rwD1 = 2 To LrD1
            If arrD(rwD) = arrD1(rwD1) Then MsgBox prompt:="match for " & arrD(rwD) & " at Database row " & rwD & " Database1 row " & rwD1
        Next rwD1
    Next rwD
End Sub