Double Autofill formula + dynamic column sum

[juniorcomplete1]

Hi! I have seen a few autofill vba codes, all very helpful, but what if the autofill needs to skip one row in order to populate with the sum of the column just autofilled? based on a value in column A ? And then continue to autofill and then again sum back the second auto fill data?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Some more details of exactly what you are trying to do should help.

 

[juniorcomplete1]

Thanks! Version 2016 office pro. The picture attached shows the sintax required. The challenge is adding the sum row midway an autofil proceedure Based on criteria in column A. So in this case: autofill formula unless criteria in column a = test1 then sum to this point.

 

[Peter_SSs]

Thanks for updating your details.

The picture attached shows the sintax required.

Well, it doesn't really - not to me anyway. There is no detail of exactly what you are starting with and what the code has to do.

Do you already have formulas in row 2 and not row 3 before the code is run?
Or is your existing code putting all the formulas in? If so, could we see the code?

Am I correct in thinking that there would always be at least one blank row in column A between the non-blank values?

 

[juniorcomplete1]

Hi! Your right totally missed allot of crucial details. Have attached a more detailed pic and description.

Sub FormulaAdd
1r = Cells.Find("*", Cells(1,1), x1Formulas, x1Part, x1ByRows, x1Previous, False).Row

Range(“F4”).formula="=C4*0.2125/D4"
Range("F4").Autofill.range("F4:F" & 1r)"

End Sub

This would autofill column F because the columns A:F change in length. (currently ending on row 11, but could be anything, with no data below row 11)

So question is how to create a sum in cells F7 and F11, bearing in mind the dynamic nature of the length of A:F?

 

[Peter_SSs]

Could you please copy/paste your actual code if you have working code.
The code that you have posted is riddled with typos and/or syntax errors. It is not valid code.

What about my other question:

Am I correct in thinking that there would always be at least one blank row in column A between the non-blank values?

 

[juniorcomplete1]

Could you please copy/paste your actual code if you have working code.
The code that you have posted is riddled with typos and/or syntax errors. It is not valid code.

What about my other question:

Hi! Yes there will always be at least one blank row in column A. The formulas will all be in VBA so no top line formula row.
The code pasted is an attempt to 'TRY' and make a formula drag down work, but got lost and when thinking of how to add the sum rows, reached out for help

 

[Peter_SSs]

Ok, for the layout in your last image, try this with a copy of your workbook.

Sub Add_Formulas()
  Dim lr As Long
  Dim rA As Range
  
  lr = Range("B" & Rows.Count).End(xlUp).Row
  Range("F4:F" & lr).Formula = "=C4*0.2125/D4"
  For Each rA In Range("A4:A" & lr).SpecialCells(xlBlanks).Areas
    rA.Cells(rA.Rows.Count + 1, 6).Formula = "=SUM(" & rA.Offset(, 5).Address & ")"
  Next rA

For the future, to save helpers having to manually type sample data to test with, please consider using XL2BB

 

[juniorcomplete1]

WOW! Genius! Thank you so much! Thanks for sharing XL2BB didnt know this existed.

 

[juniorcomplete1]

Ah hit a problem. one of the data sets has at least 1 blank space for the top sum (test1) but no black space for bottem sum (test2). Would it be possible to code it so that it looks for the names TEST1 and TEST2? what do you suggest?