Run-time error '424': Object Required

  • Thread starter Thread starter loren.pottinger
  • Start date Start date
L

loren.pottinger

I keep getting the error in the subject line. Can someone please help.
Thank you.

Sub CopyFixedAssetsformulas()
Dim startRange As Range
Dim rglast As Variant
Dim lastrange As Variant
Set startRange = Sheet20.Range("A5")
Set rglast = Sheet20.Range("A5").SpecialCells(xlCellTypeLastCell)


Set lastrange = Sheet20.Range("A5", "A" &
rglast.Row).Find("Summary", LookIn:=xlValues)


Sheet20.Range("K5", "V5").Copy
Sheet20.Range("K6", "V" & lastrange.Row - 2).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


Sheet20.Range("A5").Select


End Sub
 
You never verify if you found Summary or not. Try something more like this...

Sub CopyFixedAssetsformulas()
Dim rngFound As Range

With Sheet20
Set rngFound = .Range(.Range("A5"), .cells(rows.count,
"A").end(xlup)).Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=False)


if rngfound is nothing then
msgbox "No Summary"
else
.Range("K5").Copy
.Range(.range("K6"), .cells(rngfound.row - 2, "K")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
.Range("V5").Copy
.Range(.range("V6"), .cells(rngfound.row - 2, "V")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


.Range("A5").Select
end if
end with

End Sub
 
Hey Thanks jim.

That worked great, but my first sub isn't working either. Let me know
if you see the problem. Thanks again.

Sub getFixedAssets()

Application.Calculation = xlCalculationManual
Dim m As Variant
Dim rglast As Variant
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connString

Dim AssetData As ADODB.Recordset
Set AssetData = New ADODB.Recordset
Dim AssetQuery As String

AssetQuery = "SELECT FourDot, ProjectName, Mid(Acct,1,2) as [Type],
NULL, desc, [acq date], lifemonths, cost, nbv, dep from [FixedAssets1]
" & _
" WHERE FourDot like '" & Sheet20.Range("A1") & "%' order by
FourDot, acct;"
AssetData.Open AssetQuery, cn, adOpenForwardOnly, adLockReadOnly


Dim startRange As Range
Set startRange = Sheet20.Range("A5")

Dim i As Integer

i = 0
Dim r As ADODB.Record


If Not AssetData.EOF Then
'For Each r In AssetData
AssetData.MoveFirst

Do While Not AssetData.EOF

For m = 0 To 9
startRange.Offset(i, m).Value = AssetData.Fields(m).Value
'
Next m

startRange.Offset(i + 1, 0).EntireRow.Insert

i = i + 1
AssetData.MoveNext
Loop
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.CopyFixedAssetsformulas
Else
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.SetZero
End If
FixedAssets.CopyFixedAssetsformulas
'FixedAssets.HighlightStartEndIssues

Application.Calculation = xlCalculationAutomatic
End Sub
 
Can you narrow it down a bit. What part is not working. Does your open
statement (AssetData.Open) return a valid recordset, ...
--
HTH...

Jim Thomlinson


loren.pottinger said:
Hey Thanks jim.

That worked great, but my first sub isn't working either. Let me know
if you see the problem. Thanks again.

Sub getFixedAssets()

Application.Calculation = xlCalculationManual
Dim m As Variant
Dim rglast As Variant
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connString

Dim AssetData As ADODB.Recordset
Set AssetData = New ADODB.Recordset
Dim AssetQuery As String

AssetQuery = "SELECT FourDot, ProjectName, Mid(Acct,1,2) as [Type],
NULL, desc, [acq date], lifemonths, cost, nbv, dep from [FixedAssets1]
" & _
" WHERE FourDot like '" & Sheet20.Range("A1") & "%' order by
FourDot, acct;"
AssetData.Open AssetQuery, cn, adOpenForwardOnly, adLockReadOnly


Dim startRange As Range
Set startRange = Sheet20.Range("A5")

Dim i As Integer

i = 0
Dim r As ADODB.Record


If Not AssetData.EOF Then
'For Each r In AssetData
AssetData.MoveFirst

Do While Not AssetData.EOF

For m = 0 To 9
startRange.Offset(i, m).Value = AssetData.Fields(m).Value
'
Next m

startRange.Offset(i + 1, 0).EntireRow.Insert

i = i + 1
AssetData.MoveNext
Loop
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.CopyFixedAssetsformulas
Else
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.SetZero
End If
FixedAssets.CopyFixedAssetsformulas
'FixedAssets.HighlightStartEndIssues

Application.Calculation = xlCalculationAutomatic
End Sub

Jim said:
You never verify if you found Summary or not. Try something more like this...

Sub CopyFixedAssetsformulas()
Dim rngFound As Range

With Sheet20
Set rngFound = .Range(.Range("A5"), .cells(rows.count,
"A").end(xlup)).Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=False)


if rngfound is nothing then
msgbox "No Summary"
else
.Range("K5").Copy
.Range(.range("K6"), .cells(rngfound.row - 2, "K")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
.Range("V5").Copy
.Range(.range("V6"), .cells(rngfound.row - 2, "V")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


.Range("A5").Select
end if
end with

End Sub
Click to expand...
Click to expand...
 
Sorry about that................5th line in.

Dim cn As New ADODB.Connection
Says it's an undefined user type. Don't know how to define it, I'm a
newbie to VBA, but I know this works.............it is someone else's
code but it works in another excel file doing the same thing. All I did
was chage the DB and table from which it is retrieving the data.

Jim said:
Can you narrow it down a bit. What part is not working. Does your open
statement (AssetData.Open) return a valid recordset, ...
--
HTH...

Jim Thomlinson


loren.pottinger said:
Hey Thanks jim.

That worked great, but my first sub isn't working either. Let me know
if you see the problem. Thanks again.

Sub getFixedAssets()

Application.Calculation = xlCalculationManual
Dim m As Variant
Dim rglast As Variant
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connString

Dim AssetData As ADODB.Recordset
Set AssetData = New ADODB.Recordset
Dim AssetQuery As String

AssetQuery = "SELECT FourDot, ProjectName, Mid(Acct,1,2) as [Type],
NULL, desc, [acq date], lifemonths, cost, nbv, dep from [FixedAssets1]
" & _
" WHERE FourDot like '" & Sheet20.Range("A1") & "%' order by
FourDot, acct;"
AssetData.Open AssetQuery, cn, adOpenForwardOnly, adLockReadOnly


Dim startRange As Range
Set startRange = Sheet20.Range("A5")

Dim i As Integer

i = 0
Dim r As ADODB.Record


If Not AssetData.EOF Then
'For Each r In AssetData
AssetData.MoveFirst

Do While Not AssetData.EOF

For m = 0 To 9
startRange.Offset(i, m).Value = AssetData.Fields(m).Value
'
Next m

startRange.Offset(i + 1, 0).EntireRow.Insert

i = i + 1
AssetData.MoveNext
Loop
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.CopyFixedAssetsformulas
Else
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.SetZero
End If
FixedAssets.CopyFixedAssetsformulas
'FixedAssets.HighlightStartEndIssues

Application.Calculation = xlCalculationAutomatic
End Sub

Jim said:
You never verify if you found Summary or not. Try something more like this...

Sub CopyFixedAssetsformulas()
Dim rngFound As Range

With Sheet20
Set rngFound = .Range(.Range("A5"), .cells(rows.count,
"A").end(xlup)).Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=False)


if rngfound is nothing then
msgbox "No Summary"
else
.Range("K5").Copy
.Range(.range("K6"), .cells(rngfound.row - 2, "K")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
.Range("V5").Copy
.Range(.range("V6"), .cells(rngfound.row - 2, "V")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


.Range("A5").Select
end if
end with

End Sub

--
HTH...

Jim Thomlinson


:

I keep getting the error in the subject line. Can someone please help.
Thank you.

Sub CopyFixedAssetsformulas()
Dim startRange As Range
Dim rglast As Variant
Dim lastrange As Variant
Set startRange = Sheet20.Range("A5")
Set rglast = Sheet20.Range("A5").SpecialCells(xlCellTypeLastCell)


Set lastrange = Sheet20.Range("A5", "A" &
rglast.Row).Find("Summary", LookIn:=xlValues)


Sheet20.Range("K5", "V5").Copy
Sheet20.Range("K6", "V" & lastrange.Row - 2).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


Sheet20.Range("A5").Select


End Sub
Click to expand...
Click to expand...
Click to expand...
 
You have other replies at your other posts.

loren.pottinger said:
I keep getting the error in the subject line. Can someone please help.
Thank you.

Sub CopyFixedAssetsformulas()
Dim startRange As Range
Dim rglast As Variant
Dim lastrange As Variant
Set startRange = Sheet20.Range("A5")
Set rglast = Sheet20.Range("A5").SpecialCells(xlCellTypeLastCell)

Set lastrange = Sheet20.Range("A5", "A" &
rglast.Row).Find("Summary", LookIn:=xlValues)

Sheet20.Range("K5", "V5").Copy
Sheet20.Range("K6", "V" & lastrange.Row - 2).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False

Sheet20.Range("A5").Select

End Sub
Click to expand...
 
You require a reference to the ADODB library. In the VBE select Tools ->
References -> ActiveX Data objects Library 2.? Note that if you deploy this
to someone who does not have this library the code will crash. You are best
off in that case to select a low version number for the best chances of them
having the reference or using late binding but taht is a diferent discussion
all together.
--
HTH...

Jim Thomlinson


loren.pottinger said:
Sorry about that................5th line in.

Dim cn As New ADODB.Connection
Says it's an undefined user type. Don't know how to define it, I'm a
newbie to VBA, but I know this works.............it is someone else's
code but it works in another excel file doing the same thing. All I did
was chage the DB and table from which it is retrieving the data.

Jim said:
Can you narrow it down a bit. What part is not working. Does your open
statement (AssetData.Open) return a valid recordset, ...
--
HTH...

Jim Thomlinson


loren.pottinger said:
Hey Thanks jim.

That worked great, but my first sub isn't working either. Let me know
if you see the problem. Thanks again.

Sub getFixedAssets()

Application.Calculation = xlCalculationManual
Dim m As Variant
Dim rglast As Variant
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connString

Dim AssetData As ADODB.Recordset
Set AssetData = New ADODB.Recordset
Dim AssetQuery As String

AssetQuery = "SELECT FourDot, ProjectName, Mid(Acct,1,2) as [Type],
NULL, desc, [acq date], lifemonths, cost, nbv, dep from [FixedAssets1]
" & _
" WHERE FourDot like '" & Sheet20.Range("A1") & "%' order by
FourDot, acct;"
AssetData.Open AssetQuery, cn, adOpenForwardOnly, adLockReadOnly


Dim startRange As Range
Set startRange = Sheet20.Range("A5")

Dim i As Integer

i = 0
Dim r As ADODB.Record


If Not AssetData.EOF Then
'For Each r In AssetData
AssetData.MoveFirst

Do While Not AssetData.EOF

For m = 0 To 9
startRange.Offset(i, m).Value = AssetData.Fields(m).Value
'
Next m

startRange.Offset(i + 1, 0).EntireRow.Insert

i = i + 1
AssetData.MoveNext
Loop
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.CopyFixedAssetsformulas
Else
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.SetZero
End If
FixedAssets.CopyFixedAssetsformulas
'FixedAssets.HighlightStartEndIssues

Application.Calculation = xlCalculationAutomatic
End Sub

Jim Thomlinson wrote:
You never verify if you found Summary or not. Try something more like this...

Sub CopyFixedAssetsformulas()
Dim rngFound As Range

With Sheet20
Set rngFound = .Range(.Range("A5"), .cells(rows.count,
"A").end(xlup)).Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=False)


if rngfound is nothing then
msgbox "No Summary"
else
.Range("K5").Copy
.Range(.range("K6"), .cells(rngfound.row - 2, "K")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
.Range("V5").Copy
.Range(.range("V6"), .cells(rngfound.row - 2, "V")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


.Range("A5").Select
end if
end with

End Sub

--
HTH...

Jim Thomlinson


:

I keep getting the error in the subject line. Can someone please help.
Thank you.

Sub CopyFixedAssetsformulas()
Dim startRange As Range
Dim rglast As Variant
Dim lastrange As Variant
Set startRange = Sheet20.Range("A5")
Set rglast = Sheet20.Range("A5").SpecialCells(xlCellTypeLastCell)


Set lastrange = Sheet20.Range("A5", "A" &
rglast.Row).Find("Summary", LookIn:=xlValues)


Sheet20.Range("K5", "V5").Copy
Sheet20.Range("K6", "V" & lastrange.Row - 2).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


Sheet20.Range("A5").Select


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks Jim, that worked

Jim said:
You require a reference to the ADODB library. In the VBE select Tools ->
References -> ActiveX Data objects Library 2.? Note that if you deploy this
to someone who does not have this library the code will crash. You are best
off in that case to select a low version number for the best chances of them
having the reference or using late binding but taht is a diferent discussion
all together.
--
HTH...

Jim Thomlinson


loren.pottinger said:
Sorry about that................5th line in.

Dim cn As New ADODB.Connection
Says it's an undefined user type. Don't know how to define it, I'm a
newbie to VBA, but I know this works.............it is someone else's
code but it works in another excel file doing the same thing. All I did
was chage the DB and table from which it is retrieving the data.

Jim said:
Can you narrow it down a bit. What part is not working. Does your open
statement (AssetData.Open) return a valid recordset, ...
--
HTH...

Jim Thomlinson


:

Hey Thanks jim.

That worked great, but my first sub isn't working either. Let me know
if you see the problem. Thanks again.

Sub getFixedAssets()

Application.Calculation = xlCalculationManual
Dim m As Variant
Dim rglast As Variant
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connString

Dim AssetData As ADODB.Recordset
Set AssetData = New ADODB.Recordset
Dim AssetQuery As String

AssetQuery = "SELECT FourDot, ProjectName, Mid(Acct,1,2) as [Type],
NULL, desc, [acq date], lifemonths, cost, nbv, dep from [FixedAssets1]
" & _
" WHERE FourDot like '" & Sheet20.Range("A1") & "%' order by
FourDot, acct;"
AssetData.Open AssetQuery, cn, adOpenForwardOnly, adLockReadOnly


Dim startRange As Range
Set startRange = Sheet20.Range("A5")

Dim i As Integer

i = 0
Dim r As ADODB.Record


If Not AssetData.EOF Then
'For Each r In AssetData
AssetData.MoveFirst

Do While Not AssetData.EOF

For m = 0 To 9
startRange.Offset(i, m).Value = AssetData.Fields(m).Value
'
Next m

startRange.Offset(i + 1, 0).EntireRow.Insert

i = i + 1
AssetData.MoveNext
Loop
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.CopyFixedAssetsformulas
Else
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.SetZero
End If
FixedAssets.CopyFixedAssetsformulas
'FixedAssets.HighlightStartEndIssues

Application.Calculation = xlCalculationAutomatic
End Sub

Jim Thomlinson wrote:
You never verify if you found Summary or not. Try something more like this...

Sub CopyFixedAssetsformulas()
Dim rngFound As Range

With Sheet20
Set rngFound = .Range(.Range("A5"), .cells(rows.count,
"A").end(xlup)).Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=False)


if rngfound is nothing then
msgbox "No Summary"
else
.Range("K5").Copy
.Range(.range("K6"), .cells(rngfound.row - 2, "K")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
.Range("V5").Copy
.Range(.range("V6"), .cells(rngfound.row - 2, "V")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


.Range("A5").Select
end if
end with

End Sub

--
HTH...

Jim Thomlinson


:

I keep getting the error in the subject line. Can someone please help.
Thank you.

Sub CopyFixedAssetsformulas()
Dim startRange As Range
Dim rglast As Variant
Dim lastrange As Variant
Set startRange = Sheet20.Range("A5")
Set rglast = Sheet20.Range("A5").SpecialCells(xlCellTypeLastCell)


Set lastrange = Sheet20.Range("A5", "A" &
rglast.Row).Find("Summary", LookIn:=xlValues)


Sheet20.Range("K5", "V5").Copy
Sheet20.Range("K6", "V" & lastrange.Row - 2).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


Sheet20.Range("A5").Select


End Sub
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime error '424': Object Required 1
Runtime error '424': Object Required 2
Error 424, Object required 2
Object variable or with Block variable not set 9
Error 424 Object required 2
ERROR 424" OBJECT REQUIRED 1
Get the print range from a sheet you just copied for use inthe new 2
Run Time error 424 Object Required 8

Back
Top