Run time error 91

[Guest]

Having some trouble with the following. Problem occurs in the Sub
InsertAnswers() routine at the <<< Range(rng.Column & "2").Value = Age >>>
line. Can anyone help please before I throw my PC out of the window...... I
also have practically no idea what I am doing so you will have to really dumb
down your answer for me to get it....... Thanks very much in advance!

Public Sub FindFirstEmptyFirst200Column()
Dim SH As Worksheet
Dim rng As Range
Const FirstStr As String = ""
Set SH = ThisWorkbook.Sheets("First 200")

Set rng = SH.Rows("1:1").Find(What:=FirstStr, After:=Range("B1"),
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False)
rng.Select

End Sub
-------------------------------------------------------
Public Sub AgeQuestion()
Dim i As Integer
Dim Age As Integer

Age = InputBox("Enter Age:", "Age")
For i = 1 To 10
If Age < 17 Or Age > 100 Then
Age = InputBox("Age appears to be incorrect. Please re-enter age
now:", "Age")
End If
Next i

End Sub
-------------------------------------------------------
Public Sub InsertAnswers()
Dim rng As Range
Dim Age As Integer

Call FindFirstEmptyFirst200Column
Call AgeQuestion
If Age < 25 Then
Range(rng.Column & "2").Value = Age
End If

End Sub

 

[Dave Peterson]

rng.column returns a number.
so
Range(rng.Column & "2").Value = Age
would look like:
Range(122).Value = Age

Try:
cells(2,rng.Column).Value = Age

Cells() will accept a string column or a number column.

=====
ps. It's always good to qualify your ranges.

Set rng = SH.Rows("1:1").Find(What:=FirstStr, After:=Range("B1"),
may fail if SH isn't the active sheet.

Set rng = SH.Rows("1:1").Find(What:=FirstStr, After:=Sh.Range("B1"),
will be ok.

 

[Guest]

Thanks Dave - sadly though, Cells(2, rng.Column).Value = Age gives me exactly
the same error.......

 

[Jim Cone]

This altered code is far from perfect, but it will work (kinda).
Note that two subs are changed to functions...
'--
Public Function FindFirstEmptyFirst200Column() As Excel.Range
Dim SH As Worksheet
Dim rng As Range
Const FirstStr As String = ""
Set SH = ThisWorkbook.Sheets("First 200")
Set rng = SH.Rows("1:1").Find(What:=FirstStr, After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
Set FindFirstEmptyFirst200Column = rng
End Function
'-------------------------------------------------------
Public Function AgeQuestion() As Variant
Dim Age As Variant
Do While Age < 17 Or Age > 100
Age = InputBox("Enter Age:", "Age must be between 17 and 100")
If Age = "" Then Exit Do
Loop
AgeQuestion = Age
End Function
'-------------------------------------------------------
Public Sub InsertAnswers()
Dim rng As Range
Dim Age As Variant
Set rng = FindFirstEmptyFirst200Column
Age = AgeQuestion
If Age < 25 Then
Cells(2, rng.Column).Value = Age
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Nick Smith" <[email protected]>
wrote in message
Having some trouble with the following. Problem occurs in the Sub
InsertAnswers() routine at the <<< Range(rng.Column & "2").Value = Age >>>
line. Can anyone help please before I throw my PC out of the window...... I
also have practically no idea what I am doing so you will have to really dumb
down your answer for me to get it....... Thanks very much in advance!

Public Sub FindFirstEmptyFirst200Column()
Dim SH As Worksheet
Dim rng As Range
Const FirstStr As String = ""
Set SH = ThisWorkbook.Sheets("First 200")

Set rng = SH.Rows("1:1").Find(What:=FirstStr, After:=Range("B1"),
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False)
rng.Select

End Sub
-------------------------------------------------------
Public Sub AgeQuestion()
Dim i As Integer
Dim Age As Integer

Age = InputBox("Enter Age:", "Age")
For i = 1 To 10
If Age < 17 Or Age > 100 Then
Age = InputBox("Age appears to be incorrect. Please re-enter age
now:", "Age")
End If
Next i

End Sub
-------------------------------------------------------
Public Sub InsertAnswers()
Dim rng As Range
Dim Age As Integer

Call FindFirstEmptyFirst200Column
Call AgeQuestion
If Age < 25 Then
Range(rng.Column & "2").Value = Age
End If

End Sub

 

[Dave Peterson]

You'll want to read about Scope and Visibility in VBA's help.

When you declare a variable named Rng in a procedure, it doesn't know anything
about the other variables that are using that same name in other procedures.

One way is to declare your variables at the top of the procedure (more about
that later).

But you may want to create functions that pass values back and forth between
functions.

Option Explicit
Public Function FindFirstEmptyFirst200Column(sh As Worksheet) As Range

Dim rng As Range
Const FirstStr As String = ""

Set rng = sh.Rows("1:1").Find(What:=FirstStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set FindFirstEmptyFirst200Column = rng

End Function

Public Function AgeQuestion() As Long
Dim i As Long
Dim Age As Long

Do
'application.inputbox only accepts numbers--so you don't have to
'even check for Text.
Age = CLng(Application.InputBox(prompt:="Enter Age:", _
Title:="Age", Type:=1))

If Age < 17 _
Or Age > 100 Then
'keep looking
Else
'get out
Exit Do
End If
Loop

AgeQuestion = Age

End Function
Public Sub InsertAnswers()

Dim rng As Range
Dim Age As Integer
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("First 200")

Set rng = FindFirstEmptyFirst200Column(wks)

If rng Is Nothing Then
'no empty cells
MsgBox "no empty cells"
Exit Sub
Else
Age = AgeQuestion
If Age < 25 Then
wks.Cells(2, rng.Column).Value = Age
End If
End If

End Sub

But that other alternative is to declare your variables at the top of the
module. By using this technique, the variable is seen by procedures in the same
module:

Option Explicit
Dim Sh As Worksheet
Dim Rng As Range
Dim Age As Long

Public Sub FindFirstEmptyFirst200Column()

Const FirstStr As String = ""

Set Rng = Sh.Rows("1:1").Find(What:=FirstStr, _
After:=Sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End Sub

Public Sub AgeQuestion()
Dim i As Long

Do
Age = CLng(Application.InputBox(prompt:="Enter Age:", _
Title:="Age", Type:=1))
If Age < 17 _
Or Age > 100 Then
'keep looking
Else
'get out
Exit Do
End If
Loop
End Sub
Public Sub InsertAnswers()

Set Sh = ThisWorkbook.Worksheets("First 200")

Call FindFirstEmptyFirst200Column

If Rng Is Nothing Then
'no empty cells
MsgBox "no empty cells"
Exit Sub
Else
Call AgeQuestion
If Age < 25 Then
Sh.Cells(2, Rng.Column).Value = Age
End If
End If

End Sub

====
If you wanted to make one of those variables so that any procedure in any module
can see it, you'd use something like:

Public Rng as Range

====
It may seen easier to just declare the variables at the top. But after awhile,
you'll notice that you're using many of these functions (common functions) in
lots of different workbooks.

And each time you copy|paste, you have to make sure you declare the variables.
I bet after a little bit, you'll see that passing the parms is much easier in
the long run.