Search results

sorry, will do

Is there any chance I can have a little debugging help here? Basically, what this does (or should do) is find blank rows and title them based on the values in columns A through to C. It should then sum all data between the two blanks it's found. There is a three tier hierarchy, so what it...

cheers both

Hi All, Should be a simple one: Is there a way of summing a range in VBA without using worksheetfunction? Unfortunately google isn't a lot of use for this kind of thing, as searching for "without" doesn't really work. Cheers, George

one option to make your life a bit easier you could just whack a few message boxes in to say "do you want to update from [sheet1 name]?" and another for sheet2. At the very least it would mean you don't need to run all of the code unnecessarily. Wouldn't actually speed it up, but could save it...

What specifically are you trying to use it for?

Is the "Output" table fixed/manually populated, or do you want it to just show all unique values on its own?

Thanks Luke, exactly the kind of thing I was looking for (I agree, playing about is the best way to learn these things).

Hi all, I'm at a fairly basic understanding of macros, but I figured this would be a good little project to start getting my head around it. Basically, what I'm looking to do is the following: 1) Extract the name of the active worksheet; 2) Open a workbook with the same name as this...

if you didn't want the helper column you could always do '=sumproduct($A3:$F3,{0.05,0.2,0.05,0.15,0.05,0.5})

ooh, good solution, this would make it way easier to expand and add conditions when necessary too. Would I be better served using RGB over colour index?

yeah, it's replacing conditional formatting so I need to use the check. Basically what I'm looking to do is have the macro look at the named range Key and take the format form those colours. I'll need to change the cell references to C123 through C127 to references to Key. Does that make sense?

Right now I'm using it to fill cells in order by cycling through a range of values, so it's an element in an array, is there a way of forcing it to behave like it's an array too? For clarity this is the code I'm using: Public Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range...

I'm having a little bit of an issue combining these to copy the format from the indexed cell. Thanks for this dude :).

Cheers Luke. So I'm clear, does WorksheetFunction basically allow you to use standard excel commands? is there a way of working Q2 without using ActiveCell? Say for example I wanted to fill Cell A1 with the same colour as B1, how would I go about that?

Hi all, I'm still learning a lot about VBA, so I'm sure this first one is a relatively simple question: I'm after a function that does exactly the same thing as INDEX when used in a spreadsheet, specifically when working with a named range. The second question I think is a little more...

Cheers Narayank, it's always best to have someone show you how to find something out rather than hold your hand. Useful thing to know as a function too.

Hi all, in VBA, the statement For Each Cell In Range is used quite a bit. My question is, does this function cycle through by row or by column, i.e. for the range A1:B3 would it work through in the order a1, a2, a3, b1, b2, b3 or a1, b1, a2, b2, a3, b3 I'm sure this is something that...

Thanks Luke. One small issue with your solution though: won't that throw up an error if you only have text/only have numbers in your array? That means this formula has to become...

Hi again (figured it was better to revive this thread than start a new one). I've been looking over the link you sent Hui (thanks for that by the way, I'm pretty sure I get Index now), and something about it is bothering me a little. The way the author uses to set up a dynamic range is...

Cheers, knew I could count on you guys for a good answer :).

Hi All, Not a specific question really, but I've been using OFFSET for my lookups for a while, and I'm aware that for resource efficiency I should be using INDEX. My issue with INDEX is that it requires me to know exactly how large my data set is (unless I'm not understanding it properly -...

=sumproduct(SCORERANGE,{weightings in order, seperated by commas}) I think this is what you're after?

To explain what's going on, first here's the VBA code (as I said, might be sloppy but it works): Dim Teams As Range 'We are saying here that we're going to be making use of a variable "Teams", and it's a Range of cells. Private Sub CommandButton1_Click() 'This is the...

Cheers Luke, wouldn't have thought of that, it'll work perfectly.