Excel formula to VBA code conversion

[secure]

Hi all,
I just registered to this forum, sorry to start with a question.
I have written two formulas in excel and want to extend to many cells in a certain interval and generalize to other similar worksheets. So, I need to write them as vba code. The formulas:

=INDEX('sheet1'!$A2:$BI$7000,MATCH(MAX('sheet1'!I2:I7),'sheet1'!I2:I7,0),COLUMN('sheet1'!A1))

=IF(C3=W$3,100,IF(C3<W$4,TREND($V$3:$V$4,W$3:W$4,C3),IF(C3=W$4,85,IF(C3<W$5,TREND(V$4:V$5,W$4:W$5,C3),IF(C3=W$5,5,IF(C3<W$6,TREND($V$5:$V$6,W$5:W$6,C3),0))))))

I appreciate if any1 can help me to come up with vba codes.

thanks in advance

[royUK]

Welcome to the Forum

Formula #1

Please Login or Register  to view this content.

Formula #2

Please Login or Register  to view this content.

[pike]

Hi secure
I find the easiest way is to start a macro recording
select the cell with the formula and then goto the formula bar select the end of function and press enter.
the recorded macro will convert the function to RC format
hope it helps

[secure]

thank you very much guys for the quick replies.
however, what I need is vba program instead of rc codes. i need to extend them to many cells which satisfy other conditions and it's hard to follow the cell numbers in RC code.

[royUK]

thank you very much guys for the quick replies.
however, what I need is vba program instead of rc codes. i need to extend them to many cells which satisfy other conditions and it's hard to follow the cell numbers in RC code.

The formulas need to be in R1C1 format to write to other cells,

Please Login or Register  to view this content.

We can't give any better cod without knowing exactly what you want

[secure]

OK, I attached a sample sheet from my data. For each "Name Code" we have 6 rows of info. using this formula I choose the row with maximum "T-Trace" value and get the equivalent values in the row from the columns: A,B,G,L,H,F,J,K,AA and AB. So I will have 10 cells containing this formula:

Please Login or Register  to view this content.

Now I calculate the linear interpolation for the above found value comparing it with the score value from "Score Table" using this:

Please Login or Register  to view this content.

This formula falls either in exact matches of the score or an interpolation of two values.
Thanks again for the efforts.

[royUK]

Soyou want each formulawritten10 times in a new sheet?

[secure]

this is true for the first set of the data. the sample sheet contains 3 sets (each set includes 6 rows), the real file contains above 100 sets. so the 10 formulas for the first 6 row will be repeated above 100 times to obtain a table of say, 100*10. the second formula acquires this 100*10 and results in a table of 100*7.

[pike]

hi secure
Maybe

Please Login or Register  to view this content.

[royUK]

You coulduse

Please Login or Register  to view this content.

Pike:

You are using With so it should be .Cells

[secure]

Thanks so much guys, will check and revert

[secure]

Thanks all for the helps. But I'm still having problem. I'm not good with RC formulas and maybe that's why. Here's the issue:
1- The columns I pick the info from are not in a certain order and do not fall in any math formula. So apparently I have to rewrite formula for each cell. This is in fact alright as it is only 10 formulas to be written.
2- My sample data contains only three sets of info to be picked, while the real data is above 100 sets. So I need to create a loop for this part. i.e. I don't mind to have 10 formulas in a loop of >100.
3- Each set includes 5 rows, so the loop should increase by step of 5.

Problem: how to apply the loop and increment (e.g. for i= 1 to 7000 step 5) to cells inside the RC formula?

Thanks again

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[secure]

Thanks for replying roy,
The attached workbook contains 2 sheets, "sheet1" is a data sample with 3 sets as mentioned. Another sheet, "Pilot" includes the calculations I aim. "Pilot" has 3 tables, the first table is directly resulted from data ("sheet1") using "INDEX" formula, the second one compares table 1 to some reference values from table 3 using IF and TREND formula which I use to calculate interpolation.

First question, consider this sample be extended.
Second question, since the end of data sets are unspecified and may vary, can I use "IsNonText" or any other function to determine the end of calculation?

Thanks again,