CODEWITHSUNDEEP
vbaexcel
Patrick
Patrick

Reputation: 75

VBA Insert value in a empty cell in a new row

Already googled like 3 or 4 hours but couldn't find anything that is working.

What I want to have and what is already done:

If meinWert > 16000000 And meinWert < 20000000 Then
        Sheets("Aufträge").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Date
        Sheets("Aufträge").Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = meinWert

meinWert is avalue from a Scanner input which is an order number. The code above is a part of something bigger that is already running. The result of the code above is this: enter image description here

And for each order there are some items (from 1 to like 10 items), now I need a code that jumps into row "C" and insert the item code. Like I said, there are more then one items per order and so each of this items (input via scanner) should be listed on the same line as the right order number like this: enter image description here

I thought about smth. like this Sheets("Aufträge").Cells(Target.Row, 255).End(xlToLeft).Offset(0, 1) but its not working.

Upvotes: 1

Views: 556

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

Instead of what you have, you should figure out the row once, and then use this row to insert all of the values into it:

Dim row As Long
Dim ws As Worksheet
Dim itemCount As Integer
Dim bolMoreItems As Integer

Set ws = Sheets("Aufträge")

If meinWert > 16000000 And meinWert < 20000000 Then
    bolMoreItems = True
    row = ws.Cells(Rows.Count, 1).End(xlUp).row + 1
    ws.Cells(row, 1) = Date
    ws.Cells(row, 2) = "test"

    itemCount = 1 'this isn't really needed except as a count to how many items there are.

    While bolMoreItems = True
        ws.Cells(row, 2 + itemCount) = "value" & itemCount 'instead of "value" & itemCount, put in whatever the value is.
         If there_are_no_more_items Then 'need some check to determine if there are more items....
             bolMoreItems = False
         End If

         itemCount = itemCount + 1

    Wend

End If

I've used a variable for the sheet to make it easier to read and code. Obviously, I don't know how many items there are, so you'd need to do something to figure it out. I also don't know what you're trying to insert into the new columns so leave this to you.

By the way, this wasn't working for you because Target is a keyword. I don't know what the rest of your code is doing, but this is used in Event Handlers to specify the cell that triggered the event. You likely have nothing like this, so this wouldn't work. You could do something like this, but instead it would look like:

 Sheets("Aufträge").Cells(Sheets("Aufträge").Cells(Rows.Count, 1).End(xlUp).Row, 255) _
                 .End(xlToLeft).Offset(0, 1)

where the _ is simply continuing the statement on the next line. This ends up not being clear what you are trying to do and is not the best solution anyways.

Upvotes: 1

Related Questions