Do I Need to Add Else to My Macro?

andybason

Board Regular
Joined
Jan 7, 2012
Messages
217
Office Version
  1. 2016
Hello

I have been building a macro to add an up arrow to cells in a range if criteria is met. I have tried to add another line to add a down arrow if other criteria is met.

The macro works ok when I just have the code for either the up arrow or the down arrow but doesn't work when I have both lines together. The second line to insert the down arrow does insert the arrow but it doesn't seem to take the criteria into account.

I've done some research and I think I need to add either Else or ElseIf in somewhere but no matter what I've tried I can't get it to work.

Can anyone point me in the right direction?

Thank you


VBA Code:
Sub Highlight12MonthVariance()
 Application.ScreenUpdating = False
    Dim cel As Range
    For Each cel In ThisWorkbook.Sheets("Summary").Range("B10:B34")
        On Error Resume Next
        If Application.WorksheetFunction.IsText(cel) = True Then If Sheets(cel.Text).Range("I47") >= Sheets(cel.Text).Range("I44") * 1.1 Then cel.Value = cel.Value & "  " & ChrW(8679)
        If Application.WorksheetFunction.IsText(cel) = True Then If Sheets(cel.Text).Range("I47") <= Sheets(cel.Text).Range("I44") * 0.9 Then cel.Value = cel.Value & " " & ChrW(8681)
    Next cel
 Application.ScreenUpdating = True
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Application.WorksheetFunction.IsText(cel)
You are checking if the cell has text.

If Sheets(cel.Text).Range("I47") >= Sheets(cel.Text).Range("I44") * 1
You will get an error if comparing text to a number.

On Error Resume Next
You won't see the error because you have the above line
 
Upvote 0
Thank you Dave. The cel range will only ever be blank or contain text. Does my code not just check the cel range for text? (Forgive me I'm trying to learn VBA)
 
Upvote 0
"B10:B34" contain people's names or are empty. The macro (supposedly) looks up worksheets with the same name as that range then compares I47 to I44 which are numbers.

I thought my code just tested whether B10:B34 is text. Is that not the case?

Thank you
 
Upvote 0
It looks like you want something like this:

VBA Code:
Sub Highlight12MonthVariance()
 Application.ScreenUpdating = False
    Dim cel As Range
    For Each cel In ThisWorkbook.Sheets("Summary").Range("B10:B34")
        On Error Resume Next
        If Len(cel.Value) <> 0 Then
            If Sheets(cel.Text).Range("I47") >= Sheets(cel.Text).Range("I44") * 1.1 Then
                cel.Value = cel.Value & "  " & ChrW(8679)
            ElseIf Sheets(cel.Text).Range("I47") <= Sheets(cel.Text).Range("I44") * 0.9 Then
                cel.Value = cel.Value & " " & ChrW(8681)
            Else
                ' some other condition here?
            End If
        End If
    Next cel
 Application.ScreenUpdating = True
End Sub
 
Upvote 0
Ah, I see what is happen
VBA Code:
Sub Highlight12MonthVariance()
    Application.ScreenUpdating = False
    Dim cel As Range
    For Each cel In ThisWorkbook.Sheets("Summary").Range("B10:B34")
        On Error Resume Next
        If Application.WorksheetFunction.IsText(cel) = True Then
            If Sheets(cel.Text).Range("I47") >= Sheets(cel.Text).Range("I44") * 1.1 Then
                cel.Value = cel.Value & "  " & ChrW(8679)
            ElseIf Sheets(cel.Text).Range("I47") <= Sheets(cel.Text).Range("I44") * 0.9 Then
                cel.Value = cel.Value & " " & ChrW(8681)
            End If
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub
ing
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top