0

Quick Q:

Sub Ex()
Worksheets("ABC").Activate
If Range("B10").Text IsNot "USA" Then
Range("B11").Value = "Domestic"
Else
End If
End Sub

I am new to VBA, and have not picked up what I have done wrong here. I am trying to set up values in B11 such that if B10 is set to anything else other than "USA" then the value in must be "Domestic".

Bonus Question: Ideally I would like to be able to create a list in B11 if I select USA. How can I do this with data validation?

user9078057
  • 187
  • 1
  • 6
  • As mentioned by John, `IsNot` is an operator that is not available in VBA, only in VB.NET. BTW, `IsNot` operate on Object references, I'm not sure it would work on basic type `String`. – Vincent G Aug 13 '18 at 12:54
  • 1
    For the "Bonus Question": You can either use [Range.Validation.Add](https://msdn.microsoft.com/en-us/VBA/excel-vba/articles/validation-add-method-excel) or [Create conditional drop-down lists](https://www.microsoft.com/en-us/microsoft-365/blog/2009/11/24/create-conditional-drop-down-lists/) – Chronocidal Aug 13 '18 at 13:58

1 Answers1

3

IsNot is a VB.Net rather than a VBA operator. Instead, use <> (which corresponds to != in many other languages):

Sub Ex()
    Dim ws As Worksheet
    Set ws = Worksheets("ABC")
    If ws.Range("B10").Text <> "USA" Then
        ws.Range("B11").Value = "Domestic"
    End If
End Sub

I made a couple of other changes: I introduced a worksheet variable and used it to qualify the Range() (rather than relying on activation) and I dropped the spurious Else.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • 1
    Why did you introduce the worksheet variable rather than activation? – user9078057 Aug 13 '18 at 12:56
  • 1
    Failing to qualify ranges by worksheet is a bad habit (which I am often guilty of) that leads to bugs sooner or later. Furthermore, things like `select` and `activate` are seldom needed in VBA. Better to directly use the Excel Object Model rather than mimicking user actions. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). – John Coleman Aug 13 '18 at 12:59
  • Hi @user9078057, [here are some examples of why you shouldn't use `.select` or `.activate`](https://www.businessprogrammer.com/power-excel-vba-secret-avoid-using-select/) – Marcucciboy2 Aug 13 '18 at 13:00
  • You might also want to avoid `Text` - that is what is displayed in the cell rather than the value it contains. Usually the same, but can be `#####` (for dates with a small column width), or if cell has custom format to display something different. – Darren Bartrup-Cook Aug 13 '18 at 13:19