Results 1 to 2 of 2
Thread: Complicated IF/Then (Excel xp)
2002-10-31, 17:54 #1
- Join Date
- Jan 2001
- Thanked 1 Time in 1 Post
Complicated IF/Then (Excel xp)
I need to search each row of the used.range in a workbook. If I find a hyphen in any cell of columns B to BA i need to look in Row 5. Row 5 will have a single letter, for instance G. Now i need to go back to the cell where i found the hyphen and if any cells in the entire row B to BA have a G in it it needs to be deleted.
ROW 50 has a hyphen
In ROW 5 there is a G
I go back to ROW 50 and search each cell for the G and delete it. So if C50 has BCDGK, i need it to be BCDK.
Thank you for the help.
2002-10-31, 18:58 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
Re: Complicated IF/Then (Excel xp)
You didn't mention what column in row 5 the letter was in, I assumed it was to be grabbed from the column that the hypen was found in ( so if a hyphen is found in Cell Z15, it will look in Z5 for the letter and it will replace that letter in all cells B15 to BA15). If that is not the case change the sLetter variable definition when it finds the hyphen.
Dim sLetter As String
Dim rCell As Range
Dim iRow As Integer
Dim iCol As Integer
For Each rCell In Selection
If InStr(rCell.Value, "-") > 0 Then
iRow = rCell.Row
sLetter = Cells(5, rCell.Column).Value
For iCol = 2 To 53 'Col B = 2, BA = 53
Cells(iRow, iCol).Value = _
Substitute(Cells(iRow, iCol).Value, sLetter, "")