Unable to put IF statement in cell sucessfully

[ipbr21054]

Morning,

I enter an IF formula in a cell but after hitting enter it isnt ther.
I click the cell in question followed by selecting the formula space at top of screen.
I enter the IF formula then hit enter.

When i click on the cell i should see the IF formula that i just entered but its empty.
I even tried to grab the marker in the lower right on the cell & drag down but that also didnt do iyt.

Can you advise please.

 

[Joe4]

Is there any VBA code in this workbook?
You could have Worksheet_Change code in the Sheet module that is erasing it.

 

[ipbr21054]

Hi,
Below is the code from one sheet that was affected.
This had at least 12 cells with the issue where another sheet only had say 4.

Having said that i dont see anything here that would affect it, do yo u?

This is what i have done so far.
Cut all the code from the worksheet & just paste on a word doc.
I then added all the IF formulas where needed.
I then checked every cell after i saved it to make sure.
I then pasted back the same code from the word doc & also saved it.

I got on with my other work then some 45 minutes i checked all the cells again.
All are fine & none had been altered etc.

I was unable before the first post to type or paste for it to stay there or even drag it from one cell down.

Private Sub ClearSheet_Click()
Sheet1.Range("A4:H28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").ClearContents
Range("A4").Select
MsgBox "CELLS HAVE NOW BEEN CLEARED", vbInformation
End Sub
Private Sub CommandButton1_Click()
  Sheets("INCOME (2)").Range("C4:D4").Value = Sheets("INCOME (1)").Range("C30:E30").Value
  Sheets("INCOME (2)").Range("E4").Value = Sheets("INCOME (1)").Range("E30").Value
  Sheets("INCOME (2)").Range("F4").Value = Sheets("INCOME (1)").Range("F30").Value
  Sheets("INCOME (2)").Activate
  ActiveSheet.Range("A5").Select
  If Sheets("INCOME (2)").Range("G32").Value <> Sheets("INCOME (1)").Range("G32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "G32 CELLS DO NOT MATCH"
End Sub
Private Sub CommandButton2_Click()
Dim answer As Long, wb As Workbook
    answer = MsgBox("ONLY TRANSFER FIGURES IF ITS THE END OF THE MONTH" & vbNewLine & "" & vbNewLine & "***** DO WE CONTINUE TO TRANSFER THE FIGURES ? *****", vbYesNo + vbCritical, "END OF MONTH TRANSFER QUESTION")
    If answer = vbYes Then
        Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\EBAY\ACCOUNTS\CURRENT SHEETS\SUMMARY SHEET 2020-2021.xlsm")
        Workbooks("ACCOUNTS.xlsm").Sheets("INCOME (1)").Range("E32").Copy
        wb.Sheets("SUMMARY SHEET").Range("I26").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS.xlsm").Sheets("INCOME (1)").Range("F32").Copy
        wb.Sheets("SUMMARY SHEET").Range("I27").PasteSpecial xlPasteValues
        wb.Close True
        
        Else
        Exit Sub
        
        End If
        Workbooks("ACCOUNTS.xlsm").Sheets("INCOME (1)").Range("A5").Select
        Application.CutCopyMode = False
        MsgBox "Summary Transfer Completed", vbInformation, "SUCCESSFUL MESSAGE"
        ActiveWorkbook.Save
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "H"

'   *** Specify start row ***
    If (Target.Row > 3 And Target.Row < 29) Then
          myStartRow = 4
    Else: myStartRow = 29
    End If
'   Use first column to find the last row
    If (Target.Row > 3 And Target.Row < 29) Then
          myLastRow = 28
    Else: myLastRow = 30
    End If
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    Range("A4:H30").Interior.ColorIndex = 2
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   This color will Highlight the row
    If (Target.Row > 3 And Target.Row < 29) Then
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
'   This color will Highlight the column
    Range(Cells(4, Target.Column), Cells(28, Target.Column)).Interior.ColorIndex = 8
    Else
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 3
    End If
'   This color will Highlight the cell in the row
    If (Target.Row > 3 And Target.Row < 29) Then
    Target.Interior.Color = vbGreen
    Else
    Target.Interior.Color = vbRed
    End If
    Application.ScreenUpdating = True
    With ActiveSheet.Range("B4:B28")
        .Font.Size = 11
        .Font.Bold = True
        .Font.Color = vbBlack
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        Range("B4:E28").Borders.LineStyle = xlContinuous
        Range("B4:E28").Borders.Weight = xlThin
        Range("B4:B28").NumberFormat = "@"
    End With
    Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E5:E28")) Is Nothing Then
    Application.EnableEvents = False
    Range("E5:E28").Formula = "=IF(C5="""","""",IF(ISERROR(C5+D5),"""",C5+D5))"
    Application.EnableEvents = True
  End If
  
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("B4:H" & Rows.Count))
    
'   Exit if nothing entered into out target range
    If rng Is Nothing Then Exit Sub
    
'   Loop through all cells in our target range
    Application.EnableEvents = False
    For Each cell In rng
        cell = UCase(cell)
    Next cell
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_Activate()
If Range("B1") = "" Then
Range("A4").Select
Else
End If
End Sub

 

[Joe4]

You do not "Worksheet_Change" and "Worksheet_SelectionChange" code that could be interfering, based on where you are trying to enter formulas.
What cells, exactly, are you trying to place these formulas in?

 

[ipbr21054]

See below

This code is in column D from 4 - 28

=IF(AND(LEN(B4)=14,(MID(B4,3,1)&MID(B4,9,1))="--"),0,"")

This code is in column E 4-28

=IF(C4="","",IF(ISERROR(C4+D4),"",C4+D4))

This code is in column F 4 - 28

=IF(AND(LEN(B4)=14,(MID(B4,3,1)&MID(B4,9,1))="--"),0,"")

An example of the issue was say cell 4-10 were all fine BUT 11 & 12 were empty then 13-28 were all fine.
In other columns the empty cells were dotted about all over in columns D,E & F

 

[Joe4]

OK, I am really confused now. I thought you said the issue was:

I enter an IF formula in a cell but after hitting enter it isnt ther.

Is that not the problem?
I just want to know EXACTLY what cell addresses you were experiencing this behavior in.

 

[ipbr21054]

That is correct.
I put the IF in say cell D7 and seen it in the formula bar as that was where I was typing it.
I hit enter then clicked in D7 but there was no IF formula there.

As mentioned above I removed all the code from the sheet.
Put all the IF formulas in the cells that needed it but previously wouldn’t allow me.
Pasted the code back to the sheet & saved it.

I’ve since checked the cells and so far IF formula is still there.

I had done that before, during & after receiving replies.

 

[Joe4]

You have an awful lot of automated going happening in there. If you are going to use all of that, it is important to keep track of it all, and understand what it is doing and the implications that it will have.

I think this is the portion of your "Worksheet_Change" code that is converting your IF formula to a hard-coded value:

    For Each cell In rng
        cell = UCase(cell)
    Next cell

You have that being applied to columns B:H.

It looks like you probably have that code there to force text entries to be Upper Case.
Should that really be applied to column D, if you are trying to enter formulas there?

If you want it to ignore formulas, try changing it like this:

    For Each cell In rng
        If Not cell.HasFormula Then
            cell = UCase(cell)
        End If
    Next cell

 

[ipbr21054]

I understand what the formulas are doing I just didn’t expect any issues. Best I think just to remove them and see what’s happening. Thank you

 

[Joe4]

Automated code can often cause interference, esepcially if you have a lot of different things going on. These things all need to be considered together, and not just individually on their own.

Did you try making the adjustment I provided and see if that solves your issue?