Cell value not recognized by code.

[Brady]

I am trying to populate a cell via vba based on the value of another
cell(s).

For example, let's say:
A1 contains a current inventory level of a part (say 12)
B1 contains a minimum required level in inventory (say 10)
C1 contains how many of a certain part I need (say 3)

I have code that determines if A1-C1 is less than B1. If so, then D1 is
populated with "Buy". If not, then the cell is populated with "Pull".

My code works fine for this. So far so good.

Here is the problem:
I want to be able to flag any part labeled as "not inventory" in cell
E1 as "Buy" as there is no data for the "in stock" or "minimum stock"
values.

So, here is my code:
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 problem:
When I added this line --> Or If Cells(RowNumMin, ColPN).Value = "not
inventory"
The portion of code that fills in "Buy" stops working. It simply no
longer puts the value of "Buy" in ANY cell.

If I take out the "Or..." code, it works fine.

The part numbers are values that look like this 555-11-222 via a custom
format. The "actual" value in the cell is 55511222. And of course there
are several with the text "not inventory".

If I change all of the "not inventory" cells to something like 99911222
and then change my "Or..." code to this:

Or If Left(Cells(RowNumMin, ColPN).Value,1) <> 5

Then the code works fine and I get "Buy" in the appropriate cells.

Why can't my original code "see" the "not inventory" in the cells?????

I should also note that I put in a message box show what the code
thought was in Cells(RowNumMin, ColPN) and it completely skips "not
inventory" cells!

 

[Ian]

Not tested, but try this. I'm not sure if you can use OR in this way, so an
alternative would be as follows

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

 

[Brady]

Ian,

I tried the ElseIf code below and its a no go also. It simply
"skips" the "not inventory" cells completely and leaves the "PULL/BUY"
cell empty. It does go ahead and perform the the PULL/BUY fills so, I
am one step ahead (many thanks).

I just can't figure out why it cannot recognize the "not inventory".
I checked the formatting of the cell thinking maybe that was it. The
formatting of the part number is Custom in this fashion ###-##-###.
Thus, a number of 55511222 is "diced" up nicely. However, "not
inventory" can't be formatted like that. Could that in some way be
causing my problem? AHHHHHHHHH!!!!!!!!!!!!!!!!!!!!

Lost.

 

[Sandy Mann]

Brady,

As Ian said you can't use IF like that, in fact my XL 97 highlighted that
line in red indicating a syntax error

I altered your code by simply removing the second IF and it worked for me.
It is, however, case sensitive and so, unlike a worksheet function it would
not find
"Not Inventory" so I wrapped it in UCase and, in case the user adds a
trailing space, I added a Trim as well:

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

Note that neither the UCase nor the Trim changes the entry in the worksheet.

Personally when I was at work, (I retired on Monday), I often unsed event
code to change, say, a user entry of "NI" into "Not Inventory" to ensure
that users were not making errors in entering data into the cell when I was
going to searching for an exact entry.
--
HTH

Sandy
Retired from Work
But hopefully not from Life

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Brady]

Hi there Sandy,

Nice to meet a fellow Mann.

I changed the code as you suggested below (I had already tried it
albeit without the Trim statement). It still does not work. It simply
skips any cells with "not inventory".

I don't get it.

Thanks for the reply and best wishes on your new life!

 

[Sandy Mann]

Hi Brady,

Nice to meet a fellow Mann.

I traced my family tree back to 1833 - no money in my side of the family,
perhaps it is all on your side <g>

I assume that you are indexing RowNum in you For/Next loop. If so how is
RowNumMin being indexed?

When I get into situations like yours I try breaking it down like, if I know
that I have "not inventory" in Cell E5 I try a piece of test macro like:

Sub Tester()
If UCase(Trim(Cells(5, 5).Value)) = "NOT INVENTORY" Then MsgBox "Found
it!"
End Sub

If you get the messagebox then you know that you do not have matching
problem so it must be a reference problem. So either RowNumMin or ColPN
must be pointing the code to a different cell

--
HTH

Sandy
Retired from Work
But hopefully not from Life

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Brady]

Ha ha...money on this side...surely you jest.

Sandy,

I had put in this code earlier to do exactly what you said:

MsgBox RowNumMin & "' " & ColPN

It displayed the exact row and column I wanted to look in. Now you
see why I'm so perplexed.

I should give more info. Before I do this test and input Pull or
Buy, I've sorted by part number and subtotaled the quantity of each
part. Thus, my list contains part numbers in the part number column AND
just below the last specific part number, a new row with "part number"
- SubTotal (basic Excel stuff).

I use RowNum in a for/next loop that starts at 1 and goes to the
"Lastrow" (found in another sub-routine). Within the for/next loop
there is an if statement that looks for any row with a formula in the
qty (i.e. the subtotal row).

Once I find a subtotal row, I know that the original part number
(sans the "-SubTotal") is the row above it. Thus, RowNumMin is defined
as RowNum -1.

So why can't it "See" the "not inventory"? Drat it all.

 

[Sandy Mann]

Sorry Brady but I can't follow exactly what it is you are trying to do and
there seem to be inconsistencies in what you are saying. For example you
say:

I use RowNum in a for/next loop that starts at 1

If RowNum starts at 1 and

(sans the "-SubTotal") is the row above it. Thus, RowNumMin is defined
as RowNum -1.

Then RowNumMin must start off at zero so your code line:

Or Cells(RowNumMin, ColPN).Value = "not inventory" Then

should throw an error because there is no Row zero. Unless you are finding
RowNumMin in a different counting loop.

Do you want to send me a sample of your spreadsheet to see what is going on?

If so change me address as it says below.


--
Regards,


Sandy
Retired from Work
But hopefully not from Life

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Brady]

It's always difficult to convey thoughts via text. I'll see if I can
pair down the spreadsheet and send it to you. That is a very kind
offer.

In the meantime, some answers below :

Sorry Brady but I can't follow exactly what it is you are trying to do and
there seem to be inconsistencies in what you are saying. For example you
say:



RowNumMin in a different counting loop.

First, RowNum and RowNumMin are in the same counting loop. Thus, if
RowNum was 1 then indeed, RowNumMin WOULD throw an error...BUT ONLY, if
the code using RowNumMin were not wrapped in the If statement below:

For i = 1 to "the end"
If (somecell identified by i).hasformula Then
(some code) using (somecell identified by i) using RowNum and
RowNumMin
endif
Next i

Remember, I am only looking to perform an operation on rows that have a
"formula" (the subtotal formula) in the qty column. Thus, since the top
row (row 1) is never a formula...I never get an error for RowNumMin
being "0".

I should probably change the i value to start at 2 but...I'll get to
that later.

Does it make more sense to you now?

Thanks!