CODEWITHSUNDEEP
vbaexcel
user3881370
user3881370

Reputation: 1

VBA error in Excel: Invalid Use of Property

I have written the below code in CommandButton_Click(), but it gives the following error:

Compile Error:Invalid Use of Property.

Public Sub SectorNAme()
  Dim score As String, result As Integer
  Dim a_counter As Integer

  For a_counter = 1 To 473
    score = Range("AB" + a_counter).Value

    If score = "Technology & FM" Then
      result = "25"
    ElseIf score = "Operating Equipment & Supplies" Then
      result = "26"
    ElseIf score = "Interiors & Design" Then
      result = "27"
    ElseIf score = "Outdoor & Resort Experience" Then
      result = "28"
    ElseIf score = "HORECA" Then
      result = "29"
    ElseIf score = "Retail & Franchise" Then
      result = "30"
    ElseIf score = "Design" Then
      result = "33"
    ElseIf score = "Health & Fitness Eqpts, P&S" Then
      result = "32"
    ElseIf score = "Recreational Fun & Adventure" Then
      result = "31"
    Else
      result = "35"

    Range("AC" + a_counter).Value

    End If
  Next a_counter
End Sub

Please help me find a solution.

Upvotes: 0

Views: 4863

Answers (1)

djikay
djikay

Reputation: 10628

You have a few small problems:

  1. The error you're getting is at:

    Range("AC" + a_counter).Value

    I assume you meant to do:

    Range("AC" + a_counter).Value = result

  2. This: Range("AB" + a_counter) and this: Range("AC" + a_counter) should be using & and not + to concatenate the strings, otherwise you get a type mismatch, i.e.: Range("AB" & a_counter) and Range("AC" & a_counter).

  3. I've just edited your question and, with better indentation, I also noticed that your End If is after Range("AC" + a_counter).Value, which is wrong. It should be before that, otherwise your result will only be assigned in the Else case and not for any others.

So, your final code should look like this:

Public Sub SectorName()

  Dim score As String, result As Integer
  Dim a_counter As Integer

  For a_counter = 1 To 473
    score = Range("AB" & a_counter).Value

    If score = "Technology & FM" Then
      result = "25"
    ElseIf score = "Operating Equipment & Supplies" Then
      result = "26"
    ElseIf score = "Interiors & Design" Then
      result = "27"
    ElseIf score = "Outdoor & Resort Experience" Then
      result = "28"
    ElseIf score = "HORECA" Then
      result = "29"
    ElseIf score = "Retail & Franchise" Then
      result = "30"
    ElseIf score = "Design" Then
      result = "33"
    ElseIf score = "Health & Fitness Eqpts, P&S" Then
      result = "32"
    ElseIf score = "Recreational Fun & Adventure" Then
      result = "31"
    Else
      result = "35"
    End If

    Range("AC" & a_counter).Value = result
  Next a_counter

End Sub

This should now work as you expect.

Upvotes: 6

Related Questions