VBA to check Range then move if true

[Emoncada]

I want to be able to Enter Data straight down Column A, then hit CmdButtonRun, have it check
if Left(A:A,3) = "1Z8", then I want to move that cell to right in Column B,

if Left(A:A,3) = A numberic value less then "100" then move right x3 to Column D,

if Left(A:A,2) = "TO" then Don't move anywhere

Else
if Left(A:A, 3) = Anything other then others Move to right x2 in Column C

I know this is a lot, but I just need a little direction on how to get it to look at the column Range and to the Left Properties.

Appreciate any help

[Bob Phillips]

Off the top



With Activesheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow

If Left$(.Cells(i, "A").Value ,3) = "1Z8" Then

.Cells(i, "B").Value = .Cells(i, "A").Value
.Cells(i, "A").Value = ""
ElseIf Val(Left$(.Cells(i, "A").Value ,3) < 100 Then

.Cells(i, "D").Value = .Cells(i, "A").Value
.Cells(i, "A").Value = ""
ElseIf Left$(.Cells(i, "A").Value ,2) <> "TO" Then

.Cells(i, "C").Value = .Cells(i, "A").Value
.Cells(i, "A").Value = ""
End If
Next i
End With

[Emoncada]

That looks almost perfect, but one problem.
the cells that start with "TO" have a numeric value also so it's moviing it to the "D" Column

Example
TO012341 instead of it staying in A, it's moving to D

[Bob Phillips]

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow

If Left$(.Cells(i, "A").Value, 3) = "1Z8" Then

.Cells(i, "B").Value = .Cells(i, "A").Value
.Cells(i, "A").Value = ""
ElseIf IsNumeric(Left$(.Cells(i, "A").Value, 3)) And Val(Left$(.Cells(i, "A").Value, 3)) < 100 Then

.Cells(i, "D").Value = .Cells(i, "A").Value
.Cells(i, "A").Value = ""
ElseIf Left$(.Cells(i, "A").Value, 2) <> "TO" Then

.Cells(i, "C").Value = .Cells(i, "A").Value
.Cells(i, "A").Value = ""
End If
Next i
End With

[Emoncada]

Great XLD, I just had a thought how difficult would it be to when it moves to the correct Column to have it move up to next available Cell?

[Bob Phillips]

sorry, can you explain what you mean?

[Emoncada]

no problem.

My Goal is to be able to capture data and organize it after.
So Column A should only have "TO's" Column B Should only have Tracking #'s they start with "1Z8" this can have multiple for 1 TO same for Item which goes in column C and qty in column D.
The Break would be when a new "TO" is placed in column A

This is how I would like for it to show.
Column A................B..................C................D
TO125415...........1Z81R51.........Monitor...........2
........................1Z81R53..........CABLE............4
.............................................MODEM...........1
TO122841...........1Z81R77.........MONITOR.........1
.........................1Z81R90.........PRINTER..........1

Etc.
Hope this helps

[Emoncada]

Unless you can think of an easier way to get this to do the samething without putting the data all in column A.
The reason it's like that is they all have barcodes so i can easily scan everything straight down the hard part is then organizing it.

[Bob Phillips]

I think, but am not sure, that I see what you want, but I am struggling to envisage the start data.

Can you post a good before and after example?