Populate two columns with value from one cell on condition-CodePudding

I have a bit of a problem, due to a lack of coding knowledge. I want to create a simple inventory tracking workbook for locations in my shop. In Sheet 1 I would need a macro to separate values into Columns C and D, depending on what is written into the cell A1. Because of barcode scanner I locked all cells for selection, except A1 cell . What I would want is, if A1 values is in this format X-X-X (format for bin location, 1-1-1, 2-3-1, etc...) to copy to column C, and everything else into column D. I have list of bin locations, in sheet2, range A2:A if that could help in matching location values. I added in B1 formula to match scaned location with list location. And here is the code i tried but it won t work as i expect. Many thanks in advance

Worksheet example screen shot:

Sub razdvoji()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lclr As Long
Dim ldlr As Long




Set wsCopy = ThisWorkbook.Worksheets("Skeniranje")
Set wsDest = ThisWorkbook.Worksheets("Skeniranje")

lclr = wsCopy.Cells(wsCopy.Rows.Count, "C").End(xlUp).Row

ldlr = wsDest.Cells(wsDest.Rows.Count, "D").End(xlUp).Offset(1).Row


If Range("B1") = 0 Then
Range("A1").Copy
Range("D2" & ldlr).PasteSpecial xlPasteValues
End If

If Range("B1") > 0 Then
Range("A1").Copy
Range("C2" & lclr).PasteSpecial xlPasteValues
End If

End Sub

CodePudding user response：

Try this (goes in the worksheet code module):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v, v2, cEntry As Range
    
    Set cEntry = Me.Range("A1") 'monitoring this cell only
    If Target.Address <> cEntry.Address Then Exit Sub
    
    v = Trim(cEntry.Value)
    If Len(v) = 0 Then Exit Sub 'cell is empty
    
    v2 = Replace(v, "-", "") 'remove dashes
    
    
    With Me.Cells(Rows.Count, "D").End(xlUp)
        'decide where to put the entry
        If v Like "#*#" And IsNumeric(v2) Then
            'likely location
            .Offset(1, -1).NumberFormat = "@" 'format as text
            .Offset(1, -1).Value = v
        Else
            'box id
            .Offset(1, 0).Value = v
        End If
    End With
    'prep for next entry
    cEntry.ClearContents
    cEntry.Select
End Sub