hi there im having problems inserting a formula in a macro when i do the same input by hand it woks fine

With Range(Cells(2, "A"), Cells(2, "A").End(xlDown))
   lRowStart = .Row
   lRowEnd = lRowStart + .Rows.Count - 1
 End With
 With Range(Cells(lRowStart, "B"), Cells(lRowEnd, "B"))
    .Offset(-1).Value = "WE"
    .Formula = "=IF(WEEKDAY(A2)=7,A2+6,IF(WEEKDAY(A2)=1,A2+5,IF(W EEKDAY(A2)=2,A2+4,IF(WEEKDAY(A2)=3,A2+3,IF(WEEKDAY (A2)=4,A2+2,IF(WEEKDAY(A2)=5,A2+1,IF(WEEKDAY(A2)=6 ,A2)))))))"
    Range("B2").Select
 End With

the problem i get is some cells show the wrong date format and i dont seem to be able to change even if i try to format the cells afterwards

any help would be much appriciated

replace your formula with the following.

    .FormulaR1C1 = "=IF(WEEKDAY(R2C1)=7,R2C1+6,IF(WEEKDAY(R2C1)=1,R2C 1+5,IF(WEEKDAY(R2C1)=2,R2C1+4,IF(WEEKDAY(R2C1)=3,R 2C1+3,IF(WEEKDAY(R2C1)=4,R2C1+2,IF(WEEKDAY(R2C1)=5 ,R2C1+1,IF(WEEKDAY(R2C1)=6,R2C1)))))))"


Cheers

Matt

i tried the code that u gave me but now all my dates are coming the same

could anyone tell me why the code produces one date and then repeats it???Please

I understood you to require the cell A2 to be a single cell that is reference by all the formula, but if you want it to be relative to the current row, provided the date is in column A and the formaula is in Col B then the following will work.

  .FormulaR1C1 = "=IF(WEEKDAY(RC[-1])=7,RC[-1]+6,IF(WEEKDAY(RC[-1])=1,RC[-1]+5,IF(WEEKDAY(RC[-1])=2,RC[-1]+4,IF(WEEKDAY(RC[-1])=3,RC[-1]+3,IF(WEEKDAY(RC[-1])=4,RC[-1]+2,IF(WEEKDAY(RC[-1])=5,RC[-1]+1,IF(WEEKDAY(RC[-1])=6,RC[-1])))))))"

Also replace the following...
   With Range(Cells(2, "A"), Cells(2, "A").End(xlDown))
      lRowStart = .Row
      lRowEnd = lRowStart + .Rows.Count - 1
   End With
   With Range(Cells(lRowStart, "B"), Cells(lRowEnd, "B"))

With...
   With Range("B2:B" & Range("A65000").End(xlUp).Row)

Matt

thanks matt