Formula to follow addition of rows

Question

New to VBA, please help. My apologies. I have not done a good job of making myself clear. Let me try one more time.

My sales reps enter every call into a call sheet. They call on 50-60 people a week; some they will see more than once a week, some only a couple of times a year. On this call sheet are 4 columns; date of call, customer, numerical date, and days since last call. This sheet may have hundreds of rows, many are duplicate customers called on a different date.

I have written code that will eliminate duplicates as needed (works fine). New calls are added using NextRow=_ (also works fine). $C$2 is set at TODAY().

Formula in column C is $C10=$A10(Column C is formatted to number). Column D is number of days since last call; $C$2-$C10 etc. Simple and works fine.

Issue is that say I have 50 rows (all different customers) sorted ascending and a new customer is added, key being new. I need the formulas in C and D to drop down one row automatically when the new customer is added. I can drag the formulas down a head of time and everything will work until I sort, then my data is a the bottom of my sort because all rows in column A without a date will produce a 0 in both C and D. My finished product should be a range of different customers (no duplicates); with the customer that has not been called on the longest at the top.

I hope this is a better explanation. Can I write code to ignore the 0's?

Answer 1

I am going to go a little out on a limb here and say maybe your formulas need refactoring...

For instance. If the aim is to calculate the days since the last call was made to a customer, a simple formula such as this would work =(max(C:C)-Today()) This gets the largest value in column C and subtracts today from it. If you want to get the value in column D which corresponds to this entry then VLOOKUP() is your friend. you would use it as such: =VLOOKUP(MAX(C:C),C:D,2,FALSE)

Hope this helps. Incidentaly, the best way to do your problem in VBA, the simplest way would be to create a Named Range. You can then replace the $C$2-$D11 with the name of the named range. The simplest way to do this would be to say:

Range(Range(C2),Range(C2).End(xlDown)).Name = NAmeOfYourRange

This effectively just gets cell C2, goes to the last non blank cell in the downward direction and names that range NameOfYourRange

Hope this helps :)