Count rows in range with VBA

[artz]

Hi,
I have a Sub with a large amount code but I'm stuck on one small section. There is a variable used in the code called DataValues.Count. In changing things around to change from a Function to a Sub, I somehow broke how this area of code was working.

The code below defines the input data range and works fine except for defining the variable named above. I've tried a dozen ways to define this variable and all return errors. Here is the code I am trying to use to define the variable DataValues.Count. The count is from I8 to the last used row in column I.

 EndRow = ActiveSheet.Range("I65536").End(xlUp).Row
 Set curRange = ActiveSheet.Range("I8:I" & Format(EndRow))
 DataValues.Count = ActiveSheet.Range("I8:I").End(xlDown).Rows.Count

Any help with fixing my code is greatly appreciated.

Thanks,

Art

 

[Micron]

Kind of vague there - the whole code would have been better. Based on what you posted, you can't have periods in a variable name.

 

[offthelip]

try

DataValues = ActiveSheet.Range("I8:I").End(xlDown).Rows.Count

 

[Micron]

Except the variable isn't named DataValues?

There is a variable used in the code called DataValues.Count

and no mention of what the error or message is.

 

[offthelip]

I think you need to post a bit more of the code, because it looks like datavalues is not a simple variable, how is datavalues defined??

 

[artz]

Except the variable isn't named DataValues?

and no mention of what the error or message is.

Remember that I started out with a Function that I am converting to a VBA sub:

Function Test(DataValues As Range, Length As Integer, Phase As Double, Shift As Integer)

The code to pass the input data range and variables to the Function was performed in the ButtonClick code:

Private Sub btnTest_Click()
Dim EndRow As Long
Dim curRange As Range
EndRow = ActiveSheet.Range("I65536").End(xlUp).Row
Set curRange = ActiveSheet.Range("I8:I" & Format(EndRow))
Call Test(curRange, Sheets("Sheet1").Range("N2").Value, Sheets("Sheet1").Range("P2").Value, Sheets("Sheet1").Range("Q2").Value, 0.5, 0, 1)
Set curRange = Nothing
End Sub

The variable DataValues appears in two places in the Function code. First at:

mcnt_bars = 0
mBars = DataValues.Count

And later at:

dPrice = DataValues.Cells(y + 1, 1).Value

In porting the code from Function to Sub, I did not get something right. The Function works fine but somehow the DataValues and DataValues.Count don't work the same in the Sub. Maybe someone can see what got lost in the translation. Thanks.

 

[Alex Blakenburg]

As Micron & offthelip said unless you show us we are just guessing. And yes the Dim statement would be a big help as would be the code that was working initially.
Here is another "guess"

Sub test()
 Dim DataValues As Range
 With ActiveSheet
    Set DataValues = .Range("I8:I" & .Range("I8").End(xlDown).Row)
 End With
    MsgBox "DateValues Count: " & DataValues.Count
End Sub

 

[Alex Blakenburg]

In the code either do a replace all of DataValues with curRange OR
assuming curRange is not used for other things, then the reverse replace all curRange wtih DataValues.
Having the word Range in there is helpful though since it makes it clear it is a range variable not a just Values, which is what has tripped you up.

 

[Micron]

Remember that I started out with a Function that I am converting to a VBA sub:

That's not really relevant. What's missing is the entire function which is the procedure that you're having difficulty with. That and still no info on the error(s)

and no mention of what the error or message is.

As I see it, you're trying to pass 7 parameters to a procedure that only accepts 4.
Call Test(curRange, Sheets("Sheet1").Range("N2").Value, Sheets("Sheet1").Range("P2").Value, Sheets("Sheet1").Range("Q2").Value, 0.5, 0, 1)
Function Test(DataValues As Range, Length As Integer, Phase As Double, Shift As Integer)

 

[artz]

Thanks for the post and code. Seems like I need an intermediate variable to get DataValues.Count into my sub. Maybe something like this:

Sub test()
 Dim DataValues As Range
 With ActiveSheet
    Set DataValues = .Range("I8:I" & .Range("I8").End(xlDown).Row)
 End With
    MsgBox "DataValues Count: " & DataValues.Count
    RowCount = DataValues.Count
End Sub

Or maybe I don't need to because DataValues is defined. Correct?

As Micron & offthelip said unless you show us we are just guessing. And yes the Dim statement would be a big help as would be the code that was working initially.
Here is another "guess"

Sub test()
 Dim DataValues As Range
 With ActiveSheet
    Set DataValues = .Range("I8:I" & .Range("I8").End(xlDown).Row)
 End With
    MsgBox "DateValues Count: " & DataValues.Count
End Sub