none
VBA Excel Macro to replace cell value in the column 3 based on first 2 column conditions.

    Question

  • Hi, 

    i am working on the Macros and i am the point where i really need some help. 
    I need to REPLACE existing cell value in Column 3 where Column 1 and 2 meet the required condition.

    Example: My table range is (A1:C20). 10 rows in column A have date (02/23/2010) another 10 are empty. In Column B i have 20 rows with text (ATTEMPT) and in Column C 20 rows with text (CONNECTION). 
    I need to Replace COLUMN C to from (CONNECTION) to (CONFIRMED) only when Column A has Valid Date and Time and Column B has (Attempt). 

    I hope it makes sense. Any help would be greatly appreciated.

    Thank you very much!
    Wednesday, February 24, 2010 1:33 AM

Answers

  • Hi,

    Try this on a copy of your data.

    Sub X()
        
        Dim rngData As Range
        Dim lngRow As Long
        
        Set rngData = Range("A1", Cells(Rows.Count, 1).End(xlUp))
        For lngRow = 1 To rngData.Rows.Count
            If IsDate(rngData.Cells(lngRow, 1).Value) Then
                If UCase(rngData.Cells(lngRow, 1).Offset(0, 1).Value) = "ATTEMPT" Then
                    If UCase(rngData.Cells(lngRow, 1).Offset(0, 2).Value) = "CONNECTION" Then
                        rngData.Cells(lngRow, 1).Offset(0, 2).Value = "CONFIRMED"
                    End If
                End If
            End If
        Next
                
    End Sub

    Cheers www.andypope.info
    • Marked as answer by Tim Li Monday, March 01, 2010 10:17 AM
    Wednesday, February 24, 2010 9:13 AM