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, you can use the INDEX and MATCH functions in Excel to insert a value into a table based on the row and column numbers.

Here's how you can modify the code to use these functions:

Sub Transfer()

Dim Start_Date As Date Dim End_Date As Date Dim i As Integer Dim Start_Row As Integer Dim End_Row As Integer

Start_Date = Worksheets("Series"). Range("H8") End_Date = Worksheets("Series"). Range("H9")

For i = 18 To 715 If Worksheets("Series"). Cells(i, 4) = Start_Date Then Start_Row = i ElseIf Worksheets("Series"). Cells(i, 4) = End_Date Then End_Row = i End If Next i

For i = Start_Row To End_Row Worksheets("Series"). Cells(i, 3) = Worksheets("INPUTOUTPUT"). Range("C2")

Third Party Application Run Logic Code for processing Inputs

'Results Transfer as below

Worksheets("Series"). Cells(i, 47) = WorksheetFunction.Index(Worksheets("INPUTOUTPUT"). Range("AA87:AA90"), _ WorksheetFunction.Match(i, Worksheets("Series"). Range("B18:B715"), 0), _ WorksheetFunction.Match("AA87", Worksheets("INPUTOUTPUT"). Range("A87:D87"), 0))

Next i End Sub

Answer 2

Dear Sir,

Thanks a lot for your support.

I do not want to do this using VBa code you mentioned as it is very slow execution of macro that we do not want.

I want to do same thing in Excel itself. Is it possible using Index and MAtch and Address Functions.

My probelm statement is I have AA87 value calculated for every i which is Date value and it runs in FOR Loop . For AA87 value , i want to send this value to approproate cell in one table in worksheet . So if row number and coulmn number given is 2, and 2, then it should insert AA87 value in Address (2,2) in same table.

How to do it ?

Below is details of existing code and table

Sub Tranfer ()

Dim Start_Date As Date

Dim End_Date As Date

Dim i As Integer

Dim Start_Row As Integer

Dim endrow As Integer

Start_Date = Worksheets("Series").Range("H8")

End_Date = Worksheets("Series").Range("H9")

For i = 18 To 715

If Worksheets("Series").Cells(i, 4) = Start_Date Then

Start_Row = i

ElseIf Worksheets("Series").Cells(i, 4) = End_Date Then

endrow = i

End If

Next i

For i = Start_Row To endrow

'Worksheets("Series_1").Range("DK4") = i

Worksheets("Series").Cells(i, 3) = Worksheets("INPUTOUTPUT").Range("C2")

Third Party Appplication Run Logic Code for porcessing Inputs

'Results Tranfer as below

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

Worksheets("Series").Cells(i, 48) = Worksheets("INPUTOUTPUT").Range("AA88")

Worksheets("Series").Cells(i, 49) = Worksheets("INPUTOUTPUT").Range("AA89")

Worksheets("Series").Cells(i, 50) = Worksheets("INPUTOUTPUT").Range("AA90")

Next i

Answer 3

Here's a sample VBA code to insert the value "AA87" into cell (2,5) of a table in Excel:

Sub InsertValue() Dim i As Integer Dim j As Integer

i = 2 j = 5

Worksheets("Sheet1"). Cells(i, j). Value = "AA87" End Sub

This code assumes that the table is in a worksheet named "Sheet1". You can change this to the name of the worksheet that contains your table.

This will insert the value "AA87" into cell (2,5) of the table. You can change the values of "i" and "j" to specify the row and column number where you want to insert the value.

Answer 4

I know AA87 which is value to be transferred to table in particular cell here

I know row number as i which is date which is dynamic row number and column number is also fixed.

I want to transfer this to table shown in earlier post.

Suppose, my row numer is 2 and column is 5, so want to transfer/insert this value to cell( 2,5).

How to insert it in Excel automatically,

Excel function or VBA code should find this place and insert this value using excel function or some other methid

Please share smaple code on how we will do it ?

Answer 5

Hi

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Yes, you can use the INDEX and MATCH functions in Excel to insert a value into a table based on a specified row and column number. Here's how:

Enter the value you want to insert (AA87) in a separate cell. In the cell where you want to insert the value, use the following formula: =INDEX(your_table_range, MATCH(row_number, your_table_range[row_header], 0), MATCH(column_number, your_table_range[column_header], 0))

Here, "your_table_range" is the range of cells that make up your table, "row_number" is the number of the row where you want to insert the value, "row_header" is the range of cells that make up the row headers of your table, "column_number" is the number of the column where you want to insert the value, and "column_header" is the range of cells that make up the column headers of your table.

Note that the MATCH function is used to find the position of the row and column based on the row and column number, and the INDEX function is used to retrieve the value from the table at that position. The "0" in the MATCH function specifies an exact match.

Alternatively, you could use VBA (Visual Basic for Applications) to automate this process. You would need to write a VBA macro to do this, which you can access by pressing Alt + F11 in Excel.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.