Function Won't Recalculate

Hi all, used Matlab years ago in school, but have since lost a lot (pretty much all) of my programming memory. Trying to write a basic function in VBA to do the following:

I have two things to keep track of in a running inventory - model numbers, and work order numbers. Work order numbers are the same prefix for similar model numbers, but everytime another model number is added to the list, but already exists on the list, the suffix increments, ie.

model number H1 added - Work Order Suffix -> 0
model number H4 added - Work Order Suffix -> 0
model number H1 added - Work Order Suffix -> 1
model number H3 added - Work Order Suffix -> 0
model number H1 added - Work Order Suffix -> 2

I wrote the following code by just fiddling around, trying to remember things:

Function Test(y)
x = 0
For Counter = 1 To ActiveCell.Row - 1
If Cells(Counter, 1) = y Then x = x + 1
Next Counter
Test = x
End Function

Where my intent was for the the function to search through all cells above the current cell, looking for duplicates of the current cell. It would count how many duplicates there are, and returns that count as the "Work Order Suffix".

The code works as you enter new data into the list, HOWEVER, if you go and change a model number in the middle of the list, everything does not adjust to reflect that change. If I go into each cell afterwords and just hit enter again, it recalculates and everything is fine, so I would assume the problem is that the function calculates once, and then sits and does nothing. I see that "Application.Volatile" is used to make a function recalculate everytime anything in the sheet is done, but it doesn't seem to work for me. Nor does hitting "Shift - F9", which I thought should recalculate everything for me.

Again, I remember very little from my previous programming, so I am as green as possible, I imagine this is a simple problem, but I can't figure it out.

Thanks for any help!

EDIT - Starting to think maybe it is my code that is wrong. When I have Application.Volatile in the function, it does update everytime something is changed, but it seems to sum up all instances in the whole list of duplicates and puts that count as the Suffix for all duplicates.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories