1

Hi I am new to VBA and macros. I am trying to delete the contents of Cell D3 if Cell B4 = FALSE. If Cell B4 = TRUE then do nothing. This is what I wrote

    Range("b3:b4").Calculate
    Range("B4").Select
    If Range = False Then
        Range("D3").Select
        cell.ClearContents
    End If
    End If
End Sub

I have the calculate at the beginning because I have the sheet set to manual calculations because I don't want everything recalculate.

Am I way off in what I have written?

Community
  • 1
  • 1
Nina
  • 11
  • 1

3 Answers3

1

Try this

Sub Demo()
    Application.Calculation = xlCalculationAutomatic 'set calculation mode to automatic
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")  'change Sheet2 to your data sheet

    'check condition for B4=FALSE
    If Not ws.Range("B4") Then ws.Range("D3").ClearContents
    Application.Calculation = xlCalculationManual   'set calculation mode to manual
End Sub

NOTE : Use of SELECT should be avoided. See this for details.

EDIT : As @Slai pointed out in the comment below, If Not ws.Range("B4") will return TRUE if text of Range("B4")is either False, 0 or (blank). So either you'll have to make sure that only TRUE and FLASE are entered in Cell B4 else add another IF condition in code to check whether the entered value is BOOLEAN or not. Thus update above code as

Sub Demo()
    Application.Calculation = xlCalculationAutomatic 'set calculation mode to automatic
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")  'change Sheet2 to your data sheet

    If VarType(ws.Range("B4")) = vbBoolean Then 'check whether the cell value is boolean
        'check condition for B4=FALSE
        If Not ws.Range("B4") Then ws.Range("D3").ClearContents
    End If
    Application.Calculation = xlCalculationManual   'set calculation mode to manual
End Sub
Mrig
  • 11,612
  • 2
  • 13
  • 27
  • `If ws.Range("B4") = False Then` might be better, because `ws.Range("B4")` can also evaluate to False if it's 0 or Empty – Slai Oct 01 '17 at 12:45
  • @Slai - Thanks for pointing that out, but if I am not wrong `If ws.Range("B4") = False Then` will also be `TRUE` if `ws.Range("B4")=0` so instead `ws.Range("B4") = "False"` should be used. – Mrig Oct 01 '17 at 12:54
  • @Nina - See EDIT in answer. – Mrig Oct 01 '17 at 13:58
  • sorry I overcomplicated it :] I was pretty sure that `0 = False` and `"" = False` would result in False, but don't have Excel to test it. – Slai Oct 01 '17 at 14:08
  • @Slai - Even I didn't check it for blanks and 0's. In fact your comment helped me to work on it other way around, So thanks. :) – Mrig Oct 01 '17 at 14:13
0

You may try Mrig's code. However, I think you may want to know how your code doesn't work. I think the problem is in :

1) If Range = False Then -> Please change it to: If Selection = False Then

2)cell.ClearContents -> Please change it to: Selection.ClearContents

3) Delete the last "End if", because it is excessive.

Myke
  • 1
  • Use of `SELECT` should be avoided. See https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Mrig Oct 01 '17 at 12:15
  • Thank you for telling me where my own code was wrong. It is great to learn this stuff rather than just copying and pasting someone else's work. – Nina Oct 01 '17 at 15:37
0

Mrig already gave you a solution, but to help you in the future you should know that the default property for a range is "value", so you can (as Mrig) test directly against it. ( Ie. If Not ws.Range("B4") is the same as "If Not ws.Range("B4").Value" ). Also there is no need to select the range/cell before accessing its methods, so you can do "Range("D3").ClearContents" without first selecting the cell(s), as long as you have no other reason to select or activate a range.

P.s Values in cells are (unless otherwise stated, with "Option Compare Text" or converting cell values) case sensitive when tested, so it's a good practice to use a same consistent capitalization with ranges too, helps reading the code too.

Stacking For Heap
  • 299
  • 2
  • 9