Thanks Jim, that worked

Jim Thomlinson wrote:
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" wrote:

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 Thomlinson wrote:
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" wrote:

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


"loren.pottinger" wrote:

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(xlCellTypeLastCel l)


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