Check condition and copy data?

[adamasmay]

Form file as attached.

I want to check column AI & column AJ data have same or not (If found some rows blank = same)

If found not match , I want to check column AccRole that value = Insured or Payer.
- If column AccRole = Insured ---> I want to copy data from column LFNAME to column AccountFile.
- If column AccRole = Payer ---> I want to copy data from column Name payer to column AccountFile.


Remark : But If in column AccountFile have data already , I don't have to do anything about it.

Please guide me about it.

 

[LongToast]

is Input worksheet same with Output wroksheet?

 

[adamasmay]

I want output in same sheet input.

I separate output sheet for show output data only.

 

[LongToast]

I want output in same sheet input.

I separate output sheet for show output data only.

Add one sheet more, pretty much like this :

add one button to it, just call it "Validate" (or what ever you want)

Tab Developer > (on controls category) select insert > choose Button from Form Control

click New and put this code :

Sub Button1_Click()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
    Dim oWs As Worksheet
    Dim lRow As Long
    Dim i As Long
   
    Set oWs = ThisWorkbook.Sheets("YOUR INPUT SHEET NAME")
   
    lRow = oWs.UsedRange.Rows.Count
   
    For i = 2 To lRow
        If oWs.Range("AI" & i) <> vbNullString And oWs.Range("AJ" & i) <> vbNullString Then
            If oWs.Range("AX" & i) = "Payer" Then
                If oWs.Range("AM" & i) = vbNullString Then
                    oWs.Range("AM" & i) = oWs.Range("BB" & i)
                    oWs.Range("AM" & i).Font.Color = vbRed
                End If
            Else
                If oWs.Range("AM" & i) = vbNullString Then
                    oWs.Range("AM" & i) = oWs.Range("BC" & i)
                    oWs.Range("AM" & i).Font.Color = vbRed
                 End If
            End If
        End If
    Next i
   
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
    Set oWs = Nothing
End Sub

change SHEET NAME to your Input sheet name.

 

[LongToast]

Add one sheet more, pretty much like this :

View attachment 55363

add one button to it, just call it "Validate" (or what ever you want)

Tab Developer > (on controls category) select insert > choose Button from Form Control

View attachment 55364


click New and put this code :

Sub Button1_Click()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
  
    Dim oWs As Worksheet
    Dim lRow As Long
    Dim i As Long
  
    Set oWs = ThisWorkbook.Sheets("YOUR INPUT SHEET NAME")
  
    lRow = oWs.UsedRange.Rows.Count
  
    For i = 2 To lRow
        If oWs.Range("AI" & i) <> vbNullString And oWs.Range("AJ" & i) <> vbNullString Then
            If oWs.Range("AX" & i) = "Payer" Then
                If oWs.Range("AM" & i) = vbNullString Then
                    oWs.Range("AM" & i) = oWs.Range("BB" & i)
                    oWs.Range("AM" & i).Font.Color = vbRed
                End If
            Else
                If oWs.Range("AM" & i) = vbNullString Then
                    oWs.Range("AM" & i) = oWs.Range("BC" & i)
                    oWs.Range("AM" & i).Font.Color = vbRed
                 End If
            End If
        End If
    Next i
  
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  
    Set oWs = Nothing
End Sub

change SHEET NAME to your Input sheet name.

Sub Button1_Click()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim oWs As Worksheet
    Dim lRow As Long
    Dim i As Long
    
    Set oWs = ThisWorkbook.Sheets("YOUR INPUT SHEET NAME")
    
    lRow = oWs.UsedRange.Rows.Count
    
    For i = 2 To lRow
        If oWs.Range("AI" & i) <> vbNullString And oWs.Range("AJ" & i) <> vbNullString Then
            If oWs.Range("AM" & i) = vbNullString Then
                If oWs.Range("AX" & i) = "Payer" Then
                    oWs.Range("AM" & i) = oWs.Range("BB" & i)
                Else
                    oWs.Range("AM" & i) = oWs.Range("BC" & i)
                End If
                
                oWs.Range("AM" & i).Font.Color = vbRed
            End If
        End If
    Next i
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    Set oWs = Nothing
End Sub

Sorry, i change the code little bit

 

[adamasmay]

I want add 1 requirement , Some row in column AM have text -

If column AI have data but column AJ have data = - -----> I want to check column AccRole and write data same condition above.

Please guide me more for solve it.