Problem with Concatenateif function

[RobSA]

Hi Folks,

I am having a problem with the concatenateif function.

I have highlighted the problem in red fill in the DTS worksheet.

The idea is that upon obtaining a value in a column the number reflected in column b of the LOG worksheet must be listed.

The problem is that the number of time the value is found is correct however the concatenate function only does some of the work, that is only list some of the numbers and in one instance does not list the number.

Your help here would be appreciated

 

[Nebu]

Hi:

Your file is corrupted could not open at my end.

Thanks

 

[RobSA]

Thank you for your reply - new file is uploaded

 

[Hui]

The formula in the Concatenateif function is
If CriteriaRange.Cells(i).Value >= lowerval And CriteriaRange.Cells(i).Value < higherval Then

Cell H14 shows the value of 017 which is the only cell from the Log sheet that matches the criteria >= 191 and <200

What should it be if this is wrong?

 

[RobSA]

Hi Hui,

It should be showing the value 017 and 020

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Hui]

20 isn't included as the formula in the Macro is less than the Higher values
The formula in the Concatenateif function is
If CriteriaRange.Cells(i).Value >= lowerval And CriteriaRange.Cells(i).Value < higherval Then

If you want to include the upper limit change the formula to :
If CriteriaRange.Cells(i).Value >= lowerval And CriteriaRange.Cells(i).Value <= higherval Then

 

[RobSA]

Ah ha - thank you.

 

[RobSA]

Hi Hui,

The correction has worked well for the upper table, however the lower table now has duplications.

Can you assist?

 

[Nebu]

Hi:

Your code is very simple it will loop through DTS Chart tab and pick up the days between column (column C is your lower limit and column D is your upper limit) and check for a value in the LOG tab column that false between your lower and upper limit. Your original code included your lower limit but excluded your upper. But when you included<=in your logical statement it considered your upper limit as well. As per my understanding your code is doing what it is suppose to do. The lower table maybe giving duplicates because in your code you are not asking to remove any duplicates, you may have to remove duplicates from your base data if you want to get results without a duplicate.

Thanks