• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA help

I

irenesboy

New Member
Hi All

I have this line of code in a vba macro that I use


Range("B2:B" & LR).FormulaR1C1 = "=IF(RC[-1]=""LARRY"",""Lar"",""NONE"")"


It is in column B looking at column A and if the word/name in the corresponding cell in column A is “LARRY” it works fine. (either Lar or NONE).

My problem is I would like to modify it so that if the name in column A is AD/LOU, AD/TOM, or any AD/*** it will put AD in column B.

In simpler terms if in

Column A LARRY, column B=Lar

Column A AD/***, column B=AD

Column A OFFICE, column B=OFF

Anything else in column A column B will =NONE

Thanks for any help.

irenesboy

PS: after doing a lot of searching it might be some kind of LEFT function
 
Regular formula:

=IF(OR(A2="Larry",A2="Office",LEFT(A2,3)="ad/"),SUBSTITUTE(LEFT(A2,3),"/",""),"")


VBA format:

Range("B2:B" & LR).FormulaR1C1 = "=IF(OR(RC[-1]=""Larry"",RC[-1]=""Office"",LEFT(RC[-1],3)=""ad/""),SUBSTITUTE(LEFT(RC[-1],3),""/"",""""),"""")"
 
Hi Luke

The VBA format works but not completly.

If there is anything other than Larry, Office or AD/ in column A column B is blank.

I would like column B to return NONE (NON will be OK).

Appreciate the help.

Thank you very much.

irenesboy
 
Ah, I misunderstood your first post. VBA corrected:

Range("B2:B" & LR).FormulaR1C1 = "=IF(OR(RC[-1]=""Larry"",RC[-1]=""Office"",LEFT(RC[-1],3)=""ad/""),SUBSTITUTE(LEFT(RC[-1],3),""/"",""""),"NONE")"
 
Hi Luke

when I paste the new VBA I now get a

compile error.

syntax error.

the first VBA format still works.

Again thank you for taking the time to help.

irenesboy
 
Oops, should be double quotation marks on end.

[pre]
Code: 
Range("B2:B" & LR).FormulaR1C1 = "=IF(OR(RC[-1]=""Larry"",RC[-1]=""Office"",LEFT(RC[-1],3)=""ad/""),SUBSTITUTE(LEFT(RC[-1],3),""/"",""""),""NONE"")"
[/pre]
 
HI Luke

That did it.

Thank you very much.

One more quick ??

I would like to seperate that one line into 2 lines, what do I use for a seperator.

Thanks

irenesboy
 
Hi Luke

Nevermind the quick ?. I did some searching and found whaat I was looking for.

Thanks very much for all your help.

irenesboy
 
You must log in or register to reply here.
Back
Top