Date table

[alone]

Hi
in Cell A2 Header with name Date
A3 date For example 2/15/2011
cells J2:J8 i wrote days name to fill in columnA
J2 = Sun
J3 = Tue
J4 = Fri
from J5 to J8 empty
names of days here is changable for table to table
could you write formula to fill days from A4:A32 to fill just dates Only days that match the criteria in the range J2:J8

 

[Ruddles]

Place this in A4 and copy down to A32:-

=IF(ISNA(MATCH(TEXT(A$3+ROW()-3,"ddd"),J$2:J$8,FALSE)),"",A$3+ROW()-3)

 

[alone]

Hi Ruddles
thank you it is working , but i don't want empty cells

 

[Ruddles]

Ah, I misunderstood. If you want to eliminate the spaces you'll have to go for a VBA solution, I think.

 

[alone]

so...
what should we do ?

 

[Ruddles]

"We" can try this:-

Option Explicit
 
Public Sub FillDateColumn()
 
  Dim ws As Worksheet
  Dim dtNext As Date
  Dim iRow As Integer
  Dim wDay As String
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  dtNext = ws.Range("A3")
  iRow = 4
  Do Until iRow > 32
    dtNext = dtNext + 1
    wDay = Format(dtNext, "ddd")
    If Not IsError(Application.Match(wDay, ws.Range("J2:J8"), False)) Then
      ws.Cells(iRow, 1) = dtNext
      iRow = iRow + 1
    End If
  Loop
 
End Sub

That goes in a new standard code module. As it stands you would have to run it manually every time the table in J2:J8 changed...

or...

you could trigger it automatically any time the table was edited by adding this to your worksheet code module:-

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  If Not Intersect(Target, Range("J2:J8")) Is Nothing Then
    Call FillDateColumn
  End If
 
End Sub