Enter Formula using a Macro

**excelgenius** · 11-15-2009, 12:55 PM

Hi,

I am trying to write a macro which will get values from column B and C and print the result on column D using a simple function like the one before:

D2 = "text" & B2 & "text2" & C2 & "text3"

I need this to be done in the macro, so that when I click the button, it will automatically create column D. Column B is formula and column C is constants.

I tried something like the one below:

Please Login or Register  to view this content.

What am I doing wrong? Does anybody have an idea?

Thanks.

**JBeaucaire** · 11-15-2009, 01:14 PM

Originally Posted by excelgenius

What am I doing wrong? Does anybody have an idea?

First thing you're doing wrong is breaking the forum rules regarding thread titles. Oops.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and change the thread title to one that matches the problem exactly. (you may have to click on GO ADVANCED to get to the thread title).

Remember, thread titles form the basis of people "searching" the database to solve problems for themselves. So make sure your title matches the issue, not your emotional state. (wink)

============
Then try this:

Please Login or Register  to view this content.

**excelgenius** · 11-15-2009, 01:27 PM

@JBeaucaire:

Sorry for breaking rules, that wasn't my intention. I will read the rules at once. Thanks for the edit to the code, it worked just as I wanted.

Cheers.

**mikerickson** · 11-15-2009, 01:44 PM

.Offset doesnt work properly with discontinous ranges.

perhaps

Please Login or Register  to view this content.

**JBeaucaire** · 11-15-2009, 03:45 PM

If these suggestions takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

**shg** · 11-15-2009, 05:31 PM

Originally Posted by mikerickson

.Offset doesnt work properly with discontinous ranges.

Can you provide an example, Mike?

**mikerickson** · 11-15-2009, 06:00 PM

If A1 = "x" , A2 is blank, A3 = "y" then running this code gives these results

Please Login or Register  to view this content.

It appears that .Offset returns the offset of the first area of a discontinous range rather than offsetting the whole range.

**shg** · 11-16-2009, 01:33 PM

Curiuos -- not on my computer:

Please Login or Register  to view this content.

Returns

Please Login or Register  to view this content.

**mikerickson** · 11-16-2009, 03:35 PM

Its probably due to my Excel2004's VBAv.5 vs your VBAv.6.

(I really hope that they put VBA back into Excel 2011 or whenever)

**shg** · 11-16-2009, 03:43 PM

Reckon so.

I know that there are a variety of properties and functions that relate only to the first area of a multi-area range, or can't cope at all. I figured one day when I got ambitious I'd try to tabulate a list.