countif

[sunshine076]

I am somewhat new to using VBA and I have used the basic countif function but would like to use VBA and have it store the answer in a different cell without the msgbox. I plan to use this information later. Any suggestions would be helpful.

Here is what I am presently trying to use.

Option Explicit
result As Integer
ce = y
CN = n
application.WorksheetFunction.countif(range("F4:F48"),ce.value)

this keeps erroring out after the range and then from there I would like the result to show up in a cell for a future count.

 

[JPearson]

Option Explicit
result As Integer
ce = y
CN = n
application.WorksheetFunction.countif(range("F4:F48"),ce.value)

this keeps erroring out after the range and then from there I would like the result to show up in a cell for a future count.

The code should look something like this.
Set the Range you would like and the Cells(Row,Column) tells the function where to put the value.

Dim ce as string
Dim r As Range

Set r = Range("F4", "F48")
ce = "y"

Cells(1, 2).Value = Application.WorksheetFunction.CountIf(r, ce)

 

[sunshine076]

cells (1,2) is this row 1 column B?

 

[JPearson]

Yes that is how the Cells(r,c) works.

 

[sunshine076]

I was able to see how that worked one other question can I define a cell location such as this

Dim a As Integer
Cells(1, 2).Value = a

and have the answer show in the cell. I will have multiple columns and will be used to calculate a percentage

 

[JPearson]

That format will not define a location.

It will make the cell in row 1 column 2 is B1 = whatever a is. If a = 6 then B1 cell will display 6.

 

[sunshine076]

I figured this out.

 

[sunshine076]

Option Explicit
Private Sub process()
Dim ce As String
Dim AA As Range
Dim AB As Range
Dim AC As Range
Dim AD As Range
Dim AE As Range
Dim AF As Range
Dim AG As Range
Dim AH As Range
Dim d As Integer
Dim e As Integer
Dim f As Integer
Dim g As Integer
Dim h As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer

Set AA = Range("D1", "D48")
Set AB = Range("E1", "E48")
Set AC = Range("F1", "F48")
Set AD = Range("G1", "G48")
Set AE = Range("H1", "H48")
Set AF = Range("I1", "I48")
Set AG = Range("J1", "J48")
Set AH = Range("K1", "K48")
ce = "y"
d = Application.WorksheetFunction.CountIf(AA, ce)
e = Application.WorksheetFunction.CountIf(AB, ce)
f = Application.WorksheetFunction.CountIf(AC, ce)
g = Application.WorksheetFunction.CountIf(AD, ce)
h = Application.WorksheetFunction.CountIf(AE, ce)
i = Application.WorksheetFunction.CountIf(AF, ce)
j = Application.WorksheetFunction.CountIf(AG, ce)
k = Application.WorksheetFunction.CountIf(AH, ce)
Cells(50, 4).Value = d
Cells(50, 5).Value = e
Cells(50, 6).Value = f
Cells(50, 7).Value = g
Cells(50, 8).Value = h
Cells(50, 9).Value = i
Cells(50, 10).Value = j
Cells(50, 11).Value = k

End Sub



This is what I have so far. I have multiple columns of y/n is this a feasible way of doing this or an easier way instead of being repetitious

 

[JPearson]

Yes there is an easier way.

Private Sub process()
Dim ce As String
Dim AA As Range
Dim d As Integer
Range("D1").Select
d = 4
ce = "y"
 
Do
    Set AA = Range(Cells(1, d), Cells(48, d))
    Cells(50, d).Value = Application.WorksheetFunction.CountIf(AA, ce)
    ActiveCell.Offset(0, 1).Select
 
    d = d + 1
Loop Until d = 12
set AA= Nothing
 
End Sub

This gives you a lot of flexibility in change as well. Want it to run past row K. Change the Loop Until d=45.
Need to increase the data set change the second Cells(48,d) to Cells(60,d).

Since you are repeating a process you set one process up and create varibles for the items that are changing. From there you just have to decide how to manipulate those variables each time you would like the process to change.

 

[sunshine076]

Private Sub CBOk_Click()
Dim tbentry As Long
Sheets("ToolChange").Range("C3").Value = tbentry.Value
tbentry.SetFocus
tbentry = Cells(3, 3)
End Sub

I am receiving an error for tbentry. value as an invalid identifier.

 

[JPearson]

Couple things I see here.

1. tbentry does not need the .value after it since its a long variable

2. You need to set the tbentry equal to something first. Its a non existant value so it has nothing to put into the specified range.

3. You do have tbentry at the end setting it to something.

4. The syntax does not work like thie for tbentry.

The code seems to go in a circle.

What exactly are you trying to accomplish?

Also try to use the macro recorder if you can manually do what you want. This will give you an idea of how thing should look like but the macro recorder tends to include excess items not needed.

 

[Brianwarnock]

I am somewhat new to using VBA and I have used the basic countif function but would like to use VBA and have it store the answer in a different cell without the msgbox. I plan to use this information later. Any suggestions would be helpful.

Here is what I am presently trying to use.

Option Explicit
result As Integer
ce = y
CN = n
application.WorksheetFunction.countif(range("F4:F48"),ce.value)

this keeps erroring out after the range and then from there I would like the result to show up in a cell for a future count.

I cannot understand why you cannot just put the Countif in the Cell where you want the answer.

Brian

 

[sunshine076]

What I am doing is I have a form with a text box called tbentry. I want this value with the sub CBOk_click() to load the valule from the textbox and go into the sheet called ToolSheet into cell C3

 

[sunshine076]

Update this is what I came up with and it worked
Private Sub CBOk_Click()
tbentry = tbentry
Sheets("ToolChange").Range("C3").Value = tbentry.Text
tbentry.SetFocus
tbentry = Cells(3, 3)
End Sub

However, the value and form still remains active so I will need to close the form and erase the entry right?

 

[sunshine076]

What I am also trying to do is learn VBA but here and there I seem to get stuck

 

[JPearson]

Private Sub CBOk_Click()
 
'place value in the txt box into a worksheet cell.
Sheets("ToolChange").Range("C3").Value = me.tbentry.value
 
'can't close so we hide it from view
 
me.hide
 
'clear the txt box in the form
me.tbentry.value = ""
 
end sub

 

[sunshine076]

Thank you it worked