[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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

0
bryanscott53
Asked:
bryanscott53
1 Solution
 
Muhammad KhanManager, ITCommented:

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

0
 
AgeOfEmpiresCommented:
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.

0
 
Robert SchuttSoftware EngineerCommented:
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Robert SchuttSoftware EngineerCommented:
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

0
 
bryanscott53Author Commented:
excellent, spent 2 hours on trying to fix this thanks for your help
0
 
AgeOfEmpiresCommented:
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now