0

I am really new to vba and struggling with this issue :

I am trying to successively change one cells with data from a table, let the result being calculated and save the result into another table.

basically I would love it to work like that :

+++

  • Manually input data into the base table (say "basetbl")
  • The worksheet calculates the intermediary values with excel formulas
  • The vba macro changes the value of a targeted cells, I believe the code would roughly look like below :
Dim n as Integer, i as Integer n = range("basetbl[#Data]").Rows.Count For i = 1 to n Range("E41") = basetbl(row i column1) ' refresh worksheet to calculate the final value' Application.Calculate 'save the calculated result in cell C57 to the result table (say "resultstbl")' resultstbl[row i, column 2] = Range("C57") i + 1 End For

The reason why I need the worksheet to be refreshed is because the cell E41 is linked to a look up for multiple values in the basetbl, and I need the whole worksheet to be recalculated. I also don't want to input the whole calculation into vba because I need to see the different steps in the calculation, change constants etc... thanks !

Improve this question

1 Answer 1

Reset to default
0

I'm not sure if referencing cells in a named table will work as expected (you may want to test with some dummy data and a few debug.print statements to be sure), but I'm sharing a slightly more refined version of your code below. If you need to read the VBA documentation on cell references for ranges, which might work better if you have trouble, you can find that documentation here.

'declare variables Dim wrksht As Worksheet Dim objLO_basetbl As ListObject Dim objLR_basetbl_rows As ListRows Dim objLO_resultstbl As ListObject Dim n as Integer 'number of rows Dim i as Integer 'counter Dim rngbasetbldata as Range 'basetbl data minus headers Dim rngresultstbldata as Range 'basetbl data minus headers 'set initial variables Set wrksht = ActiveWorkbook.Worksheets("Sheet1") Set objLO_basetbl = wrksht.ListObjects("basetbl") Set objLR_basetbl_rows = objLO_basetbl.ListRows set rngbasetbldata = objLO_basetbl.DataBodyRange set objLO_resultstbl = wrksht.ListObjects("resultstbl") set rngresultstbldata = objLO_resultstbl.DataBodyRange n = objLR_basetbl_rows.Count 'get row count for the basetbl; or, if your table has headers try this instead: 'n = objLR_basetbl_rows.Count - 1 'or 'n = rngresultstbldata.rows.count If NOT n = 0 Then 'check to make sure your table has some rows For i = 1 to n Range("E41").value = rngbasetbldata.cell(i, 1).value 'note: this form of cell reference probably won't work as you expect, you'll need to test and adjust Application.Calculate 'refresh worksheet to calculate the final value rngresultstbldata.cell(i, 2).value = Range("C57").value 'save calculated result from C57 to result table; same as above, this form of cell reference might need adjusting i = i + 1 Next i End If
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hello ! Many thanks I will check the VBA documentation on cell references for ranges

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.