Beginner at VBA, getting a 424 error

[pingwong9r9r]

Hello,
I'm currently trying to write a macro which will accept user input for a range, where if only a single cell is selected, then the entire corresponding row to that cell is considered as the user input range, but I keep getting a 424 error with the following code:

Sub Calculations()


Dim UserRange As Range
Dim Result As Integer


Set UserRange = Application.InputBox("Select a range", "Record Selection", Type:=8)


Result = UserRange.EntireRow.Count


If Result = 1 Then
    Set UserRange = UserRange.EntireRow.Select
    End If
'... Perform calculations with user range


End Sub

 

[Michael M]

Do you want this

Result = UserRange.EntireRow.Count

to count the number of used cells in the row ?
If so, why ??
What calculation are you going to perform on that row ??

 

[pingwong9r9r]

Hello Micheal,
Thanks for your response. I used Result as a way to check that the user has selected only a single cell. If the user has selected a single cell, I wanted to reassign my UserRange value to the corresponding row that the user selected cell is in, rather than the single cell. I am not sure if I've reassigned UserRange correctly inside of my If statement, using...

Set UserRange =  = UserRange.EntireRow.Select

After my If statement, I'm passing UserRange to various built in math operations, but am receiving either 424 or 1004 errors after fidgeting with UserRange... Here is my entire code.

Sub Calculations()


Dim UserRange As Range
Dim Result As Integer
Dim Avg As Double
Dim Sum As Double
Dim Lcm As Double
Dim Rms As Double


Set UserRange = Application.InputBox("Select a range", "Record Selection", Type:=8)
Result = UserRange.EntireRow.Count


If Result = 1 Then
    Set UserRange = UserRange.EntireRow.Select
End If


Avg = Application.WorksheetFunction.Average(UserRange)
Sum = Application.WorksheetFunction.Sum(UserRange)




Range("H1") = Avg
Range("H2") = Sum




MsgBox "Average: " & Application.WorksheetFunction.Average(UserRange) & vbNewLine _
     & "Sum: " & Application.WorksheetFunction.Sum(UserRange)
    


                


End Sub

 

[Joe4]

Lose the word Select. You don't use that when assigning ranges, i.e.
this

Set UserRange = UserRange.EntireRow.Select

should just be:

Set UserRange = UserRange.EntireRow

 

[pingwong9r9r]

You solved it! Thanks so much! I figured it was a rookie syntax error.

 

[Joe4]

You are welcome!