VBA Formula populating 1 too many rows

[benntw]

I have only one formula that is running one too many rows and can't figure out why. I use LastRow = Range("A" & Rows.count).End(xlUp).Row. This works for everything I am running in my VBA except one formula. I have a work order number and I am combining it with the formula to create an activity id for Primavera. Below is the formula. It works perfect except it goes one too many rows.

=I2&TEXT(100+(COUNTIFS(I$2:I2,I2)-1)*10,"-0000")

Hope someone can help out with something I missed on this.

 

[Fluff]

If you mean the formula is being entered into too many rows, it would help if you posted the code being used.

 

[benntw]

If you mean the formula is being entered into too many rows, it would help if you posted the code being used.

This is the line of code that is going too far.


LastRow = Range("A" & Rows.cout).End(xlUp).Row

Range("R1").Value = "Activity Id"
Range("R2:R" & LastRow).Formula = "=RC[-9]&TEXT(100+(COUNTIFS(R2C[-9]:RC[-9],RC[-9])-1)*10,""-0000"")"
Range("R2:R" & LastRow).Copy
Range("R2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Fluff]

That works fine for me, although you can simplify slightly like

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("R1").Value = "Activity Id"
With Range("R2:R" & LastRow)
   .Formula = "=RC[-9]&TEXT(100+(COUNTIFS(R2C[-9]:RC[-9],RC[-9])-1)*10,""-0000"")"
   .Value = .Value
End With

Check what the value of lastrow is when you run the code.

 

[benntw]

That works fine for me, although you can simplify slightly like

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("R1").Value = "Activity Id"
With Range("R2:R" & LastRow)
   .Formula = "=RC[-9]&TEXT(100+(COUNTIFS(R2C[-9]:RC[-9],RC[-9])-1)*10,""-0000"")"
   .Value = .Value
End With

Check what the value of lastrow is when you run the code.

Thank you very much. That worked perfect.

 

[Fluff]

You're welcome & thanks for the feedback.