Repeating same task for different cell

  • Thread starter Thread starter Tan Arthur via OfficeKB.com
  • Start date Start date
T

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.
 
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
 
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.
 
Back
Top