Paste formula till end of the data

[ushaanu]

HI all,

I have some data in excel sheet

where I need the Macro to RUN till the end of the data and the content (formula ) in cell J column gets copied till the end of the data cell.

Sample File enclosed.


Thanks
ANu

 

[YasserKhalil]

Hello Anu
Try this code

Sub Test()
    With ActiveSheet
        With .Range("J2:J" & .Cells(Rows.Count, 10).End(xlUp).Row)
            .Formula = "=IF(H2=""NO"",""NO Part Reqd"",""Yes part Reqd"")"
        End With
    End With
End Sub

 

[ushaanu]

thank you but when run this code .
only one cell calculate the formula like

 

[YasserKhalil]

Change 10 in the code with a column number that already has data (say column B) so in this case 10 would become 2 ..

 

[ushaanu]

thank you soo much .... now it`s working fine.

thank you

 

[Nebu]

Another method

Sub blah()

With Sheet1
i& = .Cells(Rows.Count, 1).End(xlUp).Row
.[J2].Resize(i - 1).Formula = "=IF(H2=""NO"",""NO Part Reqd"",""Yes part Reqd"")"
End With

End Sub

Thanks

 

[YasserKhalil]

You're welcome. Glad I can offer some help

 

[Marc L]

Hi !

Another way for the formula result only :​

Sub Demo1()
    With Sheet1.UsedRange.Rows
        .Range("J2:J" & .Count).Value = .Parent.Evaluate("IF(H2:H" & .Count & "=""NO"",""NO "",""Yes "")&$J$1")
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Chirag R Raval]

Dear Sir @MarcL,
Just Great as tageted rows defined at first. Also we can work on any column of this Dynamic range.
We can use any property or methods if we can successfully define range on which we want work.

Apply count & evaluate is also amazing.

Regards,

Chirag Raval

 

[Chirag R Raval]

Another method

Sub blah()

With Sheet1
i& = .Cells(Rows.Count, 1).End(xlUp).Row
.[J2].Resize(i - 1).Formula = "=IF(H2=""NO"",""NO Part Reqd"",""Yes part Reqd"")"
End With

Dear Sir @YasserKhalil  & @Nebu .

Effactive use of resize property. Just great.

[quote="Nebu, post: 234876, member: 17440"]Another method
[CODE]Sub blah()

With Sheet1
i& = .Cells(Rows.Count, 1).End(xlUp).Row
.[J2].Resize(i - 1).Formula = "=IF(H2=""NO"",""NO Part Reqd"",""Yes part Reqd"")"
End With

End Sub

Thanks

 

[Marc L]

Apply count & evaluate is also amazing.

Thanks, not so amazing as just mixing Excel basics (a formula)
with an easy logic …
​

 

[shahin]

Always something new @Marc L