HI

Can anybody help me write a macro code for this?

For i = 5 To Cells(39, 2).Value
Cells(3, i).Value = MID(TEXT(B37+k ,"dddd"),1,3)
k = k + 1
Next i


My problem is that i am not able to write a code for formula.


thank you

If I read you right then try this for the Text function

Cells(3, i).Value = Mid(Application.WorksheetFunction.Text(B37 + k, "dddd"),
1, 3)


--

Regards,
Nigel




"hoysala" wrote in message
...
HI

Can anybody help me write a macro code for this?

For i = 5 To Cells(39, 2).Value
Cells(3, i).Value = MID(TEXT(B37+k ,"dddd"),1,3)
k = k + 1
Next i


My problem is that i am not able to write a code for formula.


thank you

Okay, I have a few comments. First, you can't refer to a cell value by just
giving its address... in VBA, B37 is assumed to be a variable name, not a
cell reference. So the best way to refer to B37+k in a loop is by using
Cells(37+k,"B"). Next, the VB function you want to use in place of TEXT is
Format. From the looks of your formula, it looks like B37+k will contain a
date (I assume that from you use of the "dddd" parameter that you tried to
use) and that you are trying to assign the abbreviation for the day name to
Cells(3,i). This would do what you want...

Cells(3, i).Value = Mid(Format(Cells(37 + k, "B") ,"dddd"),1,3)

However, you don't need to use the Mid function (either in VB or at the
worksheet level as TEXT uses the same format pattern parameter) in order to
get the abbreviation for the day name... just use 3 d's instead of 4 of
them. This will also do what you want...

Cells(3, i).Value = Format(Cells(37 + k, "B"), "ddd")

And, just so you are aware, you could also to what you want this way...

Cells(3, i).Value = WeekdayName(Weekday(Cells(37 + k, "B").Value), True)

Yes, I know its longer... just wanted to show you another way using date
specific functions. Speaking of date specific functions, there is still yet
another way to do what you want....

Cells(3, i).Value = WeekdayName(DatePart("w", Cells(37+k, "B").Value),True)

Me personally... I would use the second method above, namely....

Cells(3, i).Value = Format(Cells(37 + k, "B"), "ddd")

Rick


"hoysala" wrote in message
...
HI

Can anybody help me write a macro code for this?

For i = 5 To Cells(39, 2).Value
Cells(3, i).Value = MID(TEXT(B37+k ,"dddd"),1,3)
k = k + 1
Next i


My problem is that i am not able to write a code for formula.


thank you