Submit button: How to make the destination of submitted data conditional

[Morten Lahrmann]

Hey all

Im working on a sheet to keep track of a procentage of defects at my job. Here I've created a Submit-button which move three pieces of information "Batchnumber, Precentage, and Product" to another sheet in the same workbook. However, we have two categories of products and I would like to for the submit-macro to place the information moved differently depending on the category.

I would thinking some sort of IF-statement along the lines of "IF the product is PX1, PX2, PX3 move to "B", "C", "D" ELSE move to "F", "G", "H"", but dont know exactly how to include it in the macro code below.

Anyone have any experiences with something similar?

BR
Morten, Denmark

Sub Rectangle2_Click()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant, v2 As Variant
Set sh1 = Worksheets("Fyldefejl")
Set sh2 = Worksheets("Data")
v1 = Array("E27", "F27", "G27")
v2 = Array("B", "C", "D")
rw = sh2.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row
For i = LBound(v1) To UBound(v1)
 Set r1 = sh1.Range(v1(i))
 Set r2 = sh2.Cells(rw, v2(i))
 r1.Copy r2
 r1.ClearContents
Next i
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

Sub Rectangle2_Click()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim rw As Long
    Dim r1 As Range
    Set sh1 = Worksheets("Fyldefejl")
    Set sh2 = Worksheets("Data")
    Set r1 = sh1.Range("E27:G27")
    Select Case sh1.Range("G27").Value
        Case "PX1", "PX2", "PX3"
            rw = sh2.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row
            r1.Copy sh2.Range("B" & rw)
        Case Else
            rw = sh2.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Row
            r1.Copy sh2.Range("F" & rw)
 
    End Select
    r1.ClearContents
End Sub

I removed the unnecessary arrays and loop.

 

[Morten Lahrmann]

Hey Andrew

Thank you so much! Works like a charm, and thanks for cleaning up the code

BR
Morten, Denmark