copy problem

  • Thread starter Thread starter Mark K
  • Start date Start date
M

Mark K

I am trying to search all of column I for the values "---" (3 dashes)
when i find a match i want to replace whats in Ix with whats in Kx (x being
the row number)
then i want to put a 1 in Kx

I would like to loop through all of I to find the dashes.

Any help would greatly be appreciated
Mark
 
One way:

Public Sub FindThreeDashesAndReplace()
Dim rFound As Range
With Columns("I")
Set rFound = Columns("I").Find( _
What:="---", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
Do Until rFound Is Nothing
With rFound
.Value = .Offset(0, 1).Value
.Offset(0, 1).Value = 1
End With
Set rFound = .FindNext
Loop
End With
End Sub
 
Dim rng As Range
Set rng = ActiveSheet.Range("I1:I" &
UsedRange.Rows.Count)

Dim cell As Range
For Each cell In rng
If InStr(1, cell.Value, "---") <> 0 Then
cell.Value = cell.Offset(0, 2).Value
cell.Offset(0, 2).Value = 1
End If
Next

-Brad
 
Back
Top