Dynamic Formula Built from Search Criteria

[MJ]

My spreadsheet contains all dynamic information that is either being
copied or created at runtime (objects). The user clicks and gets more
columns to put info in. They save in the end as a regular spreadsheet.
It's only one sheet.

When they "make" their sheet dynamically, it could resemble what i have
written beneath. There could be more or less columns but they are
repeated.

Row 14
TOTAL TOTAL
Row 15 Premium Taxes Premium Taxes Premium Taxes
PREMIUM TAXES
Row 16 250,000 12,000 17,000 1,000 30,000 3,000
=sum( =sum(

What I am looking to do is this. I want to build the formula as the
user builds the sheet. So if they build 3 Premium columns or 30
Premium columns, I want the formula to build based upon these values.

Currently, I am "finding" the values, using the ".find" functionality.
I search for the word "Premium" and then offset the row 1.
Offset(1,0).

My problem is that I assume I need to "loop" through the values in row
16 and include their addresses (not values) in the formula. Of course
the addresses would change.

I am a web programmer!!! This is my first Excel VBA project ever. I
have learned alot, but I really am terrible at it. The info I need now
is the very last "piece" of this solution. I have approx 4,500 lines
of code at this point created during my struggle. All is working well
except this logic.

Can anyone help? Thanks in advance if you can!

 

[MJ]

Sorry the sheet got jumbled up.

Row 14 TOTAL
TOTAL
Row 15 Premium Taxes Premum Taxes PREMIUM TAXES
Row 16 250,000 23,00 150,00 1500 =sum( =sum(

 

[MJ]

I am ALMOST there!!!!! Here is some code I got to "solve" my issue:

With Worksheets(1).Range("a15:z15")
Set c = .Find("Premium", LookIn:=xlValues, MatchCase:=True)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox (c.Offset(1, 0).Address)
' MsgBox (ActiveCell.Address)
ActiveCell.Formula = "'sum(" & c.Offset(1, 0).Address & "+" &
")"

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


Now, the only problem? The formula I am writing dynamically is only
picking up the last entry...... Anyone??

 

[Guest]

Hi,
Try:

With Range("a15:z15")
Dim firstaddress As String, laddr As String, faddr As String
Set C = .Find("Premium", LookIn:=xlValues, MatchCase:=True)
If Not C Is Nothing Then
firstaddress = C.Address
faddr = C.Offset(1, 0).Address
Do
Set C = .FindNext(C)
If C.Address <> firstaddress Then laddr = C.Offset(1, 0).Address
Loop While Not C Is Nothing And C.Address <> firstaddress
End If
End With
Range(laddr).Offset(-1, 2) = "Total Premiums"
Range(laddr).Offset(0, 2).Formula = "=sum(" & faddr & ":" & laddr & ")"

 

[MJ]

oh my GOD Topper.... you are a genius! Something about it just drove
me nuts. Never thought in a million years to add the continuing range
stuff in. Amazing and I thank you very much. Have a great day. I
know I will because this advice you gave me will help me roll this
thing on "home" once and for all......

 

[MJ]

oops - wait a minute. I spoke too soon. This formula will also take
the "totals" info in. I only want the premium in the total premium and
the taxes in the total taxes.....

 

[Guest]

a different approach

Sub y()
Dim premiums As Double, taxes As Double

premiums = 0
taxes = 0
For icol = 1 To 26 Step 1
If Cells(15, icol) = "Premium" Then
premiums = premiums + Cells(16, icol)
lcol = icol
Else
If Cells(15, icol) = "Taxes" Then
taxes = taxes + Cells(16, icol)
End If
End If
Next icol
Cells(15, lcol + 2) = "Total premiums"
Cells(15, lcol + 3) = "Total taxes"
Cells(16, lcol + 2) = premiums
Cells(16, lcol + 3) = taxes
End Sub


Modified find

With Range("a15:z15")
Dim firstaddress As String, laddr As String, faddr As String
Set C = .Find("Premium", LookIn:=xlValues, MatchCase:=True)
If Not C Is Nothing Then
firstaddress = C.Address
faddr = C.Offset(1, 0).Address
Do
Set C = .FindNext(C)
If C.Address <> firstaddress Then
faddr = faddr & "," & C.Offset(1, 0).Address
laddr = C.Offset(1, 0).Address
End If
Loop While Not C Is Nothing And C.Address <> firstaddress
End If
End With

Range(laddr).Offset(-1, 2) = "Total Premiums"
Range(laddr).Offset(0, 2).Formula = "=sum(" & faddr & ")"

 

[MJ]

Okay.... YOU are the coolest! I have so little experience in Excel
VBA, I did not realize I could SUM things with commas in between!!! I
always thought you had to either "plus" everything or have the range.
Thanks a million Toppers! I think today will finish my worksheet! All
I have left, is to reverse this action for if they delete a column..

You are wonderful. And I appreciate it SO much.

 

[Guest]

Thanks for the feedback and apologies for taking so long to get it right.
Hope it all goes well.