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

x
?
Solved

Drag/Pull Formulas side ways using VBA

Posted on 2011-10-20
7
Medium Priority
?
211 Views
Last Modified: 2012-06-21
Hi,

I have a cell which would require a large formula which is on the next cell (adjasent)

Eg:

A2 (is empty) and B2 has formula

I want the formula on B2 to be dragged to A2. The reason being, this is the only way the values inside the formulas change according to A2 (and incorporate it)

Again, this is because the formula in B2 uses the data in B1 (hence the formula in A2 should use data in A1)

If I use this

Range("A2").Formula = Range("B2").Formula

Then it just copies the formula which still references to data in B1.

Example aside; I am currently using this:

Cells(5, count) (desitination cell)
Cells(5, count - 1) (formula cell)

When I drag the formula sideways to A2, then everything inside the formula changes. Is there any other way to do this?

Thanks!
0
Comment
Question by:Shanan212
  • 3
  • 3
7 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 37001361

This will drag the formula from B2 into A2
Range("B2").AutoFill Destination:=Range("A2:B2"), Type:=xlFillDefault

Open in new window

Kyle
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37001402
I don't quite understand the second half of your question.  If you're having trouble with the references (relative vs absolute) you should change that in your formula instead of in VBA.  So instead of =$B$1 you do =B1 (you get the idea).  Maybe that's not what you were trying to say.  If not please clarify.

Kyle
0
 
LVL 13

Author Comment

by:Shanan212
ID: 37001417
Thanks Kyle,

But how is that possible with the numbered cells I am using

Cells(5, count) (desitination cell)
Cells(5, count - 1) (formula cell)

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.

 
LVL 35

Assisted Solution

by:Norie
Norie earned 1000 total points
ID: 37001432
Why not just copy?

Cells(5, count-1).Copy Cells(5, count)
0
 
LVL 12

Accepted Solution

by:
kgerb earned 1000 total points
ID: 37001444
I think this will work
Cells(5, count - 1).AutoFill Destination:=Range(Cells(5, count - 1), Cells(5, count)), Type:=xlFillDefault

Open in new window

Kyle
0
 
LVL 13

Author Comment

by:Shanan212
ID: 37001449
Ow I do have some values as $B$1 and other values as D$1 and some left as $C3

The problem is that the formula extends down too (which I have a 'filler function') so in which case the D$1 would change to E$1, F$1, etc and the $C3 would change to $C4, $C5, etc.

In this particular case, I have the formula such that when it get dragged side ways, the values would change accoringly as well but I do not know how to put that into vba
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 37001481
Both solutions worked! Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question