Recent content by VincentCrimmins

I thought you could do it in VBA alright, but I was wondering if there was an Excel formula that could handle it? I think I understand how transpose works, but I dont see how it solves my problem here? The TRANSPOSE function will only tranpose the values, not the formula. In other words, if I...

Hi Glenn Thanks for that. Its actually each cell is pulling data from a different sheet, so that won't work (though I hadn't come across that formula, so thanks for that!). I suppose what I'm asking really is, can you read in the formula in a cell, in this case 'Sheet2!D4', and say column +1...

Hi, I have cell in a worksheet ("Sheet1") that references a cell in another, Sheet1!C3=Sheet2!D4 If I drag the formula down one, I know it will reference Sheet2!D5 Is there a way to drag it down to and that it will reference Sheet2!E4? I have to do this for a lot of data and it the column in...

Hi Doug, That's perfect, thanks a lot!

Hi, I have a set of significant values: 8, 8.5, 9, 9.5,etc and I want to flag when a particular value, to four decimal places, is within a 0.1 range of these sigificant values. I have a table that lists the significant value increments, in this case 0.5, and the within range value, in this...

That's perfect! Thanks for that Skip, I never came across the LARGE function before. Regards,

Sorry, I don't understand. The data is as follows: A B C Date Value Average(Top 3 of the Last 10 Values) 23-Jan-09 16.93 22-Jan-09 16.89 21-Jan-09 17.13 20-Jan-09 16.98 19-Jan-09 16.88 16-Jan-09 17.11 15-Jan-09 17.17 14-Jan-09 17.13 13-Jan-09 17.00...

The values are sorted by 'date', so I can't sort them by 'value' and then get the average of the top three.

So far I can calculate the max value and the average of all the values, but I want to get the average of the top three: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> For HighArrayData = 0 To 9 HighSeg = Range("C"...

Sorry, that should say I'm trying to create the function in VBA for Excel. Thanks...

Hi, I'm trying to create a function that will take in 10 values and will return the average of the 3 largest values. Any ideas? I'm struggling badly! Thanks,

I'm sure there's a better way, but the copy and pastespecial gets around it: ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Actual,2,FALSE)),0,(VLOOKUP(RC[-2],Actual,2,FALSE)))" ActiveCell.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _...

You could try the following: Sub RunCode() Dim VariableOne VariableOne = Excel.WorksheetFunction.VLookup("X", Range("C2:D10", 2, False) Cells(2,2)= VariableOne 'Sets B2 to the VLookup result End Sub Or else you could record a macro to copy the result in B2 and then PasteSpecial the...

That's great, thanks a lot for your help!

Where can I find out more about using 'DoEvents'?