"Object Required" Error

  • Thread starter Thread starter Damascus
  • Start date Start date
D

Damascus

Hi everyone,

I created a macro a few weeks ago and used it a lot immediately
afterwards, but I haven't touched it in a while. I have made some
other changes to the spreadsheet since then and when I went to use it
again just now, I keep getting an error message "Object Required".
Nothing has changed in the code and I'm at loss as to what the problem
could be.

The only thing I can think of is that a new worksheet was added, and
the macro uses worksheet indexes... I've fooled around with the code
to try and see if this might have been the problem but to no avail.

All the code is inside a userform, and pasted below:
 
---- I hit enter by accident!

Continued....

Code:

Private Sub Enter_Click()

Dim strNode As String 'variable for
unaggregated string data
Dim strUnAg(0 To 500) As String 'arbitrary array
sizes
Dim strAg(0 To 500) As String 'NOTE: change
dimension upon implementation?
Dim strFlag(0 To 500) As String
Dim temp As String 'temporary
variable
Dim temp2 As String
Dim i As Integer 'used as step
counter
Dim TopCell As Range 'user input top
cell of unaggregated column
Dim BotCell As Range 'user input bottom
cell of unaggregated column
Dim TopCell2 As Range 'user input top
cell of aggregated column
Dim BotCell2 As Range 'user input bottom
cell of aggregated column
Dim myCell As Range 'active cell
variable (Ag)
Dim myCell2 As Range 'active cell
variable (unAg)

Set TopCell = Nothing 'initilizes textboxes
Set BotCell = Nothing '
On Error Resume Next '

Set TopCell = Sheets(4).Range(Me.FromCell.Value).Cells(1)
'reading input for unAg range
Set BotCell = Sheets(4).Range(Me.ToCell.Value).Cells(1) '
On Error GoTo 0 '

If TopCell Is Nothing Or BotCell Is Nothing Then
Me.Label1.Caption = "At least one textbox is blank!"
'error msg
Else
For Each myCell In Sheets(4).Range(TopCell,
BotCell).Cells '
strUnAg(i) =
myCell.Value 'stores in
a string array as it loops through range
i = i +
1 '
Next myCell

Set TopCell2 = Nothing 'initializes textboxes
Set BotCell2 = Nothing '
On Error Resume Next '

Set TopCell2 = Sheets(4).Range(Me.FromAg.Value).Cells(1)
'reading input for Ag range
Set BotCell2 = Sheets(4).Range(Me.ToAg.Value).Cells(1) '
On Error GoTo 0 '

i = 0 'resets counter

For Each myCell2 In Sheets(4).Range(TopCell2,
BotCell2).Cells 'loop reads in aggregated node info from
translation table
strAg(i) =
myCell2.Value 'stores in a
second string array
strFlag(i) = myCell2.Offset(0,
1).Value 'will find flag
i = i +
1 'steps up
counter
Next myCell2
End If

Do
ActiveCell.Offset(1, 0).Select 'steps down activecell

strNode = ActiveCell.Value 'sets string value to
the value of the node

For i = 0 To 500 'loops through unag
info, searching for a match
If strNode = strUnAg(i) Then 'if it finds a
match...
temp = strAg(i) 'sets the temp
variable to the value
ActiveCell.Offset(0, 1) = temp 'prints out
corresponding ag info upon finding match
temp2 = strFlag(i)
ActiveCell.Offset(0, 2) = temp2 'prints out flag
End If
Next i

Loop Until IsEmpty(ActiveCell.Offset(0, 1)) 'repeats until the
loop reaches the blank cell at end of column

End Sub
 
FromCell is not defined.
--
Jim Cone
Portland, Oregon USA



"Damascus" <[email protected]>
wrote in message
Hi everyone,
I created a macro a few weeks ago and used it a lot immediately
afterwards, but I haven't touched it in a while. I have made some
other changes to the spreadsheet since then and when I went to use it
again just now, I keep getting an error message "Object Required".
Nothing has changed in the code and I'm at loss as to what the problem
could be.

The only thing I can think of is that a new worksheet was added, and
the macro uses worksheet indexes... I've fooled around with the code
to try and see if this might have been the problem but to no avail.

All the code is inside a userform, and pasted below:
 
Back
Top