I have a column of part numbers that look like this:

What you "see" after
###-##-### format is applied: What's actually in the
cell:
555-11-222 55511222
544-11-222 54411222
533-22-111 53322111
not inventory not inventory
511-33-444 511-33-444

I am trying to set up code that automatically determines if a part
should be "Pulled" from stock or "Bought" from the supplier based on
available stock. If the "Qty Req" makes the "Min" stock value fall
below 90% of the minimum, I should "Buy". Or, if it is "not inventory"
it should immediately be flagged as "Buy" because...it obviously is not
in stock!

I can do that in code as long as the value in part number is composed
of numbers. For the "not inventory" parts, my code simply "skips" over
the value.

The entire sheet looks something like this:
part number stock min Qty Req Pull/Buy
555-11-222 5 6 2
544-11-222 8 5 2
533-22-111 5 3 2
not inventory 2

511-33-444 7 6 2

My code looks like this:
NOTE!!! I am using variables to step through the rows 1 by 1. So, the
code you see before you is part of a larger For/Next loop.


'Populate Pull/Buy Cells
Needed = Cells(RowNum, ColN5) - Cells(RowNum, ColN4)
If Needed < Cells(RowNum, ColN6) * 0.9 _
Or If Cells(RowNumMin, ColPN).Value = "not inventory" Then
Cells(RowNum, ColN7).Value = "Buy"
Else
Cells(RowNum, ColN7).Value = "Pull"
End If

The code above will simply skip over the "not inventory" parts and
leave the "Pull/Buy" cell blank. If I change "not inventory" to
something like 999-11-222 and look for that in the code...it works.
What is wrong?

The code is a little over my head, but basically I see your "not inventory"
cell as not qualifying as "Needed", so the whole section is skipped. I would
think you would need to somehow change your structure so the "not inventory"
cell would qualify as "needed" so the "If Needed" section would work

I know it's not much help but maybe will help you see something you didn't
before..

Vaya con Dios,
Chuck, CABGx3



"Brady" wrote:

I have a column of part numbers that look like this:

What you "see" after
###-##-### format is applied: What's actually in the
cell:
555-11-222 55511222
544-11-222 54411222
533-22-111 53322111
not inventory not inventory
511-33-444 511-33-444

I am trying to set up code that automatically determines if a part
should be "Pulled" from stock or "Bought" from the supplier based on
available stock. If the "Qty Req" makes the "Min" stock value fall
below 90% of the minimum, I should "Buy". Or, if it is "not inventory"
it should immediately be flagged as "Buy" because...it obviously is not
in stock!

I can do that in code as long as the value in part number is composed
of numbers. For the "not inventory" parts, my code simply "skips" over
the value.

The entire sheet looks something like this:
part number stock min Qty Req Pull/Buy
555-11-222 5 6 2
544-11-222 8 5 2
533-22-111 5 3 2
not inventory 2

511-33-444 7 6 2

My code looks like this:
NOTE!!! I am using variables to step through the rows 1 by 1. So, the
code you see before you is part of a larger For/Next loop.


'Populate Pull/Buy Cells
Needed = Cells(RowNum, ColN5) - Cells(RowNum, ColN4)
If Needed < Cells(RowNum, ColN6) * 0.9 _
Or If Cells(RowNumMin, ColPN).Value = "not inventory" Then
Cells(RowNum, ColN7).Value = "Buy"
Else
Cells(RowNum, ColN7).Value = "Pull"
End If

The code above will simply skip over the "not inventory" parts and
leave the "Pull/Buy" cell blank. If I change "not inventory" to
something like 999-11-222 and look for that in the code...it works.
What is wrong?

The "Needed" variable is not part of the "Or" if statement.

Secondly, I would first have to be able to read the value in the cells
marked with "not inventory"...which is exactly what I can't seem to do,
to determine some action for cells with that value.

This must be some kind of data mismatch and for the life of me I just
can't see it.

Thanks anyway for the response. I'll keep plugging away at it.


CLR wrote:
The code is a little over my head, but basically I see your "not inventory"
cell as not qualifying as "Needed", so the whole section is skipped. I would
think you would need to somehow change your structure so the "not inventory"
cell would qualify as "needed" so the "If Needed" section would work

I know it's not much help but maybe will help you see something you didn't
before..

Vaya con Dios,
Chuck, CABGx3

Sorry, my bad..........perhaps ElseIf would work better than OrIf.........

Vaya con Dios,
Chuck, CABGx3



"Brady" wrote:

The "Needed" variable is not part of the "Or" if statement.

Secondly, I would first have to be able to read the value in the cells
marked with "not inventory"...which is exactly what I can't seem to do,
to determine some action for cells with that value.

This must be some kind of data mismatch and for the life of me I just
can't see it.

Thanks anyway for the response. I'll keep plugging away at it.


CLR wrote:
The code is a little over my head, but basically I see your "not inventory"
cell as not qualifying as "Needed", so the whole section is skipped. I would
think you would need to somehow change your structure so the "not inventory"
cell would qualify as "needed" so the "If Needed" section would work

I know it's not much help but maybe will help you see something you didn't
before..

Vaya con Dios,
Chuck, CABGx3