Dynamic Update macro for autofill, rank, sum, large /w example

**Hyflex** · 01-03-2012, 01:02 PM

Hey,

I'm trying to create an update button to dynamically input a series of macros based on a bunch of rows at the top of the sheet... here it goes explaining each part

Formula
The formula will always be located in row 10, it needs to autofill all the way down to the bottom of the sheet (end of column A)
Sum
Uses the ranges in row 3 and row 4 for the start and end rows for the sum macro.
Large
Same as sum but a large...
Rank
- Same as large & sum but for a rank

The macro should be able to work out what macro to run in each column by the text located in Row 1...

I've attached an example which should clear it up a bit, with some macros to help... but I don't have the knowledge to make them work how I want them to...

**Hyflex** · 01-04-2012, 06:18 AM

I'm trying to get it to run in a simple for i = 1 to LC (last Column) but it's not working how I want it to, I tried just a bunch of if commands (i know there are better ways but I don't know how to do them either :S

**watersev** · 01-06-2012, 06:45 PM

hi Hyflex, please check attachment if it is sort of what you mean. I did not understand what calculations should trigger Formula wording so I just entered any values.

Run code "main". The code for now checks values in "b1:b1, i1:l1" range that can be amended if required.

**Hyflex** · 01-06-2012, 08:00 PM

Watersev, that is mighty impressive and so far looks incredible, I still can't work out what I was doing wrong when I tried but your one is significantly more advanced that what I was doing.

For the Formula macro it should just drag/autofill from row 10 all the way down to the last row in that column.
(I've attached an example)

If Row 5 for each column has a true then after it has done whatever it needs to do in that row it should turn it the whole column into values.
If Row 6 for each column has a true then after it has completed everything it should turn every column into values.

**watersev** · 01-07-2012, 06:04 PM

please check attchment, code shortened and revised, run code "main"

**Hyflex** · 01-08-2012, 09:14 PM

Hey Watersev, thank you very much for your help.

I've edited your code below a fair bit, let me know if I've made any obvious errors.

Please Login or Register  to view this content.

**Hyflex** · 01-09-2012, 11:19 AM

Hey watersev,

I've been playing around with it some more and it's pretty much done apart from one error now.
In the Range("J27:J34") or Range("E27:E34") (last Dataset of any Sum Action) the values aren't correct and I can't work out why it's not calculating them.

See attached for most update version:

**watersev** · 01-10-2012, 05:15 AM

can you provide example of SUM formula example for J13 as you would like it to be after the code execution?

**Hyflex** · 01-10-2012, 06:41 AM

Hey,

The formula for J13:

Please Login or Register  to view this content.

The formula for J14:

Please Login or Register  to view this content.

The formula works perectly fine for the first dataset (J13:J22) but for the second when it's turned into value's it doesn't work. If you set J5 and J6 to FALSE and run the macro they all work fine.

EDIT: I just realised, Rank, Count and Large are doing the same thing (Only for the last dataset)

**watersev** · 01-11-2012, 05:25 PM

try tp replace this block:

Please Login or Register  to view this content.

to this one

Please Login or Register  to view this content.

**Hyflex** · 01-12-2012, 06:07 AM

Hey, Thanks for your reply.

The change you just made stops the macro from replacing the formula into values...

**watersev** · 01-12-2012, 06:40 AM

this line:

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

**Hyflex** · 01-12-2012, 09:08 AM

Thank you ever so much, this is working amazingly now and saves me so much time.

Dynamic Update macro for autofill, rank, sum, large /w example

LinkBack

Thread Tools

Rate This Thread

Display

Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Re: Dynamic Update macro for autofill, rank, sum, large /w example

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions