How to apply an IF statement in VBA to apply to a Range

June 16, 2020 at 14:00:00
Specs: Windows 7
How to apply a formula to a column in VBA

Hello dear volunteers, I need your help please. I need a condition applied to a column range J2:J10001 in VBA but I'm a noob.
It needs to be overwriteable, but if the overwrite is deleted to make the field blank again, I need the initial conditions to be recalculated. The formula I currently use is
=IF(I2="Amount is OK",G2,"")
It works fine and can be overwritten, but if someone makes a mistake and deletes their entry, the field is blank sometimes when it should return to "Amount is OK".


See More: How to apply an IF statement in VBA to apply to a Range

Reply ↓  Report •

#1
June 16, 2020 at 19:18:45
I'm confused.

It sounds like you currently have that IF formula in J2:J10001.

Why would you want it to be "overwriteable"?

In addition, I don't understand how it could return to "Amount is OK" if it contained a formula (not text) beforehand.

Please clarify what you are trying to accomplish.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
June 19, 2020 at 01:33:56
Hi DerbyDad03, I should have clarified, I'd like to move the formula into VBA. Here is what I am trying to do. The sheet has to be locked down and has hidden columns but has a few columns where users can enter data. Column G is a number provided by software as "FalloutData" meaning the spreadsheet checks to see where the problem might have been. I'm running a number of verifications on the data in Column G using formulas in Columns H and I. Collumn J needs to populate from Column G if it is clean or be blank so a Human can enter manually verified data. Currently this overwrites the formula in that cell that transfers Clean G data. Sometimes users enter data in the wrong row or accidentally overwrite the clean data. When they try to correct their mistake, they delete but then the formula is gone. I was Hoping to move the formula into VBA so it pre-populates J if Column I Passes, but lets a user enter data and if they delete the user data, the cell reverts to the result of =IF(I2="Amount is OK",G2,"")

Reply ↓  Report •

#3
June 19, 2020 at 06:32:37
I can't tell if your formula is the same in every cell in Column J, specifically referencing I2 and G2 every time, or if it based on the row in which the formula resides.

e.g.

J2: =IF(I2="Amount is OK",G2,"")
J3: =IF(I3="Amount is OK",G3,"")
J4: =IF(I4="Amount is OK",G4,"")

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
June 19, 2020 at 12:50:33
If I understand what you're trying to do, what about using conditional formatting to show which rows need manual updating, instead of a formula (that gets overwritten by manual entry, as you know)? If you conditionally format so the cells that need manual entry stand out, nothing gets overwritten by the user's entry, and if they put it in the wrong cell they can erase it without doing any damage.

Reply ↓  Report •

#5
June 22, 2020 at 18:52:47
Hi Harryrutland, I'm glad you mentioned the conditional formatting because I've definitely used a lot of it to make the cells in column J change colour (Canadian spelling) depending on what the formula returns, including blank (""). However, we need to pass information to column J from column I. If I is ok, then the cell turns green and populates the cell with information from column G. If the information from Column I is not good, it populates as zero or blank. Our users look for the yellow blank cells and populate them. However, our user testing shows that mistakes happen and they need to delete but then both the formula is gone and the conditional formatting gets stuck on the error colouring the cell red even if the correct information is entered.
Here is some code that a friend wrote that seems to be solving our issue:
Private Sub Worksheet_Change(ByVal target As Range)
    
    'If a cell has a formula in it that you want to let a user overwrite but have the formula replaced if the user deletes an entry so the formula resets in the cell
    'Declare variables
    Dim column, row
    Dim cell As Range
    
    'Disable other events to avoid loops
    Application.EnableEvents = False
    
    ' Go through each cell in a range
    For Each cell In target
      
        ' Get the column and convert it to a letter for ease of use
        ' Also get the row number
        column = Col_Letter(cell.column)
        row = cell.row

        ' Add default values here
        ' Use this as your template for other columns
        If column = "J" And IsEmpty(cell) Then
            '=IF(I2="OK",G2,"")
            cell = "=IF(I" & row & "=""OK"",G" & row & ","""")"
            GoTo skip
        End If
        
        'Repeats the check for similar sets of columns
        If column = "O" And IsEmpty(cell) Then
            '=IF(N2="OK",L2,"")
            cell = "=IF(N" & row & "=""OK"",L" & row & ","""")"
            GoTo skip
        End If
    
skip:
    Next cell
        
    'Re-enable other events
    Application.EnableEvents = True

End Sub


Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

This code seems to be doing the trick for us. Thought I'd share just in case it helps others.


Reply ↓  Report •

#6
June 22, 2020 at 18:57:11
Hi DerbyDad03, thanks for the reply,
Yes, the formula is relative and changes based on the row in which I, G and J are found. Your representation of the series is correct with the row number advancing in series. I see in some of your other posts that you are quite familiar with VBA and it looks like you have helped me in the past. (Thank you) so I thought I'd share the bit of code a friend sent me the other day. He walked me through it over the phone and it seems to be doing the trick. I hope you enjoy it and I hope it can help others with similar problems.
Private Sub Worksheet_Change(ByVal target As Range)
    
    'If a cell has a formula in it that you want to let a user overwrite but have the formula replaced if the user deletes an entry so the formula resets in the cell
    'Declare variables
    Dim column, row
    Dim cell As Range
    
    'Disable other events to avoid loops
    Application.EnableEvents = False
    
    ' Go through each cell in a range
    For Each cell In target
      
        ' Get the column and convert it to a letter for ease of use
        ' Also get the row number
        column = Col_Letter(cell.column)
        row = cell.row

        ' Add default values here
        ' Use this as your template for other columns
        If column = "J" And IsEmpty(cell) Then
            '=IF(I2="OK",G2,"")
            cell = "=IF(I" & row & "=""OK"",G" & row & ","""")"
            GoTo skip
        End If
        
        'Repeats the check for similar sets of columns
        If column = "O" And IsEmpty(cell) Then
            '=IF(N2="OK",L2,"")
            cell = "=IF(N" & row & "=""OK"",L" & row & ","""")"
            GoTo skip
        End If
    
skip:
    Next cell
        
    'Re-enable other events
    Application.EnableEvents = True

End Sub


Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function


Reply ↓  Report •

#7
June 22, 2020 at 20:10:23
Seems that it would have been easier to just use the Worksheet_Change event and put the formula back into the single cell that the user cleared. Unless of course, the users are clearing multiple cells at one time.

But if it works for you, that's all that counts.

message edited by DerbyDad03


Reply ↓  Report •

#8
June 23, 2020 at 16:00:10
Hi DerbyDad03, interesting idea, what would have it looked like with Worksheet_Change?

Reply ↓  Report •

#9
June 24, 2020 at 11:36:22
This code should work when either a single cell or multiple cells in Column J are cleared by the user. However, you may want to rethink your work processes.

Preventing problems is typically a better idea than trying to fix them afterwards.

Private Sub Worksheet_Change(ByVal Target As Range)

'This macro will place the formula in any cell that
'the user manually clears e.g. presses the delete key
'The code should work for both single cells and multiple cells

Dim cell As Range
   
   If Target.Column = 10 Then
    
    For Each cell In Range(Target.Address)
     
      If cell = "" Then
        Application.EnableEvents = False
          cell.Formula = _
            "=IF(I" & cell.Row & "=""Amount is OK"",G" & cell.Row & ","""")"
        Application.EnableEvents = True
      End If
    
    Next
   
   End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question