Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    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.

    COL T

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Complicated IF/Then (Excel xp)

    Try this.

    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.


    <pre>Option Explicit

    Sub ReplaceLetter()
    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 = _
    Application.WorksheetFunction. _
    Substitute(Cells(iRow, iCol).Value, sLetter, "")
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts