VBA Row Looping formula

Question

I'm trying to loop code from cell directly in a macro. But all the return values in the AD column are #NAME?. How can I fix this?

Note that my Column refer to K-Column which has Date value in a form like this: 24/6/2015 6:54:00 AM for example.

For PRow = lrow To 2 Step -1
    CurrentSheet.Cells(PRow, "AD").Value = "=CONCATENATE(YEAR(R[0]C[-19]), _ 
    ""/"",TEXT(MONTH(R[0]C[-19]),""00""))"
Next PRow

MGP · Accepted Answer · 2015-11-20 09:02:57Z

2

When using the R[]C[] Notation you need to add the value like this:

CurrentSheet.Cells(PRow, "AD").FormulaR1C1 = "=CONCATENATE(YEAR(R[0]C[-19]), ""/"",TEXT(MONTH(R[0]C[-19]),""MM""))"

edited Nov 20, 2015 at 9:02

answered Nov 20, 2015 at 8:12

MGP

2,6732 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MGP Over a year ago

did you mean to write TEST(... or TEXT(...??

MGP Over a year ago

@IlhamideaZ Look at my edit, i tried an example and that seems to work. let me know if that is what you where looking for?!

IlhamideaZ Over a year ago

OK, typo, my fault >< Thanks @marco-getrost ! :)

Collectives™ on Stack Overflow

VBA Row Looping formula

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related