I can't seem to get the syntax right on the worksheet functions. Any help?
Please Login or Register to view this content.
I can't seem to get the syntax right on the worksheet functions. Any help?
Please Login or Register to view this content.
What's C[2] ?
I don't know. That's the way the macro recorder records the function when I insert it into a cell. I think it's just column B
C[2] is R1C1 notation for "the entire column two columns to the right of the cell containing the reference."
This ActiveSheet.Range("B:B") is a vaild VBA range object referring to column B of the active sheet and should work well as the argument for the sum function.
This 'Pasted Zip Codes'!C[2] has no meaning in VBA (it is a valid R1C1 reference in the spreadsheet, but we are in VBA).
My guess is, that the COUNTA() function is returning an error because it is getting a meaningless argument. You probably need to figure out what range you want the count of, then formulate an appropriate reference (for VBA) for that range (syntax similar to the argument used in the sum function).
Originally Posted by shg
I've never had to write VBA for a function referring to another sheet. This is my best guess. Any other suggestions?
worksheetfunction.COUNTA('Pasted Zip Codes'!Range("B:B"))
This might be a good read to understand how to return an individual item from a collection http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx The example they use early on is Worksheets("Sheet1").Range("A5").Value = _
Worksheets("Sheet1").Range("A1").Value. So you can use the worksheets(identifier) to return the desired spreadsheet, then use the range(identifier) to return the desired range. In your case this might look like worksheets("pasted zip codes").range("B:B").
You should try this:
Please Login or Register to view this content.
Last edited by hafizimran; 02-21-2013 at 04:54 AM.
Hafizimran, I tried the code below. It didn't error, which made me excited; but it also didn't seem to do anything. For instance I could change the Sheets value to "Pasted Zip Codessssssss" , which should error, but it would still run. It always thought the 2 functions were not equal no matter what.
Mr. Shorty, this would be my best guess testing your suggestion. I haven't had time to read through your recommended reading, since I'm at work, but this code returns the error "Argument not optional"Please Login or Register to view this content.
Please Login or Register to view this content.
I would suggest that you read it. I think one of the most helpful skills I picked up early on was how to search, read, and understand the help files the MS provides. I've found them very helpful in learning Excel and VBA. With that in mind, here's some additional reading http://msdn.microsoft.com/en-us/libr.../ff197608.aspx Note in MS's examples, as well as your own use of the sum() function, that the arguments for a function are enclosed in parentheses, not offset by periods.I haven't had time to read through your recommended reading
It is interesting to me that hafizimran's statement did not work for you. Syntactically it looks correct. It suggests to me that something else from elsewhere in the routine is not correct either. Which means it may be time to learn and apply some additional debugging skills.
Is there any error trapping (like an On error resume next statement)? If you enter break mode and step through the code, does it ever actually execute this If statement?For instance I could change the Sheets value to "Pasted Zip Codessssssss" , which should error, but it would still run.
Did you try a scenario where you adjusted the spreadsheet values so that you knew the sum and counta functions would return equal results? If you set a watch for these two expressions, are they showing results that you expect, or something unexpected? If you execute a debug.print statement for each expression, do you get appropriate or expected results in the immediate window?It always thought the 2 functions were not equal no matter what.Please Login or Register to view this content.
And if you feel trouble using this code, you should upload your workbook if it doesn't contain your confidential data. you may upload with sample data otherwise so that I might be able to understand exactly what trouble you are facing.
I really appreciate the help. I think this has solved my problem. I'll remember to use parenthesis instead of periods with these functions. And MrShorty I did try some of the things you suggested. The Debug.Print option is a good idea, which I don't use much. I'll try to come back to the suggested reading when time allows. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks