userform data information transfer

[xacx3x]

hello

I have three sheets within a Workbook - Sheet A, Sheet B and Sheet C

In Sheet B, i have a userform in which the user inputs the data and Sheet B will update according to the user input

In Sheet C, I have a macro that will compare sheet A and sheet B and place all the lines that do not match.

The place where I am stuck is that I want to use the data that the user inputs in sheet B in sheet C after I have compared Sheet A and B

Is there way to do this?

Please let me know if I did not explain this properly

Thanks
Avni

[nathansav]

Any more info? Did you code the forms or use the data entry ones?

[xacx3x]

i created the userform

this is the code for the userform on Sheet B

Private Sub Ok_Click()
'yellow clients
Unload Me

Dim YellowClients As Variant
YellowClients = Split(Yellow1.Value, vbNewLine)

Dim i As Integer
Dim F As Range
Dim YUI As String
Dim FA

For i = LBound(YellowClients) To UBound(YellowClients)
    YUI = YellowClients(i)
    Set F = ActiveSheet.Cells.Find(YUI, lookat:=xlPart)
    If F Is Nothing Then
    Else
        FA = F.Address
        While Not F Is Nothing
        ActiveSheet.Cells(F.Row, "D") = "Yellow"
        ActiveSheet.Cells(F.Row, "D").Interior.ColorIndex = 6
        ActiveSheet.Cells(F.Row, "D").Interior.Pattern = xlSolid

        Set F = ActiveSheet.Cells.FindNext(F)
        If F.Address = FA Then
            Set F = Nothing
        End If
    Wend
    End If
Next i

'Red clients

Dim RedClients As Variant
RedClients = Split(Red1.Value, vbNewLine)

Dim j As Integer
Dim G As Range
Dim RUI As String
Dim GA

For j = LBound(RedClients) To UBound(RedClients)
    RUI = RedClients(j)
    Set G = ActiveSheet.Cells.Find(RUI, lookat:=xlPart)
    If G Is Nothing Then
    Else
        GA = G.Address
        While Not G Is Nothing
        ActiveSheet.Cells(G.Row, "D") = "Red"
        ActiveSheet.Cells(G.Row, "D").Interior.ColorIndex = 3
        ActiveSheet.Cells(G.Row, "D").Interior.Pattern = xlSolid

        Set G = ActiveSheet.Cells.FindNext(G)
        If G.Address = GA Then
            Set G = Nothing
        End If
    Wend
    End If
Next j

'Green clients

Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Dim Rdata As Long
For Rdata = 4 To lastrow
    If ActiveSheet.Cells(Rdata, "D").Value = "" Then
        ActiveSheet.Cells(Rdata, "D").Value = "Green"
        ActiveSheet.Cells(Rdata, "D").Interior.ColorIndex = 4
        ActiveSheet.Cells(Rdata, "D").Interior.Pattern = xlSolid
    End If
Next Rdata


 
End Sub

And this is the code

Private Sub NotShipped_Click()

Dim JDE As Worksheet
Set JDE = ActiveWorkbook.Sheets(1)
Dim LRJDE As Long
LRJDE = JDE.Cells(Rows.Count, "A").End(xlUp).Row

Dim JET As Worksheet
Set JET = ActiveWorkbook.Sheets(2)
Dim LRJET As Long
LRJET = JET.Cells(Rows.Count, "A").End(xlUp).Row

'In JDE the protocol is col A, consignment # is col B and Customer is col D
'In JET the protocol is col B, consignmnet # is col A and customer is col D

Dim consignmnet As String
Dim cfind As Range
Dim protocol As String
Dim pfind As Range
Dim customer As String
Dim cufind As Range

Dim nextrow As Integer

'JDE to JET


Dim i As Integer
Dim x As Integer
'Dim rngFound As String

For i = 5 To LRJDE Step 1
    consignmnet = JDE.Cells(i, 2)
    protocol = JDE.Cells(i, 1)
    customer = JDE.Cells(i, 4)
   
   
   
   Set cfind = JET.Columns("A:A").Find(what:=consignmnet, lookat:=xlPart)
   Set pfind = JET.Columns("B:B").Find(what:=protocol, lookat:=xlWhole)
   Set cufind = JET.Columns("C:C").Find(what:=customer, lookat:=xlPart)
   
        If Not cfind Is Nothing And Not pfind Is Nothing And Not cufind Is Nothing Then
            Exit For
        Else
            nextrow = Application.Max(Worksheets("Not Shipped").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row, 4)
            Sheet3.Cells(nextrow, 1).Value = consignmnet
            Sheet3.Cells(nextrow, 2).Value = protocol
            Sheet3.Cells(nextrow, 3).Value = customer
            Sheet3.Cells(nextrow, "G").Value = "JDE"
        End If
   
Next i

'JET to JDE
For i = 5 To LRJET Step 1
    consignmnet = JET.Cells(i, 1)
    protocol = JET.Cells(i, 2)
    customer = JET.Cells(i, 3)
   
   
   
   Set cfind = JDE.Columns("B:B").Find(what:=consignmnet, lookat:=xlPart)
   Set pfind = JDE.Columns("A:A").Find(what:=protocol, lookat:=xlWhole)
   Set cufind = JDE.Columns("D:D").Find(what:=customer, lookat:=xlPart)
   
        If Not cfind Is Nothing And Not pfind Is Nothing And Not cufind Is Nothing Then
            Exit For
        Else
            nextrow = Application.Max(Worksheets("Not Shipped").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row, 4)
            Sheet3.Cells(nextrow, 1).Value = consignmnet
            Sheet3.Cells(nextrow, 2).Value = protocol
            Sheet3.Cells(nextrow, 3).Value = customer
            Sheet3.Cells(nextrow, "G").Value = "JET"
        End If
   
Next i

Sheet3.Columns.AutoFit

End Sub

I want to then add the input from the userform in sheet B into sheet C after I autofit the columns

I am not sure how to do that because i need to update sheet B, then compare it to A and and it auto populates sheet c and then I wanteto update sheet C with the user input from sheet B

[nathansav]

It looks like you already know how Sheet3.range("a1").value or sheet3.cells(1,1).value

[xacx3x]

yes but I am unsure on how to transfer the list of yellow clients in sheet b that I obtained from the userform and use it in sheet 3 after I have compared everything. How do I call on that variable again without having to have the user input all the data again after i compared the data