CODEWITHSUNDEEP
stringvbaexcelexcel-formula
Ricardo Rosas
Ricardo Rosas

Reputation: 57

Range as string to fill cell with formula

Background: the project plan Excel I'm working on has different categories with different amount of items. I already wrote a macro http://goo.gl/Kjxs9T that will generate the categories with the necessary number of rows between them.

Goal: Within the loop that creates the category, fill the cell with formula (see picture), for which the range would change depending on the amount of rows

enter image description here

The loop looks like this: 

 For i = 1 To RowCounter
     'The offset is used to copy the next category and not the first each time, the counter will make sure it moves the appropriate amount of positions
    Set rng = cate.Range("A1").Offset(CateCount, 0)
    rng.Copy

    'for this one we tell where it should paste the value. For every new loop it needs to move more cells downward, which is why we use the CateCount 3
    Set rng2 = times.Range("B11").Offset(CateCount2, 0)
    rng2.PasteSpecial

    'The CateCount3 will count how many rows we need to add by counting how many times the category we are dealing with now
    'defined by rng there is in the action sheet and then adding 1 one

    CateCount3 = Application.CountIf(Worksheets("All actions Sheet").Range("B:B"), rng)

    'Here we group the layers
    Set rng = Range(rng2.Offset(1, 0), rng2.Offset(CateCount3, 0))
    rng.Rows.Group

    'Create the numbers of the categories using a loop
    Dim CatNum As Integer
    Set rng = cate.Range("A1", cate.Range("A1").Offset(RowCounter, 1))

    CatNum = Application.WorksheetFunction.VLookup(rng2.value, rng, 2, True)
    Dim NumCount As Integer
    NumCount = 0

    For j = 0 To CateCount3
        Set rng = rng2.Offset(NumCount, -1)
        rng = CatNum + NumCount
        NumCount = NumCount + 1
    Next j

    'We need to add one unit to the counter so that for the next loop it will start one category below
    CateCount = CateCount + 1
    'The CateCount2 is used to add the right number of rows for the next loop
    CateCount2 = CateCount2 + CateCount3 + 1
    'We need to "delete" the contents of CateCount3 after every loop
    CateCount3 = 0

Next i

Already I could use the formula within VBA to calculate it, 

Set rng = rng2.Offset(0, 1)
Set rng3 = Range(rng2.Offset(1, 1), rng2.Offset(CateCount3, 1))
rng = Application.Min(rng3)

However, I want the formula to remain in the cell since the table can change at any time and I don't want to always have to update the macro.

Question: How could I translate that VBA formula into a string that stays in the cell? instead of application.min(rng2.offset...... to simply =min(C12:C17)?

Upvotes: 0

Views: 324

Answers (2)

99moorem
99moorem

Reputation: 1983

You could also skip the need for the rng3 variable altogether

rng.formulua = "=Min(" & Range(rng2.Offset(1, 1), rng2.Offset(CateCount3, 1)).address & ")"

Upvotes: 1

mielk
mielk

Reputation: 3940

You can do it like that:

rng.Formula = "=MIN(" & rng3.Address & ")"

Upvotes: 2

Related Questions