I'm trying to do either one of the formulas below in VBA using the Application.WorksheetFunction but am having difficulty with the asterisk as part of the worksheet function:
or
Please Login or Register to view this content.
I think the second formula, once changed over to VB code, might need Application.WorksheetFunction.FormulaArray() as well.Please Login or Register to view this content.
I'm not sure what you get by multiplying the column by itself that you don't get with just using Max(A:A). Regarding formula array, on a worksheet the first formula would be an array formula the sum product is fine, it doesn't need to be an array formula.
In my testing with your formula, both of these work in VBA.
If you are wanting to put the formula in a cell, then these work:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by skywriter; 08-30-2015 at 03:15 AM.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
Hi skywriter,
The two formulas are to find the last cell in column A that contains text or numeric value but not a formula. Column A may have gaps of rows of blank, some text, some numbers, or a combination of the two. Whichever is the lowest in the column, I'm trying to determine a row count for VB for a particular column.
In VB, I was using Application.WorksheetFunction. to determine this value and assign it to an integer variable in VB. That's where I had the problem with the asterisk as a multiplier for a worksheetfunction.
Try using Evaluate.
If posting code please use code tags, see here.
The two that I posted first in sub test are a shortcut for evaluate which is what Norie is suggesting.
They both seem to work fine. I put some data in some cells and tested them again.
skywriter, I tried using both of those codes separately but referencing a different sheet from the first/main sheet. It came up as an error. Your code works fine with text only but not numeric values if I reference it within the same sheet, so my guess is my referencing is bad when using a remote sheet range? I'd like to reference a remote sheet and check for last row with numeric or text but ignore formula cells. I guess a work around is just using a formula in Excel and reference it from VBA.
Please Login or Register to view this content.
Last edited by T86157; 09-02-2015 at 09:08 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks