CODEWITHSUNDEEP
excelexcel-formulavba
user3222242
user3222242

Reputation: 129

Facing Error while applying excel formula in Macro

I have the below type of values in column J in excel 

<a href="http://twitter.com" rel="nofollow">Twitter Web Client</a>

and, I have used this formula 

=RIGHT(LEFT(J2,LEN(J2)-4),LEN(LEFT(J2,LEN(J2)-4))FIND(">",LEFT(J2,LEN(J2)-4),2))

to get this value "Twitter Web Client.

When I tried to apply Macro for this using below code in not getting value. Also help me in code by applying formula in the entire range of 'Q' column referencing the 'J' column

Sub Enter_Formula()

    Range("Q2").Formula = "=RIGHT(LEFT(J2,LEN(J2)-4),LEN(LEFT(J2,LEN(J2)-4))-FIND(" > ",LEFT(J2,LEN(J2)-4),2))"

End Sub

Upvotes: 2

Views: 72

Answers (1)

H D
H D

Reputation: 554

You could try something like this

LastRow = ActiveSheet.UsedRange.Rows.Count
MsgBox LastRow 'This would give the User range value to be used to enter the formulaa in all the rows
Range("Q2:Q" & LastRow).Formula = "=LEFT(RIGHT(RC[-7],LEN(RC[-7])-FIND("">"",RC[-7],2)),LEN(RIGHT(RC[-7],LEN(RC[-7])-FIND("">"",RC[-7],2)))-4)"

The result set would be "Twitter Web Client" text.

Upvotes: 2

Related Questions