Repeating same task for different cell

[Tan Arthur via OfficeKB.com]

Hi All ,
I need to repeat the below task at different cell . E.g C8 ,D20 , C21 ...

Dim sRange As String
sRange = "C8"
Range(sRange).Select
If IsEmpty(ActiveCell) = True Then ?..
Else ??
End If

Would appreciate someone can help , thanks.

 

[Vikrant Vaidya]

modify this script to a function as follows

Private Function modval(row,column)
Dim sRange As String
sRange = worksheets("your worksheet name").cells(row,column)
Range(sRange).Select
If IsEmpty(ActiveCell) = True Then ?..
Else ??
End If

Then in the cell you put value which is to be manipulated just pass the row
and column to this function like
=modval(8,3) for cell c8

vikrant

 

[JE McGimpsey]

How will you determine which cells?

If in advance, here's one way:

Public Sub test1()
Dim rCell As Range
For Each rCell In Range("C8,D20,C21")
If IsEmpty(rCell.Value) Then
'?..
Else
'??
End If
Next rCell
End Sub

If you want it to run on all selected cells:

Public Sub test2()
Dim rCell As Range
If TypeName(Selection) = "Range" Then
For Each rCell In Selection
If IsEmpty(rCell.Value) Then
'?..
Else
'??
End If
Next rCell
End If
End Sub


Note that selection/activation is not necessary. Using the range objects
directly makes your code smaller, faster, and IMO, easier to maintain.

 

[Tan Arthur via OfficeKB.com]

Hello McGimpsey,
thanks for your help .