Results 1 to 16 of 16

Find & Replace macro with condition

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-14-2010
    Location
    oman
    MS-Off Ver
    Excel 2003
    Posts
    384

    Post Find & Replace macro with condition

    Sub abpackage()
    Dim lst As Long
    lst = Range("L" & Rows.Count).End(xlUp).Row
    For i = 2 To lst
        With Range("L" & i)
            If Not .HasFormula Then
                Select Case .Value
                    
                    Case 15: .Formula = "=5*1.5+5*1.5"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
                    Case 16.5: .Formula = "=6*1.5+5*1.5"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
                    Case 18: .Formula = "=6*1.5+6*1.5"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
                
                End Select
            End If
        End With
    Next i
    End Sub
    Dear friends,at the moment I'm using above macro & i want to modify that with new conditions.my requirement is..

    01.macro should check column "G" & column "G" has below values then according to that macro should replace values in column "L"

    ex-
    column "G"
    TP
    EMB
    BHT

    If column "G" has any of above values & same row column "L" like below,
    column "L"
    10
    11
    12

    then macro should change that values like below..

    Case 10: .Formula = "=5*1+5*1"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
    Case 11: .Formula = "=6*1+5*1"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
    Case 12: .Formula = "=6*1+6*1"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"

    02.If column "G" values other than above(TP,EMB,BHT) & same row column "L" like below,

    column "L"
    15
    16.5
    18

    then macro should change that values like below..

    
    Case 15: .Formula = "=5*1.5+5*1.5"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
    Case 16.5: .Formula = "=6*1.5+5*1.5"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
    Case 18: .Formula = "=6*1.5+6*1.5"
                               .Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"

    pls help me to do this.thanks in advance..
    Last edited by johncena; 02-01-2012 at 05:38 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1