How to write a vba loop for Excel

[Paul]

I have the following code for an Excel worksheet, it works on the first try
but it fails on the second time. It returns an error saying "Object variable
or With block variable not set". Thanks

.Range("A6").Select
If Not rs1.BOF Then
rs1.MoveFirst
Do While Not rs1.EOF
strPurchaseOrder = rs1!PurchaseOrder
'Assign Purchase Order to Excel
ActiveCell.Offset(1,
0).Select..............................it fails here. it does not like
ActiveCell in the second try
ActiveCell.Offset(1, 0).FormulaR1C1 = "PURCHASE ORDER:"
ActiveCell.Offset(1, 0).Characters(Start:=1,
Length:=25).Font.Name = "Comic Sans MS"
ActiveCell.Offset(1, 0).Characters(Start:=1,
Length:=25).Font.FontStyle = "Regular"
ActiveCell.Offset(1, 0).Characters(Start:=1,
Length:=25).Font.SIZE = 16

ActiveCell.Offset(0, 1).Select
Range(ActiveCell.Offset(0, 0).Address & ":" &
ActiveCell.Offset(0, 4).Address).Select
Range(ActiveCell.Offset(0, 0).Address & ":" &
ActiveCell.Offset(0, 4).Address).HorizontalAlignment = xlLeft
Range(ActiveCell.Offset(0, 0).Address & ":" &
ActiveCell.Offset(0, 4).Address).MergeCells = True

ActiveCell.FormulaR1C1 = strPurchaseOrder
ActiveCell.Characters(Start:=1, Length:=25).Font.Name =
"Comic Sans MS"
ActiveCell.Characters(Start:=1, Length:=25).Font.SIZE = 16

ActiveCell.Offset(1, -1).Select
Set rs2 = CurrentDb.OpenRecordset("SELECT [Qry_Store
Purchase Order Pull Sheet].* " & _
"FROM [Qry_Store Purchase Order
Pull Sheet] " & _
"WHERE ((([Qry_Store Purchase
Order Pull Sheet].PurchaseOrder)='" & strPurchaseOrder & "'));",
dbOpenSnapshot)
If Not rs2.BOF Then
rs2.MoveFirst
Do While Not rs2.EOF
strHDDescription = rs2!HomeDepotDescription
strSize = rs2!SIZE
strSKU = rs2!SKU
strQuantity = rs2!ShippingQuantity
'Assign Store Purchase Order Details to Excel
ActiveCell.FormulaR1C1 = strHDDescription
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strSize
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strSKU
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strQuantity
ActiveCell.Offset(1, -3).Select
rs2.MoveNext
Loop
rs2.Close
End If
rs1.MoveNext
Loop
rs1.Close
End If

 

[cucchiaino]

Paul:

I have the following code for an Excel
worksheet, it works on the first try but it
fails on the second time. It returns an error
saying "Object variable or With block variable
not set". Thanks

.Range("A6").Select
If Not rs1.BOF Then
rs1.MoveFirst
Do While Not rs1.EOF
strPurchaseOrder =
rs1!PurchaseOrder 'Assign
Purchase Order to Excel
ActiveCell.Offset(1,
0).Select..............................it fails
here.

The message you get means that you try to work
with an object variable that
has no contents / context yet.

In your example I would guess that you probably
has got no selection in your
sheet or that the ActiveCell is not existing. To
me these two seems to be
conflicting, either you have ONE acitve cell or
you have a selection. Or am
I completely wrong here?

Nevertheless, check all variables for their
contents in your function call
to see which one is not set yet.