asked on

Insert formula using VBA

I am trying to enter the following formula in using vba but it is not working, can you help show me where i am going wrong?

Range("d2:d30000").Formula = _
        "=if(c2=""Solnsmarts2"",IF(ISNUMBER(FIND(""Created by"",f2),MID(f2,FIND(""Created by"",f2)+11,IF(ISNUMBER(FIND("" "",f2,FIND(""Created by"",f2)+11)),FIND("" "",f2,FIND(""Created by"",f2)+11),LEN(f2))-FIND(""Created by"",f2)-10),"""")),c2)"

Open in new window

ASKER CERTIFIED SOLUTION

Muhammad Khan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AgeOfEmpires

Not sure exactly what you are trying to do, but upon quick inspection, one of the first things I see is an incorrect call to ISNUMBER. It takes one parameter, yet you don't have a closing ")" after the FIND(""Created by"",f2) to close out the ISNUMBER function. Same thing on the next call.

Robert Schutt

I think this is what you need (changed some arguments around)...

Range("d2:d30000").Formula = "=if(c2=""Solnsmarts2"",IF(ISNUMBER(FIND(""Created by"",f2)),MID(f2,FIND(""Created by"",f2)+11,IF(ISNUMBER(FIND("" "",f2,FIND(""Created by"",f2)+11)),FIND("" "",f2,FIND(""Created by"",f2)+11),LEN(f2)-FIND(""Created by"",f2)-10)),""""),c2)"

Open in new window

Robert Schutt

it was basically what AgeOfEmpires already said, to solve it, I rewrote the formula like this (it helps if you have a text editor that can show matching brackets):

=if(
	c2="Solnsmarts2",
	IF(
		ISNUMBER(FIND("Created by",f2)),
		MID(
			f2,
			FIND("Created by",f2)+11,
			IF(
				ISNUMBER(
					FIND(
						" ",
						f2,
						FIND("Created by",f2)+11
					)
				),
				FIND(
					" ",
					f2,
					FIND("Created by",f2)+11
				),
				LEN(f2)-FIND("Created by",f2)-10
			)
		),
		""
	),
	c2
)

Open in new window

Bryan Scott

ASKER

excellent, spent 2 hours on trying to fix this thanks for your help

AgeOfEmpires

Glad to see someone else investigates problems this way! This is exactly the way I verified the mismatch. With those long formulas, trying to sort it out in your head isn't always fool-proof. Putting it in a text editor (as robert_schutt and I did) will certainly help to isolate these types of problems.