VBA code to perform an advanced trim, on the last rown entry

[tommodaniels]

Hi all,

I think I have something which could challenge your expertise so here it is.
<o> </o>
I need some VBA code that always navigates to the last populated row of a s/sheet and then checks column B for a percentage value. It seems that the data in column B will appear as (e.g) 99995544 HAR:5.95%LLM. So I guess I need something which identifies the percentage sign and then extracts this and any numbers left of the percentage sign, to another sheet.
<o> </o>
To take this one step further if at all possible. I need the code to actually cycle through the rows and when it reaches column A where dd.mm.yy is equal to yesterday’s date, it moves to column b of that row and performs the percentage value extraction.
<o> </o>
I have no ideas where to start with this so your answers are much appreciated.
<o> </o>
Many thanks
<o> </o>
T

 

[Krishnakumar]

Hi,

Welcome to board!

It's Formula based approach.

I used a helper cell

In E1,

=INDEX($B$2:$B$35,MATCH(TODAY()-1,A2:A35,0))

In E2:

=MID(E3,FIND(":",E3)+1,SUM(FIND({":","%"},E3)*{-1,1}))+0

I assume that there will be a ':' before the percentage.

HTH

 

[tommodaniels]

Thanks so much - very prompt response and does exactly what I need.

Best regards

Tom