VBA Hey guys, I just started to work with VBA and i am looking for a solution to my problem. The task is to count the number of different Values (Suppliers) inside a column, starting at a fixed point, lets say for column A to start at A5, but end at the last value in that column. Every time i try to put the range from A5 to last written cell in column A I either get errors or a wrong answer. Anyone here that can help me out?
CodePudding user response:
Please, try the next way:
Sub testCountDefinedRange()
Dim sh As Worksheet, startRow As Long
Const searchVal As Double = 25.13 'what is it to be searched for
Set sh = ActiveSheet 'use here the sheet you need
startRow = 5 'the starting row of the range to be searched
Debug.Print WorksheetFunction.CountIf(sh.Range("A" & startRow, sh.Range("A" & sh.rows.count).End(xlUp)), searchVal)
End Sub
But according to community spirit, you should prove your involvement in solving the problem by your own. Even if you cannot show us a working piece of code/formula etc. Anything proving that you did something, at least, some research on the internet...
CodePudding user response:
Sub Count_Values_()
Sheets("Präsentation").Select
Dim dblAnz As Double
Dim rngRange As Range, rngRangeCnt As Range
Set rngRange = Range("A5:A50")
For Each rngRangeCnt In rngRange
dblAnz = dblAnz 1 / WorksheetFunction.CountIf(rngRange, rngRangeCnt.Text)
Next
Dim Bereich As Range
Dim Zelle As Range
Dim lAnzahl As Long
Set Bereich = Range("A5:A50")
For Each Zelle In Bereich
If Zelle.Font.Bold = True Then
lAnzahl = lAnzahl 1
End If
Next Zelle
Sheets("Anleitung").Select
Range("F1").Value = dblAnz - lAnzahl - 1
End Sub
This is the code I used before by searching values from A5 to A50 and subtracting all the bold headlines. I also added a -1 as an empty cell counts as a value as well I guess or at least I got the right numbers this way.