UtterAccess Discussion Forums

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

corrieann

Apr 23 2012, 02:06 PM

I need to automate the entry of a formula: =COUNTA(A2:A##)-1

This is what I have so far:

CODE

Sub CountA()
Dim r As Long, c As Integer
Dim maxR As Long, maxC As Integer

  maxR = .Rows.Count
  maxC = .Columns.Count

  For c = 1 To maxC
  Cells(c).Formula = "=counta(" & columnref & "2:" & columnref & maxR & ")-1"
  Next c

End Sub

I need to enter this formula in row 1 for every populated column. When I run this "as is" I get an error that reads: "Invalid or unqualifed reference." The .Rows is hightlighted in the VBE.

Ideas? Suggestions? Help?

ipisors

Apr 23 2012, 02:12 PM

I haven't tested this, so this is just air code, or air "thought", in this case, but...

It appears you may be trying to use syntax that is common to the inner part of a WITH statement, except you don't have a With statement.

Example:

With Worksheets("Sheet1")

.Range("A1").Value="Value"

End With

ipisors

Apr 23 2012, 02:14 PM

In fact, here is some additional idea if what I posted doesn't really 'get' you anywhere.

As much as I'm sort of against the UsedRange property, Perhaps you're wanting to do something more like:

Sub CountA()
Dim r As Long, c As Integer
Dim maxR As Long, maxC As Integer

With ActiveSheet.UsedRange
maxR = .Rows.Count
maxC = .Columns.Count
End With
For c = 1 To maxC
Cells©.Formula = "=counta(" & columnref & "2:" & columnref & maxR & ")-1"
Next c

End Sub

corrieann

Apr 23 2012, 03:06 PM

You were exactly right! Thanks.

One more thing...how do you grab the letter of the column? I found some articles online and came up with this:

CODE

Sub CountA()
Dim r As Long, c As Integer
Dim maxR As Long, maxC As Integer

With ActiveSheet.UsedRange
   maxR = .Rows.Count
   maxC = .Columns.Count
End With

For c = 1 To maxC
Cells(c).Formula = "=counta(" & getColLtr & "2:" & getColLtr & maxR & ")-1"

Next c

End Sub
-------------

Public Function getColLtr(intColNum As Integer) As String

Const sAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ": Dim iNum As Integer
If intColNum Mod 26 = 0 Then iNum = Int(intColNum / 27) Else iNum = Int(intColNum / 26)

If intColNum > 26 Then
getColLtr = Mid(sAlpha, iNum, 1) & Mid(sAlpha, intColNum - (26 * iNum), 1)
Else
getColLtr = Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", intColNum, 1)

End If
End Function

Which of course is not working. Ideas?

ipisors

Apr 23 2012, 03:17 PM

Hello again,

You just needed to add the argument at the end of the function. When there is a function that is built like this:

CODE

Function FunctionName (argument1 as DataType, argument2 as DataType)
....(more code)
End Function

That means that if you ever call that function elsewhere in your code, you must supply the argument, so maybe you would use it like this:

CODE

[some code] & FunctionName(argument) & [more code]

(or whatever)

Try:

CODE

Sub CountA()
Dim r As Long, c As Integer
Dim maxR As Long, maxC As Integer

With ActiveSheet.UsedRange
   maxR = .Rows.Count
   maxC = .Columns.Count
End With

For c = 1 To maxC
Cells(c).Formula = "=counta(" & getColLtr(Cells(c).Column) & "2:" & getColLtr(Cells(c).Column) & maxR & ")-1"

Next c

End Sub

Public Function getColLtr(intColNum As Integer) As String

Const sAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ": Dim iNum As Integer
If intColNum Mod 26 = 0 Then iNum = Int(intColNum / 27) Else iNum = Int(intColNum / 26)

If intColNum > 26 Then
getColLtr = Mid(sAlpha, iNum, 1) & Mid(sAlpha, intColNum - (26 * iNum), 1)
Else
getColLtr = Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", intColNum, 1)

End If

End Function

corrieann

Apr 23 2012, 03:22 PM

Yay!!! That was Perfect!!! And thanks for articulating (teaching). It really helps me along!

ipisors

Apr 23 2012, 03:23 PM

I am glad it worked for you! I have learned so much from UA responders providing that extra glimpse of explanation and I try to do the same. Hopefully I do it accurately LOL

Good luck with the project.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.