Worksheet Function Syntax

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).

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").

Originally Posted by Jiptastic

I can't seem to get the syntax right on the worksheet functions. Any help?

Please Login or Register  to view this content.

You should try this:

Please Login or Register  to view this content.

I haven't had time to read through your recommended reading

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.

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.

For instance I could change the Sheets value to "Pasted Zip Codessssssss" , which should error, but it would still run.

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?

It always thought the 2 functions were not equal no matter what.

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?

Please Login or Register  to view this content.