For Each Loop and Functions

[PaulAsaran]

I imagine this is painfully simple, yet all my research and trying to figure it out on my own has led to failure.

I've learned how to create a button in Excel and create code in VBA to have that button change cell values. The problem is that right now I only know how to affect one cell at a time or, at best, a range where all cells are to have the same result. But today I have a column of cells that are taking information from other sheets in the workbook:

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]Pole Strength[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A2),Data!N2,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A3),Data!N3,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A4),Data!N4,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A5),Data!N5,0)[/TD]
[/TR]
</tbody>[/TABLE]
I want the user to be able to write over the calculations with their own numbers, but be able to replace the calculations if they press a "Recalculate" button. I know the general idea of how the button works, having made one for clearing data before, but this is a slightly different animal with some more complicated elements I'm not used to.

My goal is to create a simpler code, one that doesn't require me to specify each and every cell that needs changing. My idea was to use a For Each loop to sequentially increase the row number of the cell for each step of the loop (i.e. Data!A2 > Data!A3 > Data! A4, and so on). Trying to input a formula based on this failed, so I ultimately came up with this:

Private Sub PoleStrength_Click()
    Dim rng As Range, cell As Range, cnt As Integer, A As Range, N As Range
        Set rng = Sheet2.Range("R17:R43")
        cnt = 0
        A = Sheet5.Cells(2 + cnt, 1)
        N = Sheet5.Cells(2 + cnt, 14)
    
    For Each cell In rng
        If Not A Then
            ActiveCell.Value = N
            Else
            ActiveCell.Value = 0
        End If
        cnt = cnt + 1
    Next
End Sub

Note that Sheet2 is the sheet that is being changed by the code and Sheet5 is the "Data" sheet that I'm pulling information from.

This is literally something I slapped together on my own after a few hours of studying various subjects trying to find a good way to achieve my goal. I'm not receiving any errors, but the code also isn't doing anything, so clearly something is wrong (I am once more reminded that I really need to take a proper VBA course).

If someone could point out what I'm doing wrong (I'm sure it's obvious), I'd appreciate it. Would a "Do Until" or simple "For" loop be better?

 

[Fluff]

How about putting the formula back in those cells, would that work?

 

[PaulAsaran]

I forgot to mention that the A column in the Data sheet is returning as "TRUE" or "FALSE" based on a range of criteria. That may be important for the variable definition.

 

[PaulAsaran]

How about putting the formula back in those cells, would that work?

In a way, that's where the problems stems from. The whole reason I started this project was so that I could learn how to enter the formula into a range without having to write code for every cell in the range, because the formula changes cell references based on row. How do I make VBA input the same formula with a different cell reference in a single bit of code instead of a set of code for each of the 27 rows? I know my example above has only four rows, but the real thing? Yeah, that's 27 rows long.

In other words, I don't know how to make a single line or block of code that will do this:
[TABLE="class: grid, width: 30, align: center"]
<tbody>[TR]
[TD="align: center"]Pole Strength[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A2),Data!N2,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A3),Data!N3,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A4),Data!N4,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A5),Data!N5,0)[/TD]
[/TR]
</tbody>[/TABLE]
Instead of this:
[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]Pole Strength[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A2),Data!N2,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A2),Data!N2,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A2),Data!N2,0)[/TD]
[/TR]
[TR]
[TD="align: center"]=IF(NOT(Data!A2),Data!N2,0)[/TD]
[/TR]
</tbody>[/TABLE]
I tried just entering a formula, but couldn't figure out the syntax for making the cell references change as needed.

 

[Fluff]

How about

Sub PaulAsaran()
   Range("R17:R43").Formula = "=IF(NOT(Data!A2),Data!N2,0)"
End Sub

 

[Fluff]

Or, using the way you were trying

Private Sub PoleStrength_Click()
    Dim rng As Range, cell As Range
        Set rng = Sheet2.Range("R17:R43")
    
    For Each cell In rng
        If Sheet5.Cells(cell.Row - 15, 1) Then
            cell.Value = Sheet5.Cells(cell.Row - 15, 14)
        Else
            cell.Value = 0
        End If
    Next
End Sub

 

[PaulAsaran]

Or, using the way you were trying

Private Sub PoleStrength_Click()
    Dim rng As Range, cell As Range
        Set rng = Sheet2.Range("R17:R43")
    
    For Each cell In rng
        If Sheet5.Cells(cell.Row - 15, 1) Then
            cell.Value = Sheet5.Cells(cell.Row - 15, 14)
        Else
            cell.Value = 0
        End If
    Next
End Sub

The first code you suggested did nothing but throw a run-time error at me, but this one does the job. I'm surprised to say I even recognize how it works, and it's not a solution I would have considered at all. Thanks!

 

[Fluff]

What was the error message & number

 

[PaulAsaran]

What was the error message & number

Strange, I saved a backup of the file and tried to original code again and it works just fine now. Maybe I had a typo or something? At any rate, now I know that VBA automatically updates the formula despite being given no instruction to do so, which is good knowledge to have. Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback