Please evaluate this formula in vba

[GaryOfah]

Sub Try1()
'
Dim NextRow As Long:
Dim f1 As String
With ThisWorkbook.Worksheets("try"): NextRow = .Range("A" & .Rows.Count).End(xlUp).Row:
‘
f1 = Evaluate("=countif(“D" & NextRow -2 & ":" & "D" & NextRow & "," & "D" & NextRow)")
Range("F" & NextRow).Value = f1
End With
End Sub

Please!

 

[Jonmo1]

You have missplaced " and missing concatenate & symbol

Try

Sub Try1()
Dim NextRow As Long
Dim f1 As String
With ThisWorkbook.Worksheets("try")
    NextRow = .Range("A" & .Rows.Count).End(xlUp).Row
    f1 = Evaluate("=countif(D" & NextRow - 2 & ":" & "D" & NextRow & "," & "D" & NextRow & ")")
    Range("F" & NextRow).Value = f1
End With
End Sub

 

[GaryOfah]

Thank you very much!
I'm tired and can't know everything, yet!
Although it was pretty similar in excel.Ahhh...

What do you think...what will be better...to use this or the same worksheet function in vba, and why?

Surprisingly or not but I can't make neither to count first row or:

"D" & NextRow

Code constantly just skips that part, and I don't know why...
Then it counts only rows after that to atop and accordingly to number assigned.In this case -2.
(Maybe it is something with volatility or something...)

 

[Jonmo1]

Glad to help.

Generally speaking, I tend to think this way..
"If it can be done with a standard worksheet formula, then it probably should be done with a standard worksheet formula"
VBA is for doing things that you generally can't do with normal built in functions, and for automating frequent tasks.

 

[shg]

"If it can be done with a standard worksheet formula, then it probably should be done with a standard worksheet formula"

+1

It will almost always be faster as well.

 

[Rick Rothstein]

You have missplaced " and missing concatenate & symbol

Try

Sub Try1()
Dim NextRow As Long
Dim f1 As String
With ThisWorkbook.Worksheets("try")
    NextRow = .Range("A" & .Rows.Count).End(xlUp).Row
    f1 = Evaluate("=countif(D" & NextRow - 2 & ":" & "D" & NextRow & "," & "D" & NextRow & ")")
    Range("F" & NextRow).Value = f1
End With
End Sub

Another way to write the line of code I highlighted in red...

f1 = Application.CountIf(Cells(NextRow, "D").Offset(-2).Resize(3), Cells(NextRow, "D"))

Although to tell you the truth, I'm not 100% convinced either of our code lines are correct. The OP calculates his "NextRow" (I probably would have called it "LastRow" as that seems more appropriate) on a sheet named "try", but then calculates his 'f1' value on whatever the active sheet currently is and then outputs that value to a cell, again, on whatever the active sheet currently is. Given that the OP encased all of his code within a With/EndWith block, one has to wonder if 'f1's' calculation and the output cell should be located on the worksheet named "try"?

 

[GaryOfah]

Glad to help.

Generally speaking, I tend to think this way..
"If it can be done with a standard worksheet formula, then it probably should be done with a standard worksheet formula"
VBA is for doing things that you generally can't do with normal built in functions, and for automating frequent tasks.

I think only in vba...
Which method is better in vba and why?

No excel and formula.
Or this is your answer about that?

+1

It will almost always be faster as well.

Also...just to clarify...

 

[Jonmo1]

I think only in vba...
Which method is better in vba and why?

No excel and formula.
Or this is your answer about that?

Then I don't understand the question.

You've presented only 1 method,
So is this method better or worse than what?

 

[Jonmo1]

Although to tell you the truth, I'm not 100% convinced either of our code lines are correct. The OP calculates his "NextRow" (I probably would have called it "LastRow" as that seems more appropriate) on a sheet named "try", but then calculates his 'f1' value on whatever the active sheet currently is and then outputs that value to a cell, again, on whatever the active sheet currently is. Given that the OP encased all of his code within a With/EndWith block, one has to wonder if 'f1's' calculation and the output cell should be located on the worksheet named "try"?

Excellent point.

Although I believe this whole thread is just an exercise in learning, not really based on a real situation.
So the sheet try most likely is the active sheet, and the With structure was just thrown in there

 

[GaryOfah]

Ok, maybe it is wrong..lets see
Correct it will need ws or something in the code.

But I said, something is wrong with code because it doesn't calculate/countif first row.
But maybe this is because the result in this example goes as seen, in the same row!?
I don't know.