How to find first empty cell in diff columns (i.e. c5, e5, g5, i5, al5, aw5)

[gssachin]

Dear All,

I have two worksheet (i.e. Emp_Master & Leave_Paid)

I want to update Leave_Sal paid amount from "Leave_Paid" sheet to "Master" Sheet

For Example Leave_Paid sheet a2 having emp no 1111 then Macro to copy that number and search in "Master" Sheet Column "A" and then it should Copy Leave_sal amount from Sheet "Leave_Paid" (i.e. b2) and paste into where the employee in Master Sheet
But Macro has to find 1st Empty Column out of my Range (I.e. c5, e5, f5, h5, ac5, af5 etc) and then past the amount.

 

[DanteAmor]

Dear Sir,

I want to update "Releaving Date" (Emp going on Vacation/EML date). But I want to use two Cols Array (1. Vacation 2. EML).

Instead of Leave_Paid Sheet I will have "Releaving" Sheet which will contain A : Emp No, B : Releaving Date & C : Vac_Type (i.e "Vacation/EML")
Macro has to check whether type is Vacation then used Cols Array 1 or EML then Cols Array 2 and then check emp no in Master sheet and paste "Releaving Date" in 1st Empty Column of that Array

Sorry to trouble you again.

It is not a problem.
But I do not understand the relationship of this requirement with the above.
You could create a new thread and there you explain with examples. Please.

 

[gssachin]

Dear Sir,

Thanks for reply. Somehow I manage to modify your earlier code with my above requirement. and its works fine.

In future I will create new thread for my new requirement.

Thanks again

 

[gssachin]

Dear Sir,

Can I get one more filter added to above codes.

If "Amount" is already entered in given columns or entire columns after selecting entire row of that Employee (in Master Sheet) then macro will give msgbox "EMp No & This amount already entered" else will copy to next column, so we avoid duplicate entry for same employee, tried following codes but not get success.

'TO CHECK AMOUNT ALREADY ENTERED OR NOT
Set F = sh2.Range("A:A").Find(emp, LookIn:=xlValues, LookAt:=xlWhole)
If sh2.Cells(F.Row).EntireRow = .Find(AMOUNT, LookIn:=xlValues, LookAt:=xlWhole) Then
MsgBox (EMP & "AMOUNT ALREADY EXITS")
End If

 

[DanteAmor]

Try this

Sub pasteamount()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim emp As String, f As Range, i As Long, amount As Double
    Dim cols As Variant, emptyCol As Long, j As Long
    
    Set sh1 = Sheets("Leave_Paid")
    Set sh2 = Sheets("Master")
    cols = Array("C", "E", "G", "I", "AL", "AW")
    
    For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
    
        emp = sh1.Range("A" & i).Value
        amount = sh1.Range("B" & i).Value
        
        Set f = sh2.Range("A:A").Find(emp, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            emptyCol = 0
            For j = 0 To UBound(cols)
                If sh2.Cells(f.Row, cols(j)).Value = "" Then
                    emptyCol = Columns(cols(j)).Column
                    Exit For
                Else
[COLOR=#008000]                    If sh2.Cells(f.Row, cols(j)).Value = amount Then[/COLOR]
[COLOR=#008000]                        MsgBox emp & " " & amount & " ALREADY EXITS"[/COLOR]
[COLOR=#008000]                        sh1.Range("A" & i & ":B" & i).Interior.ColorIndex = 6[/COLOR]
[COLOR=#008000]                        Exit For[/COLOR]
[COLOR=#008000]                    End If[/COLOR]
                End If
            Next
            If emptyCol <> 0 Then
                sh2.Cells(f.Row, emptyCol).Value = amount
            Else
                sh1.Range("B" & i).Interior.ColorIndex = 6
            End If
        Else
            sh1.Range("A" & i).Interior.ColorIndex = 6
        End If
    Next
End Sub

 

[gssachin]

Dear Sir,

Thanks you, it perfectly working.

One thing I want know instead of If sh2.Cells(f.Row, cols(j)).Value = amount CAN WE USE FULL ENTIRE ROW FOR SEARCHING SAME AMOUNT IS AVAILABLE OR NOT ???

 

[DanteAmor]

Dear Sir,

Thanks you, it perfectly working.

One thing I want know instead of If sh2.Cells(f.Row, cols(j)).Value = amount CAN WE USE FULL ENTIRE ROW FOR SEARCHING SAME AMOUNT IS AVAILABLE OR NOT ???

Yes, we can.
But I do not know if in the intermediate columns, which are not in the list of specific columns, there may be an equal amount, so I only look for specific columns.

 

[gssachin]

Dear Sir,

Yes, I agree that , but for my knowledge I want use in future in any other situation for that purpose. to search any value in entire row, how to do that...

 

[DanteAmor]

Dear Sir,

Yes, I agree that , but for my knowledge I want use in future in any other situation for that purpose. to search any value in entire row, how to do that...

something like this

Sub pasteamount()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim emp As String, f As Range, i As Long, amount As Double
    Dim cols As Variant, emptyCol As Long, j As Long, f2 As Range
    
    Set sh1 = Sheets("Leave_Paid")
    Set sh2 = Sheets("Master")
    cols = Array("C", "E", "G", "I", "AL", "AW")
    
    For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
    
        emp = sh1.Range("A" & i).Value
        amount = sh1.Range("B" & i).Value
        
        Set f = sh2.Range("A:A").Find(emp, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            emptyCol = 0
[COLOR=#0000ff]            Set f2 = sh2.Rows(f.Row).Find(amount, LookIn:=xlValues, lookat:=xlWhole)[/COLOR]
[COLOR=#0000ff]            If f2 Is Nothing Then[/COLOR]
                For j = 0 To UBound(cols)
                    If sh2.Cells(f.Row, cols(j)).Value = "" Then
                        emptyCol = Columns(cols(j)).Column
                        Exit For
'                    Else
'                        If sh2.Cells(f.Row, cols(j)).Value = amount Then
'                            MsgBox emp & " " & amount & " ALREADY EXITS"
'                            sh1.Range("A" & i & ":B" & i).Interior.ColorIndex = 6
'                            Exit For
'                        End If
                    End If
                Next
[COLOR=#0000ff]            Else[/COLOR]
[COLOR=#0000ff]                MsgBox emp & " " & amount & " ALREADY EXITS"[/COLOR]
[COLOR=#0000ff]                sh1.Range("A" & i & ":B" & i).Interior.ColorIndex = 6[/COLOR]
            End If
            If emptyCol <> 0 Then
                sh2.Cells(f.Row, emptyCol).Value = amount
            Else
                sh1.Range("B" & i).Interior.ColorIndex = 6
            End If
        Else
            sh1.Range("A" & i).Interior.ColorIndex = 6
        End If
    Next
End Sub

 

[gssachin]

Thanks you sir

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.