Excel Function and VBA Coding

Question

Dear Sir,

I want to know if there is a function in Excel or VBA feature in Excel wherein we can insert particular value say cell no AA87 value in Table of Cells like below table in appropriate cell based on row and column number ,

Column Number is fixed say 35 and row number is dynamic like i ( Date Value)

How can i transfer AA87 value to appropriate cell in below table , if i know row and column number clearly and is there any excel function whcih can insert these values in below table ?

Answer 1

Yes, it is possible to insert a value into a cell based on its row and column number using Excel functions.

Here's an example of how to use the INDEX and MATCH functions to insert a value into cell (i, 47) of the "Table1" in your worksheet:

=INDEX(Table1, i, MATCH(47, A1:Z1, 0))

In this formula, the MATCH function searches the first row of the table (A1:Z1) for the value 47 and returns the column number. The INDEX function then returns the value in the cell at the specified row (i) and column (returned by MATCH).

Note: Replace "47" with the actual column number and "i" with the actual row number in the formula.

You can also use this formula in a loop for all the values of i to get the desired result.

Answer 2

Worksheets("Series"). Cells (i, 47) is the cell in worksheet. wherein i will need to put some function like Excel Function and refer it to AA87 value.

Column 47 is fixed actually. How can i achieve this.

Even if i change the code to copy paste, still it will consume lot of time in VBA right ?

Worksheets("INPUTOUTPUT"). Range("AA87").copy

Worksheets("Series"). Cells (i, 47).pastespecial

Only solution is to do this tranfer in Excel with some workaround only,

Answer 3

Dear Sir,

Thanks a lot for your advice on this.

See below code.

Worksheets("Series"). Cells (i, 47) = Worksheets("INPUTOUTPUT"). Range("AA87")

It runs for all i values (row numbers).

For each row number, AA87 will have new value as well as i will have new value so Worksheets("Series"). Cells (i, 47 will take new value.

Your above code of Index and match will not work for this.

I do not want to use above VBA code as it is running very slow .

How can we do this using Excel or Excel Function only.

Please advice. Your advice has helped me a lot uptill now.

IS there any feature in Excel wherein it will insert particular value in cell based on its row and column number ?

IS it possible to do that ?

Answer 4

You can use the INDEX and MATCH functions to achieve this. The INDEX function will return the value in a cell based on the row and column number, and the MATCH function will return the row or column number of a given value in a table.

For example, if you have a table named "Table1" in your worksheet and the data starts from cell A1, you can use the following formula to insert the value of "AA87" into cell (i, 47) of the table:

=INDEX(Table1,i,MATCH(47,A1:Z1,0))

Replace "47" with the actual column number, and "i" with the actual row number. The MATCH function will search the first row of the table (A1:Z1) for the value "47" and return the column number. The INDEX function will then return the value in the cell at the specified row (i) and column (returned by MATCH).

This way, you can insert the value of "AA87" into cell (i, 47) of the table in your worksheet using an Excel formula without using VBA.

Answer 5

Dear Sir,

AA87 value is dynamic value which is calculated for every iteration and needs to insert in appropriate cell based on row and column number is i is row number and 47 is the column number here.

Worksheets("Series").Cells(i, 47) = Worksheets("INPUTOUTPUT").Range("AA87")

I do not want to use above code as it is working but slowing doen the macro a lot. I want to do these things in excel itself using excel functions like address, Index and Match .

Did you get my point ?

If i get the address of Worksheets("Series").Cells(i, 47) as say C22, How to insert Value of AA87 in C22 for first i.

For second i, AA87 will have new value and in place of C22, we will have C23, wherein C22 and C23 are cell addresses in table

So how to insert AA87 value in C22 cell using Excel Fucntion?

Regards,