Updating formula from macro

[n35]

Hello again,

So, been trying to make a macro that updates a formula in a cell with values from a loop.

What I am basing this on is an example found on google.

Sub Macro2()
    
x = Range("A1")
y = Range("A2")

Range("B2") = "= sum(C" & x & ":C" & y & ")"

This will insert the formula

=SUM(Cx:Cy)

Where X and Y is start and end of a range of cells, for instance C3 through to C7

The above example works. What I am then attempting to do, is use that as a basis to get a macro working that does something similar but slightly different.

What I have so far is this.

Sub Macro3()
x = 2
Range("BO3") = "=IF(AND(AK3=1;C" & Period & "=1);1)+IF(AND(AL3=1;D" & Period & "=1);1)+IF(AND(AM3=1;E" & Period & "=1);1)+IF(AND(AN3=1;F" & Period & "=1);1)+IF(AND(AO3=1;G" & Period & "=1);1)+IF(AND(AP3=1;H" & Period & "=1);1)+IF(AND(AQ3=1;I" & Period & "=1);1)+IF(AND(AR3=1;J" & Period & "=1);1)+IF(AND(AS3=1;K" & Period & "=1);1)+IF(AND(AT3=1;L" & Period & "=1);1)+IF(AND(AU3=1;M" & Period & "=1);1)+IF(AND(AV3=1;N" & Period & "=1);1)+IF(AND(AW3=1;O" & Period & "=1);1)+IF(AND(AX3=1;P" & Period & "=1);1)+IF(AND(AY3=1;Q" & Period & "=1);1)+IF(AND(AZ3=1;R" & Period & "=1);1)+IF(AND(BA3=1;S" & Period & "=1);1)+IF(AND(BB3=1;T" & Period & "=1);1)+IF(AND(BC3=1;U" & Period & "=1);1)+IF(AND(BD3=1;V" & Period & "=1);1)+IF(AND(BE3=1;W" & Period & "=1);1)+IF(AND(BF3=1;X" & Period & "=1);1)+IF(AND(BG3=1;Y" & Period & "=1);1)+IF(AND(BH3=1;Z" & Period & "=1);1)+IF(AND(BI3=1;AA" & Period & "=1);1)+IF(AND(BJ3=1;AB" & Period & "=1);1)+IF(AND(BK3=1;AC" & Period & "=1);1)+IF(AND(BL3=1;AD" & Period & "=1);1)
+IF(AND(BM3=1;AE" & period & "=1);1)"


End Sub

Now, this code does not work. I have no idea why, but the editor states there is a syntax error. Which I do not get, since it is based directly upon the first mentioned example.
Basically what the macro I want to build is intended to do the following:

Compare two cells, Cx, and AKx and if both cells equal 1, then add one(1) to a BOx, it does this for each of the 29 rows in the spreadsheet. I need this formula to be updated by a macro, so that when the for loop is in the first loop, where x = 1, then it will look at C1, in the next loop, it will look at C2.

That is, for each loop in the macro, it must compare a number of columns where the row number = x.

I hope this is explained good enough. Else I can attach an example or a figure that illustrates how the spreadsheet is setup.

Even shortening the given macro to the following:

Sub Macro3()
x = 2
Range("BO3") = "=IF(AND(AK3=1;C" & Period & "=1);1)"


End Sub

Then I get the following error.

Run-time error '1004':
Application-defined or object-defined error

Please note, I have not included the for loop, since if I cannot get it to work where I just define what X should be, then I am unlikely to get it working with a for loop with shifting values of x.

 

[n35]

Solved this myself, by simply moving the functionality into a macro. Was very simple, not sure why I did not think of this sooner.

Sub CountingComponentGrade() 
 Dim mySheet As Worksheet
    Dim myRange As Range
    Set mySheet = Workbooks("workbook.xlsm").Worksheets("sheet1")
    For y = 3 To 27
        Cells(y, 66) = 0
        For x = 37 To 65
            If Cells(y, x) = 1 Then
                'MsgBox Cells(3, x).Value
                Cells(y, 66) = Cells(y, 66) + 1
            End If
    
        Next x
    Next y
End Sub

 

[Ruddles]

That's what I like most about this forum: post a question and someone always comes up with the answer!