CODEWITHSUNDEEP
excelvbaloops
François Pitois
François Pitois

Reputation: 1

Successively change cell value based on table content (VBA)

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 :

+++

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 !

Upvotes: 0

Views: 340

Answers (1)

Word Nerd
Word Nerd

Reputation: 136

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

Upvotes: 0

Related Questions