Related questions

Counting a Specific value within a column

2018-10-15 15:38 Brendan-John Martin imported from Stackoverflow

I have added a count line to my code to count how many times a specific value pops up and then to display it. The size of the column can vary (A huge amount)I have learnt recently a good alternative (Faster than doing an "For Each" loop) is to use the Find function but I can't see any reading material on using "Find" to count the value.

This is just a rough draft of my count code.

 Sub test()

     Dim rng As Range
     Dim cell As Range

        Set rng = Sheet1.Range("H2:H200")

        Count = 0

        For Each cell In rng
            If cell.Value = "VALUE" Then
            Count = Count + 1
        End If 
    Next cell

    Sheet1.Range("O3").Value = Count

    Count = 0

End Sub

can someone point me in the right direction?

Thank you.

1 answer

  • answered 2018-10-15 15:42 Damian

    Try this:

    Sub Counting()

    Dim LastRow As Long, Count As Long, rng As Range, sheet1 As Worksheet

    Set sheet1 = ThisWorkbook.Sheets("whateverursheetiscalled")
    LastRow = sheet1.Range("H2").End(xlDown).Row
    Set Range = sheet1.Range("H2:H" & LastRow)
    Count = Application.CountIf(rng, "VALUE")
    sheet1.Range("O3") = Count

End Sub

See also questions close to this topic

  • VBA: why I cannot use "=" to transfer data from one sheet to another sheet?

    I want to use "=" operator to transfer data from "Sheet1" to "Sheet2" because it is fast. However, the following code doesn't work: 

    Sheets("Sheet2").range(cells(1, 1),cells(1,5)).value = Sheets("Sheet1")
.range(cells(1, 1),cells(1,5)).value

    Why?

  • Delete hidden rows in auto-filter in coauthoring spreadsheet

    What I want to do is to reset the filter then delete all the rows below row 9.

    This should be a easy task, but this is a coauthoring spreadsheet, i want to avoid using ActiveSheet.ShowAllData as this will require all people to refresh the spreadsheet.

    I tried to use keybd_event to simulate ALT+A+C to clear the filter, but it dosen't quite work.

    keybd_event VK_Alt, 0, 0, 0
keybd_event VK_A, 0, 0, 0
keybd_event VK_A, 0, KEYEVENTF_KEYUP, 0
keybd_event VK_Alt, 0, KEYEVENTF_KEYUP, 0
keybd_event VK_C, 0, 0, 0
keybd_event VK_C, 0, KEYEVENTF_KEYUP, 0

Worksheets(1).Rows(9 & ":" & Worksheets(1).Rows.Count).Delete

    My thought is when the marco simulate alt+a+c, the hidden rows do not show until the whole macro is finished. So it only delete those visible rows, and after the macro finished, those hidden rows come out.

    Dose anyone know better ways to do it? Any help would be appreciated! Thanks!

  • Stop Excel Form Auto Convert Date Time To Number

    In column A I have the value of: A| and in column B I have 23/04/2019 23:48:17.

    I am merging both of the columns with the formula =A1&""&B1&, yet the result I am getting is A|43451.4670486111. However, this result does not meet my expectations as I need the date and time formatting preserved.

    Here is an example of the expected result: A|23/04/2019 23:48:17

    I have 380 column to merge, How to stop Excel from converting date & time to number?

  • How to play windows media player in VBA, starting from a spesific duration

    I am doing a project of video based analysis. I want to know how to play a windows media player in excel using vba, for a specific time, such as playing the video from duration 00.06 until 00.14. Because the current state that I have known is playing the video using vba code from 00.00 to the end of the duration.

    Thank you, your help will be much appreciated

  • VBA Highlight Cells that don't match master list in a column

    I'm having some trouble taking apart this code I found. I'm looking to error check a group of cells and I'd like to highlight the ones that don't match a Master list.

    For some reason I can't get this to highlight the cells that DON'T match the list.

    Please help me through this, I'd be very grateful!

    Dim myRange, v, f, a
Set myRange = Range("P12:P200")
For Each v In Workbooks("Formula_Weighup Audit Auto-Fill Final").Worksheets("Active Master List").Range("J2:J1054")
    Set f = myRange.Find(what:=v, lookat:=xlPart)
    If f Is Nothing Then
        a = f.Address
        Do
            f.Interior.ColorIndex = 5
            Set f = myRange.FindNext
        Loop While f.Address <> a
    End If
Next


MsgBox "Error: R&D #(s) do not exist" & vbNewLine & "(see highlighted items)"
        Exit Sub
  • Runtime error 1004 range assigned function

    I am doing operations on stock data, and I am attempting to assign functions to ranges.

    Sub RSIFunction()

Dim numberOrRowsToSkip As Integer: numberOFRowsToSkip = 70
Dim TrialsToExecute As Integer: TrialsToExecute = 127
Dim firstFormula As Range: Set firstFormula = Range("J154:J220")
Dim secondFormula As Range: Set secondFormula = Range("K154:K220")
Dim thirdFormula As Range: Set thirdFormula = Range("L154:L220")
Dim fourthFormula As Range: Set fourthFormula = Range("M167:M220")
Dim fifthFormula As Range: Set fifthFormula = Range("N167:N220")
Dim sixFormula As Range: Set sixFormula = Range("O167:O220")
Dim sevenFormula As Range: Set sevenFormula = Range("P167:P220")

Dim i As Integer

For i = 0 To TrialsToExecute

    firstFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _
        "=RC[-6]-R[-1]C[-6]"
    secondFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _
        "=IF(RC[-1]>0,RC[-1],"")"
    thirdFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _
        "=IF(RC[-2]<0,RC[-2],"")"
    fourthFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _
        "=(SUM(R[-13]C[-2]:RC[-2])/14)"
    fifthFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _
        "=(SUM(R[-13]C[-2]:RC[-2])/14)"
    sixFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _
        "=RC[-2]/RC[-1]"
    sevenFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _
        "=IF(RC[-2]=0,100,100-(100/(1+RC[-1])))"
Next i

End Sub

    Which returns:

    Runtime error '1004': Application-defined or Object-defined error

    After pressing Debug, this line is highlighted:

    secondFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-1]>0,RC[-1],"")

  • Replace countifs with advanced filter or other macro - guidance appreciated

    I have quite a heavy workbook. I am curious if any of you could advise how to best do it so that the processing speed isn't affected much.

    sheet Occupancy: See image

    TblOccupancy[code]
TblOccupancy[start]
TblOccupancy[end]
TblOccupancy[setting]

    The above is used on another sheet.

    Sheet Numbers:
    See image

    column B6:B370 contains dates
    range CODES_PASTETO which is c3:ie5 contains 3 criteria
    row 3 = setting
    row 5 = code

    I have created a formula that counts number of users of given code present on specific date.

    I was hoping you could help me converting this into macro [Excel-vba]

    and that it would in turn speed up processing and automation in general.

    This is what I use in c6:ie370: =IFERROR(IF($B6>Today,0,SUMPRODUCT((occupancy_code=Numbers!C$5)*(occupancy_startdate<=Numbers!$B6)*(occupancy_enddate="")*(occupancy_setting=Numbers!C$3))),"-")

    Thank you in advance. At the moment testing advanced filter, struggling with multi-criteria & between dates. I will comment if i solved this one. In the meantime sending hugs for anyone willing to help!