Excel Functions for Records
 

I have a spreadsheet that has a different number of rows every week. Based on
the number of rows, I would like excel to perform functions such as Vlookup
and Days 360, in the empty columns. How do I get the functions to appear only
when there are records in the corresponding columns? Here is what I have. Not
sure where I should insert where to put my formulas and how ?


Worksheets("ARO").Unprotect

With Sheets("ARO")
Range("B8").CopyFromRecordset RST
End With
DoEvents

RST.Close

Worksheets("ARO").Protect
DoEvents

Worksheets("ARO").Unprotect
Sheets("ARO").Select
Cells(2, 1).Value = "Report has been succesfully updated"
DoEvents
Cells(2, 1).Font.ColorIndex = 1
DoEvents
Cells(2, 1).Font.Bold = True
DoEvents
Worksheets("ARO").Protect

Exit Sub

Excel Functions for Records
 

The easy solution is to use the Recordset.RecordCount property to find the
number of rows in the recordset; then you can loop through those rows and
copy the formulas; e.g.

RSTCount = RST.RecordCount
With Sheets("ARO")
.Unprotect ' you may as well put this inside the With... End With
.Range("C8").Copy 'assumes a formula in C8 that you want copied down
' assume you need the dot here before Range:
.Range("B8").CopyFromRecordset RST
For i = 1 to RSTCount-1
.Range("C8").Offset(i,0).PasteSpecial xlPasteAll
Next i
DoEvents
RST.Close
.Protect
End With

--
- K Dales


"marthasanchez" wrote:

I have a spreadsheet that has a different number of rows every week. Based on
the number of rows, I would like excel to perform functions such as Vlookup
and Days 360, in the empty columns. How do I get the functions to appear only
when there are records in the corresponding columns? Here is what I have. Not
sure where I should insert where to put my formulas and how ?


Worksheets("ARO").Unprotect

With Sheets("ARO")
Range("B8").CopyFromRecordset RST
End With
DoEvents

RST.Close

Worksheets("ARO").Protect
DoEvents

Worksheets("ARO").Unprotect
Sheets("ARO").Select
Cells(2, 1).Value = "Report has been succesfully updated"
DoEvents
Cells(2, 1).Font.ColorIndex = 1
DoEvents
Cells(2, 1).Font.Bold = True
DoEvents
Worksheets("ARO").Protect

Exit Sub