Convert Numeric List in Column A to Say Positive, Negative, or Neutral in Column B

[scooby33]

Hello,

I am very new to VBA and Macros, and I am trying to get the values in column A which are made up of positive and negative numbers to be converted to "positive," "negative," or "neutral." in column B.

Column A Column B
-1 negative
0 neutral
3 -----> positive
-2 negative
4 positive

I currently do not have any code to output neutral, I am just trying to get to get this working before I add that in.

Here is my code below:
Sub SentimentRecode()
Workbooks("Sentiment Recode").Worksheets("Sheet1").Activate

Dim cell As Range
Dim n As Negative
Dim p As Positive

For Each cell In Range("A:A")
If cell.Value < 0 Then
Range("B:B").Value = n
Else
Range("B:B").Value = p
End If
Next cell
End Sub

I have tried about 10 different iterations of code to try and get the output I want, I am currently getting an error that says compile error: user-defined type not defined. Any help would be greatly appreciated!

 

[scooby33]

Worked great, thank you!

 

[alansidman]

Here is an alternative solution using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Column1] > 0 then "Positive" else if [Column1] = 0 then "Neutral" else if [Column1] < 0 then "Negative" else if [Column1] = null then null else "Neutral"),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"Custom", ""}})
in
    #"Replaced Errors"

 

[Fluff]

You're welcome & thanks for the feedback.