Excel Macro to set a Flag

sivaraj43

New Member
I have an excel sheet with three columns- (the employee number, e-mail id and flag).

I need the cells of the flag field to be set to one if the cells of the either the employee number or the email-id is empty. If either of the cells of employee number or the email-id contains an entry, the flag need not be set.

I need to write this in a Macro and not as a formula. Can someone help me out?

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Tom Urtis

MrExcel MVP
Well, you didn't give any details about specific columns or ranges, so if by chance this is column C for Flags, and if by chance your data starts in row 1, try this for starters:

Sub Test1()
Dim LR As Long
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Range("C1:C" & LR).Formula = "=IF(COUNTA(RC1:RC2)<2,1,"""")"
End Sub

If you want the Flag value of 1 to be a constant and not a formula, try this instead:

Sub Test2()
Dim LR As Long
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
With Range("C1:C" & LR)
.Formula = "=IF(COUNTA(RC1:RC2)<2,1,"""")"
.Value = .Value
End With
End Sub

P Sitaram

Well-known Member
Assume the the flag range is C1:C10. Try this code (in the sheet module):

Sub SetFlag()
[c1].Formula = "=if((len(A1)+len(B1)),char(32),1)"
[c1].Copy [c2:c10]
End Sub

Replies
7
Views
132
Replies
8
Views
144
Replies
2
Views
160
Replies
7
Views
256
Replies
13
Views
217

1,195,743
Messages
6,011,395
Members
441,612
Latest member
npasha1

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.

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

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