1

I have a group of selected cells that I need to run the formula (x-TRUNC(x)) on in VBA. I then need to COUNTIF them for every "3:00 AM". But I need the change to happen exactly where the selected cells are. So below in the picture, I need those to be affected by the equation but still be in the same spot. What would be the best way to go about this? Can it be done while still having them all selected? Maybe an Array?

I have to use the equation on them because of the way they get imported from the software I have to use. So I need to turn it into a form that allows me to run COUNTIF on.

The ultimate goal is that I just need the COUNTIF number for 3:00 AM for each selected group.

I'm not sure this is correct at all, but looking for something in this direction:

Dim counted As Integer
Dim myArray As Variant

Range(ActiveCell, ActiveCell.End(xlDown)).Select
myArray = Selection
For Each Item In myArray
    Item = Item - TRUNC(Item)
Next
counted = WorksheetFunction.CountIF(myArray, "3:00 AM")
Sheets("Report").Range("C" & x).Value = counted & " shifts"

Selected Cells

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user3040231
  • 19
  • 3
  • One line to trunc using your formula **(x-TRUNC(x))**. `[D3:D4] = [INDEX((D3:D4) - TRUNC(D3:D4),)]` Where `D3:D4` is your range. For a variable range you can use evaluate as shown [Here](https://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells/19986324#19986324) Similarly you can use Countif... – Siddharth Rout May 21 '20 at 07:29
  • So I tried your [D3:D4] = [INDEX((D3:D4) - TRUNC(D3:D4),)] code but I get either a #NAME? or #VALUE!. I tried the exact above code and also tried to replace the specific cells with Selection. – user3040231 May 21 '20 at 19:52

1 Answers1

0

This macro creates some random numbers, truncates them and then counts how many fractions are greater than 0.5, which is hopefully similar to what you wanted it to do!

Cells.Clear
Range("a1:a10").Formula = "= Rand() * 10"
Range("b1:b10").Formula = "=a1-TRUNC(a1)"
Range("c1").Formula = "=COUNTIF(b1:b10,"">0.5"")"
JMP
  • 4,417
  • 17
  • 30
  • 41