VBA Code Needed: Variable Formula

[JohnnyAngel]

I need a "Dim LR As Long" type of vba code that will change the following formula as information in certain row are variable.

For example, if the data on the sheet ranges from A1:A20 and then it changes from A1:A30, I need the code to change the range of the following formula when the range changes: <!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> <o:TargetScreenSize>1024x768</o:TargetScreenSize> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->[FONT=&quot]

=COUNTBLANK('List1'!AF2:AF2936)"

Thank you.




[/FONT]

 

[njimack]

use dynamic ranges - http://www.contextures.com/xlNames01.html#Dynamic

 

[JohnnyAngel]

The following 2 formulas I created using the dynamic ranged method.

=OFFSET('1List'!$A$2,0,0, COUNTA('1List'! $A:$A),1)

=OFFSET('1List'!$A$2,0,0,COUNTBLANK('1List'!$AF488:$AF970),1)


The OFFSET/COUNTA formulas is working fine from what I can see.

However, the COUNTBLANK is counting everything blank on the sheet.

How do I narrow the OFFSET/COUNTBLANK formula down to only count that blank cells in a certain column AND for it to expand the formula as data is added to a specific column range.

Please advise.

 

[njimack]

I don't understand what you're trying to do - your OFFSET starts from A2 but you're counting blanks in the range AF488:AF970?

 

[JohnnyAngel]

Yeah I wasn't sure if the formula was correct.

Basically, what I need it to do for instance is count the blanks in AF2:AF500.

Then, I need the countblank formula to expand or retract as data is added or deducted on any cells after AF500. I want the information to be returned into one cell for statistical purposes.

Let me know.

 

[njimack]

Something like this perhaps
=OFFSET('1List'!$A$2,0,0,COUNTBLANK('1List'!$AF$2:$AF$500)+COUNTA('1List'!$AF$501:$AF$65536),1)

 

[JohnnyAngel]

Not exactly. I need two separate formulas. One formula to calculate the blank cells and the other to calculate the populated cells.

I need to be able to paste each formula into different cells to view clearly how many cells are blank in a range and how many cells are populated in a range. And again, I need both formulas to expand or retract as data is added or removed.

 

[njimack]

I'm struggling to understand - are you trying to create a dynamic range using OFFSET or a formula in a cell to count populated/blank cells?

Can you post a sample of your data with expected result?

 

[JohnnyAngel]

I'm trying to create a dynamic range using OFFSET formulas that defines the range.

I've entered the following formula that defines a dynamic range in the "Refers To" Box
=OFFSET('List1'!$AF$2,0,0,COUNTA('List1'!$AF:$AF),1) and it works and does want is supposed to which is count that number of cells with data in them and it automatically recalculates as information is added or deleted within a specific range.

However, when I use the same method but use the COUNTBLANK formula in the dynamic range it doesn't count the blank cells for a specific range, instead, it counts all the cells from AF2 Through AF65536.

What I would like to know is if the dynamic range method using the OFFSET to define the range can be modified in such a way that it will count the blank cells in a specific range rather than an entire range automatically. Right now, it doesn't seem possible because all the other formulas that I have entered in the Dynamic range are working as I want them to.

What I have had to do as an alternative for now is have a dynamic range defined where it counts the number of cells with data in them and another that counts the total number. Then, I enter a formula in a different cell the takes the total count minus cells that are populated to get the total count of blank cells for my stats.

Hope this helps. I'm not sure how to post an example spreadsheet in this thread. I've seen it done but unsure how.

 

[JohnnyAngel]

Any MVPs available to try to solve the last thread on this one. Please read the most recent thread from June 13, 2011 and let me now the solution.