I first need to find a value in a cell and then store the range of that cell.
the below code in VBA stores the email NOT the range of the cell.
thank you for any help - Eileen
Dim occurRange As Range
Windows(CARDfile).Activate 'cardholder summary file
With Range("Z:Z") 'EMAIL
Do Until X > occurCNT 'x=1 to start
If X = 1 Then
Set occurRange = .Find(What:=emailCK, LookIn:=xlValues, LookAt:=xlWhole) 'stores the range
Else
Set occurRange = .FindNext(occurRange)
End If 'x=1
If occurRange.Offset(0, -21).Value = " " Then 'active status - don't look anymore
cStatus = "A" 'A=active
X = occurCNT 'Range.Offset(RowOffset, ColumnOffset)
Else
cStatus = occurRange.Offset(0, -21).Value 'store last status value unless found active
End If
X = X + 1
Loop 'X loop
End With
the below code in VBA stores the email NOT the range of the cell.
thank you for any help - Eileen
Dim occurRange As Range
Windows(CARDfile).Activate 'cardholder summary file
With Range("Z:Z") 'EMAIL
Do Until X > occurCNT 'x=1 to start
If X = 1 Then
Set occurRange = .Find(What:=emailCK, LookIn:=xlValues, LookAt:=xlWhole) 'stores the range
Else
Set occurRange = .FindNext(occurRange)
End If 'x=1
If occurRange.Offset(0, -21).Value = " " Then 'active status - don't look anymore
cStatus = "A" 'A=active
X = occurCNT 'Range.Offset(RowOffset, ColumnOffset)
Else
cStatus = occurRange.Offset(0, -21).Value 'store last status value unless found active
End If
X = X + 1
Loop 'X loop
End With