EOMONTH function in VB

[Flowerstloft]

In the first Column are date values (1/2/2011, 4/25.2009...), which I would like to converted to either last date of the month or the first date of the month using VB code and populated in the second column
(I already know how to use EOMONTH excel function, but since I am dealing with over 250k rows of data, I would like to use VB code to fill the second column)

For example

1st 2nd Col
1/22/2011 1/31/2011 (or 1/1/2011)
3/5/2009 3/30/2009 (or 3/1/2009)
...
...
...

THANK YOU IN ADVANCE FOR YOUR BIG HELP!!

 

[shg]

What version of Excel are you using?

 

[Rick Rothstein]

Not sure how fast this will be, but this code will load up Column B with the first day of the month for the dates in Column A...

With Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  .FormulaR1C1 = "=text(RC1,""mmm yyyy"")"
  .Value = .Value
End With

Note the code assumes the first date is in A1 (hence the 1 in the B1 address in the With statement). If your dates start on a different row, change the 1 to that row number. You may have to format the column to look like you want it to look.

 

[Flowerstloft]

Dear shg:

Excel 2010. Thanks!

Dear Rick:

It works fast. Any suggestion as to how to populate the col b with the last day of the month?

Thank you!

 

[Rick Rothstein]

Dear Rick:

It works fast. Any suggestion as to how to populate the col b with the last day of the month?

Give this code a try...

With Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  .FormulaR1C1 = "=DATE(YEAR(RC1),MONTH(RC1)+1,0)"
  .Value = .Value
End With

 

[Flowerstloft]

It won't work because not every month has 31 days.

Thanks!!

 

[shg]

You might try it before deciding it doesn't work.

 

[Rick Rothstein]

It won't work because not every month has 31 days.

Are you referring to my latest posting? If so, try it... it works fine.

 

[Flowerstloft]

Opps. YOu are right! My sincerely apology to both; it worked perfectly, and my eyes need some rest - neen looking at the numbers all day long

Sorry again and TAHNK YOU!

 

[Flowerstloft]

I have one more request for you gentlemen:

I am trying to figure out the following:


There are random values (alphanumeric) in the range of A2:J200.


In the column range , K2:K200, I would like to VB to calculate the value based on the following conditions

K2: IF(AND(A2="Discount",B2<>0,OR(E2=1,F2=H2)),0,1)
K3: IF(AND(A3="Discount",B3<>0,OR(E3=1,F3=H3)),0,1)
K4
& so on


In the column range , L2:L200: .

L2: IF(COUNTIF(C:C,C2)>1,"Do Not Sell","")
L3: Same...for row 3
.....

Thank you again and I will be sure to test and retest before commenting