CODEWITHSUNDEEP
excelvba
Munir Abrar
Munir Abrar

Reputation: 81

VBA Excel — Insert symbol based on cell value

I need to work with Excel to which I am absolutely new. I am looking for a VBA solution to speed up some daily work. Here is my case: I need to check cells of Column C which has negative, positive and netural values and then insert sysmbols in corresponding cells of Column B.

For instance, if C4 has a positive value (2,345), B4 should have &u& as symbol. If C5 has negative value (-12.98), then B5 should have &d& as a symbol. And if C6 has a netural value (0.000), then B6 should be inserted with &n& as a symbol.

An empty B column already exists and values are all in Column C.

Upvotes: 0

Views: 1752

Answers (2)

eirikdaude
eirikdaude

Reputation: 3256

As Kyle says, all you need for this is a simple (well, nested) if-formula:

=IF(C1<0;"&d&";IF(C1>0;"&u&";"&n&"))

The format is pretty straightforward, the if has three parts separated by semicolons. The first is the logical test, what you test for, in this case if the value in C1 is smaller (or larger) than 0. The next is the value you want in the cell if the the statement in the test is true, and finally what to put in if it is false.

In this case, we test again if C1 is not smaller than 0, to see if it is larger than zero, that is why there is another if-statement inside the first one.

To apply the formula to your entire column, just copy it down the entire way, and the cell it refers to should update automatically.

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Give this a try:

Sub WhatEver()
    Dim C As Range
    Set C = Intersect(ActiveSheet.UsedRange, Range("C:C"))
    For Each cc In C
        ccv = cc.Value
        If ccv <> "" Then
            If ccv = 0 Then
                cc.Offset(0, -1).Value = "&n&"
            ElseIf ccv > 0 Then
                cc.Offset(0, -1).Value = "&u&"
            Else
                cc.Offset(0, -1).Value = "&d&"
            End If
        End If
    Next cc
End Sub

Upvotes: 0

Related Questions