Please help.. i cant figure out what am i doing wrong.
Please Login or Register to view this content.
Please help.. i cant figure out what am i doing wrong.
Please Login or Register to view this content.
Last edited by Paul; 03-28-2012 at 04:37 PM. Reason: Added CODE tags for new user. Please do so yourself in the future.
Hi Dinky,
I'm guessing it's because you don't have an "=" in front of your formula, nor proper quotation marks, and your iLastRow variable isn't spelled the same each time it is used. You may want to try:
Please Login or Register to view this content.
Thank You!!!
Now it gives me "#Name" error..
Are your sheet names exactly "Simon Malls" and "Travelex - Trans Report"? Even one incorrect character or space could generate that error. If they are the same, please upload a copy of your workbook (less any confidential data).
I am not able to upload my EXCEL FILE..
THANK YOU FOR YOUR HELP!!!
PHP Code:
Sub TranscationCount()
Dim iLastRow As Long
Dim strFormula As Variant
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Simon Malls").Select
Range("D2").Select
strFormula = "=SUMPRODUCT((Travelex - Trans Report!$A$2:$A$" & iLastRow & "=A2)*(Travelex - Trans Report!$B$2:$B$" & _
iLastRow & "=200)*(Travelex - Trans Report!$D$2:$D$" & iLastRow & ">0))"
Range("D2:D" & iLastRow).Formula = strFormula
End Sub
Hi Dinky,
I somehow overlooked the fact that your worksheet name has spaces in it. When there are spaces in the name, you have to put apostrophes around it in VBA and worksheet functions otherwise it doesn't get recognized. I also adjusted the code slightly. Try the code shown below:
Please Login or Register to view this content.
Not it gives 0
Last edited by dinky; 03-29-2012 at 02:33 PM.
It works with the test file I built to test the code. Which only means one thing...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks