For Each Loop and Functions

PaulAsaran

New Member
Joined
Feb 11, 2019
Messages
22
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:

Pole Strength
=IF(NOT(Data!A2),Data!N2,0)
=IF(NOT(Data!A3),Data!N3,0)
=IF(NOT(Data!A4),Data!N4,0)
=IF(NOT(Data!A5),Data!N5,0)

<tbody>
</tbody>
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:

Code:
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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
How about putting the formula back in those cells, would that work?
 
Upvote 0
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.
 
Upvote 0
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:
Pole Strength
=IF(NOT(Data!A2),Data!N2,0)
=IF(NOT(Data!A3),Data!N3,0)
=IF(NOT(Data!A4),Data!N4,0)
=IF(NOT(Data!A5),Data!N5,0)

<tbody>
</tbody>
Instead of this:
Pole Strength
=IF(NOT(Data!A2),Data!N2,0)
=IF(NOT(Data!A2),Data!N2,0)
=IF(NOT(Data!A2),Data!N2,0)
=IF(NOT(Data!A2),Data!N2,0)

<tbody>
</tbody>
I tried just entering a formula, but couldn't figure out the syntax for making the cell references change as needed.
 
Upvote 0
How about
Code:
Sub PaulAsaran()
   Range("R17:R43").Formula = "=IF(NOT(Data!A2),Data!N2,0)"
End Sub
 
Upvote 0
Or, using the way you were trying
Code:
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
 
Upvote 0
Solution
Or, using the way you were trying
Code:
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!
 
Upvote 0
What was the error message & number
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top