VBA Excel word search and copying formulas

Question

I'm searching for a VBA macro for Excel, which can detect the word "mean", in column A. After this it would copy the yellow row with the formula in C to J. The formula counts the average from one row after the last "mean" to the next =AVERAGE (C1323:C1437)

after every sixth mean there also needs to be Area and 150 copyied two rows after mean and I and J Need to be changed. Consequently I and J would refer to the cell A1441 in this case (=G1439/C1439*$A$1441) till the end of the file.

I'm not quite sure if it's easy or not but I'm totally overchallenged. I would be very thankful for help.

Sub Makro1()
'
' Makro1 Makro
'
' Tastenkombination: Strg+q

   strSearchWord = "Mean"
i = Application.WorksheetFunction.CountIf(Range("A:A"), strSearchWord)
Y = 2
For x = i To 0
i = Application.WorksheetFunction.Match(strSuchWort, Range("A:A"), 0)
     Range("C" & i).Select

Application.CutCopyMode = False
      ActiveCell.FormulaR1C1 = "=AVERAGE(R[-147]C:R[-1]C)"  ' that's still wrong, should be something like i-y?
    Selection.AutoFill Destination:=Range("C" & i:"J" & i), Type:=xlFillDefault
    Range("CY:JY").Select
i = Y

'for each fifth i
 'Range("A" & i + 3).Select
  '  ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-6]*R2159C1"

Next x

End Sub

it's still wrong, but my first draft.

@stucharo the Area correction is difficult to describe I've added a better Picture with formulas. I hpe that now it's understandable

Answer 1

You clearly have a long list of data and want to automate the creation of the rows and formulas you describe.

It is possible write VBA to scan through the data and modify the formulas etc but first I would question if this is the best approach to give you what you need.

Excel has a feature called "pivot tables" which essentially allows you to summerise data in a list.

for instance if the list had one row for each city in the world and gave the population in the city, and a column gave which country it was in. A pivot table could be used to create the average population for a country of the countries cities. I suspect you are doing this sort of thing.

If you don't know about pivot tables you should find out about them. See here

In your case your mean row is summeriseing data in the rows above it. To use pivot tables you would have to have a column that defined which group each row is in. You pivot table would sue this column as a row summary and you would then create the average for all the other column.

@Nathalie. It's hard to help without knowing more. eg Is the data delivered with the mean text already inserted. It looks like column A has a number the represent the row number within the group (and this could be used by a formula to create the "Group Name" column you need for pivot tables.

You can get the pivot tables to do the area adjustment by:

1. Creating a new set of columns which contains formulas that cause the values in columns C to J to be copied except for when it is the 6th set of data in which case you adjust the values in C to J accordingly).

   2. You probably need to introduce columns that:

   3. A. give the "group name" B. give a count of which group it is in so every 6th you can do the adjustment you need.

4 by using pivot tables and basic techniques you will find it easie rot update the refresh the data, should you need to.

Answer 2

If your line ActiveCell.FormulaR1C1 = "=AVERAGE(R[-147]C:R[-1]C)" needs to change the number of rows betwen means each time then you'll need to add a variable as you comment suggests. Also, just writing the string to the cells value (ActiveCell.Value) means that you will see it written as a formaula when you click the cell in the workbook (and it'll highlight the range etc.). You could try replacing it with:

ActiveCell.Value = "=AVERAGE(R[" & i - Y & "]C:R[-1]C)"

although since I can't see the first row of your sheet I'm not certain that'll give you the correct range of rows each time.

If your row number is likely to change and you are copying over the same number of columns each time then it might also be just as easy to write the formula directly to cells within a loop, rather than explicitly copying it.

Adding text after every 6th "mean" would require you to keep count of how many means had passed so far. This can be done by incrememnting a counter variable and using the Mod operator will tell you the remainder after a division. Therefor numberOfMeans Mod 6 will give you the remainder when divided by 6 and when this equals zero you know you have a multiple of 6.

I've tried to capture all this into the code below.....

Sub Test()

Application.ScreenUpdating = False

Dim startRow As Integer
startRow = 2
Dim endrow As Integer
endrow = Range("A2").End(xlDown).row

Dim lastMeanRow As Integer
lastMeanRow = startRow - 1
Dim areaRow as Integer
areaRow = lastMeanRow + 3
Dim meanCounter As Integer
meanCounter = 0

Dim avgColHeight As Integer
Dim col As Integer
Dim row As Integer

'Check each row in the sheet
For row = startRow To endrow
    'Cols i and j in every row need to be modified
    For col = 9 To 10
        Cells(row, col).Value = "=RC[-2]/RC[-6]*R" & areaRow & "C1"
    Next col

    'If column 1 of that row contains "mean" then
    If Cells(row, 1).Value = "mean" Then

        'Calculate the column height to average over....
        avgColHeight = row - lastMeanRow - 1
        '...and loop through each of the columns....
        '(including i and j to add average)
        For col = 3 To 10
            '....inserting the averaging formula.
            Cells(row, col).Value = "=AVERAGE(R[-" & avgColHeight & "]C:R[-1]C)"
        Next col

        'Then increment the counter to keep track of the number of means
        meanCounter = meanCounter + 1
        'If the number of means is a multiple of 6 then
        If (meanCounter Mod 6 = 0) Then
            'insert the "Area" and "150" strings
            Cells(row + 2, 1).Value = "Area"
            Cells(row + 3, 1).Value = "150"
            areaRow = row + 3
        End If

        'Finally change the lastMeanRow to the mean row we have just processed.
        lastMeanRow = row

    End If
'Do it again until we reach the end of the data
Next row

Application.ScreenUpdating = True

End Sub

I also noticed your point on the value of area changing periodically. Writing this programatically, as above, will aloow you to add some logic over the value of "Area" and when it changes.