Posts by tardy

Hi Guys,

Have attached an excel example of my query.

How do i look up based on both a row header and a column header to return a value that corresponds to those two from another sheet?

Thanks!

Regards
Lloyd

Re: Use Of Hash Sign In Macro

Thanks alot!

Hi Guys,

I have this code that reads below,

What is the # used to refer to ? Appreciate any help on this... thanks in advance..

ReDim qtf(1# To N)
    ReDim FVec(1# To M)


    .....


        If N <= 0# Or M < N Then
        Info = -1#


    .....

Re: Dimming Object In Custom Function

thanks richard!

i put in N = 50, and i specified the range to just A1:D1, apparently, it does work but changing those ranges beyond the specified N-1 doesnt affect the function values.

Same goes for specifying N = 5, and then specifying my object to include 100 ranges. Editing those ranges beyond those boundaried by N-1 also doesnt affect.

Just curious, how can i possibly remove this object in the function, and include an array instead?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Dave,

thanks, so u mean, to fundamentally change the code so that it will loop through the ranges?

Is this more efficient, since I do have the ranges fixed for that particular V(i)s?

Re: Dimming Object In Custom Function

hi richard,

thanks for changing the topic. so if i select range A1 to A5 as the object, the loop will actually take A1 value first, then go on to A2's value.. Is that correct?

I specified my N to be 50, but if i select the object with a collection of just 10 ranges e.g. A1:A10, will the loop for i > 10 to N -1 , take values of V to be zero?

Hi Guys,

I have a user defined function that goes like below, is v an array? would appreciate if someone can help provide me with a little more detailed explaination on what happens with V in this loop.. many thanks

function a(t as double, v as object, n as long)


dim sum as double, i as long, change as double


change = t/n


for i = 1 to n - 1
sum = sum + v(i) * (2 + v(i) )
next
sum = sum + v(N)


a = ....

Re: Reverse Vlookup

yes dave.. roger that[hr]*[/hr] Auto Merged Post;[dl]*[/dl]just wondering if this will be robust can someone advise? i can put the match inside the equation...

sorry guys..thanks...

Hi guys,

Is there a way to reverse a lookup function?

I have a s/s attached, basically B is like an output of figures after some calculations. And I am trying to find from largest to smallest values and then having column E reflect the Letter beside that number...

tried using match but messed up.

can someone please help?

Re: Detailed Guide On Solver

derk

this is wonderful... thanks a mil!

Hi Guys,

Does anyone have a comprehensive / detailed guide on using the Solver which includes many examples?

Sorry for the brief question.. didnt know where else to find as the website guides are making me dizzy..

regards,
lloyd

Re: Calculating Averages On Moving Ranges

Thanks Domenic, Ger

If you guys would be kind enough to explain how your formula actually works?

I tried reading on mod ceiling and int but i just cant put them together...

that will really help in my understanding....

thanks again ....
lloyd

Re: Calculating Averages On Moving Ranges

Domenic,

I looked at ur formula, it works! thanks...

Ger, same for yours! - I am totally amused/amazed... it is gonna take me a long time to get whats going on in there... but its good learning!

Domenic,

One question... If i wanted to get the Average of just the 1st and the 10th, 2nd-11th, 3rd-12th and so on... meaning no more averaging all 10 numbers but just 2 numbers. Still moving back by each day.. can it be done?

Regards,
Lloyd

Re: Calculating Averages On Moving Ranges

Peeps..

i am flabbergasted by the formulas... Hahahah will try now!

thanksssss

Hi Guys,

Have a complicated (i think) task here...

Im trying to calculate moving averages on a window of 10 working days. For a period of 40 days, i will get 4 average figures.

However, I will need to repeat this process by moving back 1 day for each set of 4 Average figures.

This means I will have, for a 40 day period, a total of 40 sets of averages but of coz this will require my data set to have 50 sets of daily figures to accomodate for the last set of 4 average figures.

Then i want to translate these figures into a seperate column, in order of the calculations performed..

Can a macro be written for this?

I think im confusing pple.. have attached a spreadsheet...

Is there a code that can do this?

Hi Guys,

Anyone knows a code to deactivate macros in a current workbook?

I have an auto save on the workbook at a certain time, but after it saves down the workbook goes on to save itself again ...

many thanks
lloyd

Re: Transpose Data Onto Another Sheet

thanks bradles..

Guys,

does anyone have a macro that can tranpose the data: "AA-FF" as row category and "A-F" as column category? many thanks...

i attached an xl...

lloyd

Re: Sum Based On Category

Roy,

I cant design a better way.. each product has two figures. and figures are daily... and i wish to see all details by day which is the top row..

Dave, Parsnip

Thanks!

Lloyd

Hi Guys,

Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.

Have attached a sample xls..

many thanks..

Re: Cell Protection On Multiple Ranges

ohh

now i know what went wrong.. i did this instead...

with range("A1:A10","B1:B10","C1:C10")

:p

thanks roy!