0

I am looking to insert Formula in Column "G" =Text(A3, 'mmmm") so that it updates each cell with Month based on date/time in column A.

xcWks.Range("G" & rCount).Formula = "=TEXT("A"& rCount, mmmm)"

Community
  • 1
  • 1
Puneet Soni
  • 21
  • 3

3 Answers3

0

You want to use

xcWks.Range("G" & rCount).Formula = "=TEXT(A" & rCount & ",""mmmm"")"

which will generate the following formula if rCount was, for instance, 3:

=TEXT(A3,"mmmm")
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Thanks for the quick reply but its not working. I have a running code which extracts outlook data to excel sheet. i wanted to insert this formula. Column "A" has Date/Time, i want to fill column "G" (to the last row) with month name based on column "A" date. – Puneet Soni Oct 03 '17 at 02:17
  • @PuneetSoni Code works for me - what error are you getting? – YowE3K Oct 03 '17 at 02:24
  • no error but it does not fill anything. Cells are blank – Puneet Soni Oct 03 '17 at 02:45
  • is it because my rCount is assigned earlier in the code as "rCount=rCount+1" – Puneet Soni Oct 03 '17 at 02:55
  • @PuneetSoni Without seeing the rest of your code, it is very hard to tell. P.S. Did you follow the link in the comment from Slai. There is a good chance that you don't need a loop for this. But, then again, the other stuff you are doing in your existing code may mean that a loop is better. – YowE3K Oct 03 '17 at 02:59
0

As stated in question

I am looking to insert Formula in Column "G"

I am assuming you want to enter formula in Column G, thus try

Dim lastRow As Long
lastRow = xcWks.Range("A" & xcWks.Rows.Count).End(xlUp).Row 'get last row with data using Colunm A
xcWks.Range("G2:G" & lastRow).Formula = "=TEXT(A2,""mmmm"")"
Mrig
  • 11,612
  • 2
  • 13
  • 27
0

Thanks so much guys. I had an counter "intRow" defined earlier in my code which i used along with rCount and it worked like a charm.

excWks.Cells(intRow, 7).Formula = "=TEXT(A" & rCount & ",""mmmm"")"

Puneet Soni
  • 21
  • 3