Fill Down Based On Contents of Another Cell

[KTB23]

I would like to be able to fill down 7 fields in a row for each row that has a date in another field. I want it to stop filling when there is not a date in comlumn A. I would like to do this as part of a macro (that I already created), but I am not very familiar with how to set a range, especially if it is based on whether or not there is something in another cell. Can anyone help me with this?

Thanks in advance!

 

[SkipVought]

KTB23,

I don't know what you mean by "fill down 7 fields in a row" since rows are ACROSS no DOWN.

Assuming that your dates are in Col A, starting in row 1

Dim rng as range, r as range
set rng = range(cells(1,1), cells(1,1).end(xldown))
for each r in rng
  'do stuff for r in row r.row
next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KTB23]

Thanks for the response Skip.

Sorry for being a little unclear in my previous posting.

I have formulas in cells S2:Y2. So if there is a date in, say, cells A3:A70, I would like the macro to automatically fill down cells S3:S70. However, the last cell that has a date will change from week to week.

Does this make more sense?

 

[SkipVought]

So you want the formulas in cols S:Y to fill in whenever a new entry is added in column A?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KTB23]

Right. I know this is done easily through formulas, but I would prefer to do it within the macro, since I will be running the macro for other purposes once new data is added to the spreadsheet.

 

[SkipVought]

You will need to run this procedure from the Worksheet_Change event (right click the sheet tab and select View Code)

Sub NameDataRange()
    ActiveWorkbook.Names.Add _
        Name:="rData", _
        RefersTo:="='" & ActiveSheet.Name & "'!" & [A3].CurrentRegion.Address
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    
    With Range("rData")
        If Target.Row = .Row + .Rows.Count And Target.Column = 1 Then
            For iCol = Cells(1, "S").Column To Cells(1, "Y").Column
                Select Case Chr(iCol + 64)
                    Case "S"
                        Cells(Target.Row, iCol).Formula = "=A" & Target.Row
                    Case "T"
                    
                End Select
            Next
        End If
    End With
    
    NameDataRange
    Application.EnableEvents = True
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884