excel - Linking two different Excel tables and auto-populate one when populate another

Question

I have two diferent excel formatted tables one near another. First table (green headers) is a table where I have to add some chemical formulas in Column A and Column B will be SUM of all compounds that I will add from second table (yellow headers), which represents Periodic System of Elements!

The formula that I am using in Table 2 (yellow headers) for calculating chemical compounds is this:

=C$2*MAX(IFERROR(IF(FIND(C$1&ROW($1:$99);MolM.[@[Mol. Formula]]);ROW($1:$99);0);0);IFERROR(IF(FIND(C$1&CHAR(ROW($65:$90));MolM.[@[Mol. Formula]]&"Z");1;0);0)) (CSE formula)

What and how I am usually doing this update of new compounds is that I am adding new chemical formulas in Column A manually (that is okay) and then dragging main formula in Table 2 (yellow header) to calculate all elements, and then SUM in column B for the main result!

My question is, is there a possibility to be more automated, just when I type new compound in Column A it will expand as normal table do, but also to auto-expand and calculate rest of compounds, without that I drag the formula manually..?

Hopefully this was clear enough.

Is there any possibility to make this happen? Is the only solution Power Query or?

标签： excelvbaexcel-formulaexcel-2016

Answer 1

I'm making a wild guess here. Say that you write NH3 in A3, and then have it print I2 (value of "N") in I3, and C2 * 3 (value of "H" times 3, "H3") in C3. To then have B3 calculate the total value with =SUM() or similar.

You could have a VBA sub that looks for the value and prints this. Here is a prototype of that:

Sub molFunc(chem As String, formRow As Long)
Dim i As Long, c As String, atoms As Range, a as Range

Set atoms = Range("C1", Cells(1, Columns.count).End(xlToLeft))    
For i = 1 To Len(chem)

    If Not Mid(chem, i + 1, 1) = UCase(Mid(chem, i + 1, 1)) Then 
        c = Mid(chem, i, 2)
        i = i + 1
    Else
        c = Mid(chem, i, 1)
    End If
    
    For Each a In atoms
        If a.Value = c Then
            If IsNumeric(Mid(chem, i + 1, 1)) Then
                a.Offset(formRow - 1).Value = a.Offset(1) * Mid(chem, i + 1, 1)
            Else
                a.Offset(formRow - 1).Value = a.Offset(1)
            End If
        End If
    Next a
Next i
End Sub

Then you can call it from a Worksheet_Change event in the worksheet of your choice.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing And Target.count = 1 Then
        Application.EnableEvents = False
        Call molFunc(Target.Value, Target.Row)
        Application.EnableEvents = True
    End If
End Sub