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!

 

[Fluff]

Do you have a header row? If so which is the first row with numbers?

 

[scooby33]

Do you have a header row? If so which is the first row with numbers?

I do not have a header row. Row 1 is the first row with numbers.

 

[Fluff]

Ok, how about

Sub scooby()
   With Workbooks("Sentiment Recode").Worksheets("Sheet1")
      With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         .Offset(, 1).Value = .Worksheet.Evaluate(Replace("choose(sign(@)+2,""Negative"",""Neutral"",""Positive"")", "@", .Address))
      End With
   End With
End Sub

 

[scooby33]

Ok, how about

Sub scooby()
   With Workbooks("Sentiment Recode").Worksheets("Sheet1")
      With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         .Offset(, 1).Value = .Worksheet.Evaluate(Replace("choose(sign(@)+2,""Negative"",""Neutral"",""Positive"")", "@", .Address))
      End With
   End With
End Sub

That worked perfectly, thank you I appreciate it!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[scooby33]

Hello,

I am running into an issue where if the cells are blank the output is still neutral which messes up the counts. I have your code copied verbatim from yesterday. Is there a fix for this?

 

[Fluff]

What should col B be if col A is blank?

 

[scooby33]

If possible, can col b be blank as well?

 

[Fluff]

Yup, try

Sub scooby()
   With Workbooks("Sentiment Recode").Worksheets("Sheet1")
      With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         .Offset(, 1).Value = .Worksheet.Evaluate(Replace("if(@="""","""",choose(sign(@)+2,""Negative"",""Neutral"",""Positive""))", "@", .Address))
      End With
   End With
End Sub