Why this VBA doesn't work?

excelos

Active Member
Joined
Sep 25, 2011
Messages
355
Office Version
  1. 365
Platform
  1. Windows
I try to do this:

Range("A1").Formula = "This is some text ",TEXT((COUNTIFS(UDE!$AC:$AC,"Low",UDE!$G:$G,"High")+COUNTIFS(UDE!$AC:$AC,"4AMLD",UDE!$G:$G,"High"))/COUNTIF(UDE!$G:$G,"High"),"00%"))"
Range("A1").Formula = "=CONCATENATE("This is some text ",TEXT((COUNTIFS(UDE!$AC:$AC,"Low",UDE!$G:$G,"High")+COUNTIFS(UDE!$AC:$AC,"4AMLD",UDE!$G:$G,"High"))/COUNTIF(UDE!$G:$G,"High"),"00%"))"


Any idea?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
What doesn't work?
I see TWO VBA statements.
The first doesn't work? The second doesn't work? They both don't work?

How don't they work?
What error are you getting?
Are you getting an error?

So many questions because of so little information supplied.
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
355
Office Version
  1. 365
Platform
  1. Windows
Both statements are trying to do the same time but none works.

It says Syntax error.

I want basically to make a cell contain some text ("This is some text") followed by the return from a formula.

How can I do that?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
In your second VBA formula you need to encompass ALL strings in double double qoutes
So...

instead of
"This is some text"
use
""This is some text""

Do the same for the other text entries so Excel can determine what is part of the "formula" and what is text
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Put the formula in a cell & check that it works.
Then start the macro recorder, select the cell with the formula, hit F2, stop the recorder & you have the code.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Change your first formula to

Range("A1").Formula = "=""This is some text "",TEXT((COUNTIFS(UDE!$AC:$AC,""Low"",UDE!$G:$G,""High"")+COUNTIFS(UDE!$AC:$AC,""4AMLD"",UDE!$G:$G,""High""))/COUNTIF(UDE!$G:$G,""High""),""00%""))"

Again double quotes around text.

The proper format for storing a formula in a cell via VBA is

Destination.formula = "=formula"

UPDATE: I don't know if this works, but it cures the syntax errors that you originally had in both.
 
Last edited:

excelos

Active Member
Joined
Sep 25, 2011
Messages
355
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I still get Syntax error.

Can you write it for me or give me an example?
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
355
Office Version
  1. 365
Platform
  1. Windows
I put in everything double quotes and now it puts the whole thing in A1! Like the " Range("A1").Formula = "=CONCATENATE(""This is some..........."
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Does this work?

Range("A1").Formula = "=""This is some text ""&(COUNTIFS(UDE!$AC:$AC,""Low"",UDE!$G:$G,""High"")+COUNTIFS(UDE!$AC:$AC,""4AMLD"",UDE!$G:$G,""High""))/COUNTIF(UDE!$G:$G,""High"")"

I don't have any data so am getting a DIV/0 error.


CONCATENATE( ) parameters are separated by commas.

But if you're not using the CONCATENATE function as in your first formula then you need ampersand not comma.
Like these

="ABCDE" & "FGHIJ"

=CONCATENATE("ABCDE" , "FGHIJ")

Spaces inserted for clarity.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top