CODEWITHSUNDEEP
vbaexcelexcel-2010
0m3r
0m3r

Reputation: 12499

how to add a value to cell

I am using the spreadsheet to do inventory- scanning parts in bin I am trying to add a A2 Value next to the (Target.Row, 5)(row 6)

how do I make A2 or A9 or the next scan Value show up on Column F so if column E & F don't match I know the part is sitting on the wrong bin

I have tried .Value = Target.Worksheet.Cells(Target.Row, 1).Value but not working

enter image description here

Here is my complete code 

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Cells.Count > 1 Or IsEmpty(Target) Or Target.Column <> 1 Then Exit Sub

    If Not SheetExists("WarehouseInventory") Then Exit Sub

    Dim Result As Variant
    Set Result = Sheets("WarehouseInventory").Cells.Range("E:E").Find(Target)

    If Result Is Nothing Then
       Target.Worksheet.Cells(Target.Row, 2) = "Data New Bin"
    Else
        Target.Worksheet.Cells(Target.Row, 2) = Result.Worksheet.Cells(Result.Row, 4)
        Target.Worksheet.Cells(Target.Row, 3) = Result.Worksheet.Cells(Result.Row, 5)
        Target.Worksheet.Cells(Target.Row, 4) = Result.Worksheet.Cells(Result.Row, 6)
        Target.Worksheet.Cells(Target.Row, 5) = Result.Worksheet.Cells(Result.Row, 7)
    End If

End Sub

Public Function SheetExists(SheetName As String) As Boolean
    Dim ws As Worksheet
    SheetExists = False

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = SheetName Then SheetExists = True
    Next ws
End Function

Upvotes: 0

Views: 1393

Answers (2)

Vasily
Vasily

Reputation: 5782

this an example how you can achieve required result

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x&, y&
If Target.Column = 6 Then
    y = 5: x = Target.Row
    While Cells(x, y) <> "": x = x - 1: Wend
    If Cells(Target.Row, 5) <> "" Then Cells(Target.Row, 6) = Cells(x, 1)
End If
End Sub

output

enter image description here

UPDATE

here your updated code, tested, works fine, insert it into "Cycle Count" sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x&, y&, Result As Range, ws As Worksheet
    Application.EnableEvents = 0
    If Target.Count > 1 Then
        Application.EnableEvents = 1
        Exit Sub
    End If
    If Not Intersect(Target, [A1:A99999]) Is Nothing Then
        With ThisWorkbook
            For Each ws In .Worksheets
                If ws.Name = "WarehouseInventory" Then
                    Set ws = .Worksheets("WarehouseInventory")
                    Set Result = ws.[E:E].Find(Target.Value)
                    Exit For
                End If
            Next ws
            If Result Is Nothing Then
                Target.Offset(, 1).Value = "Data New Bin"
                Application.EnableEvents = 1
                Exit Sub
            Else
                Target.Offset(, 1).Value = ws.Range(Result.Address).Offset(, -1).Value
                Target.Offset(, 2).Value = ws.Range(Result.Address).Value
                Target.Offset(, 3).Value = ws.Range(Result.Address).Offset(, 1).Value
                Target.Offset(, 4).Value = ws.Range(Result.Address).Offset(, 2).Value
                y = 5: x = Target.Row
                While .ActiveSheet.Cells(x, y) <> "": x = x - 1: Wend
                Target.Offset(, 5).Value = .ActiveSheet.Cells(x, 1).Value
            End If
        End With
    End If
    Application.EnableEvents = 1
End Sub

sample file here http://www.filedropper.com/book1_5

Upvotes: 1

Jonas Sveistrup S&#248;gaard
Jonas Sveistrup S&#248;gaard

Reputation: 23

What about Target.Worksheet.Cells(Target.Row, 6).Value = Target.Worksheet.Cells(Target.Row,1).Value ?

If you want only A2, in all rows substitute Target.Row with 2

Upvotes: 1

Related Questions