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.

 

[Steve_]

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.

This should get you going...

Sub test()
    y = Get_Empty_Col("Sheet1", 1, 1)
    MsgBox y
End Sub
Function Get_Empty_Col(TargetSheet, RowNumber, StartColumn)
    Do Until Sheets(TargetSheet).Cells(RowNumber, StartColumn + y) = ""
        y = y + 1
    Loop
    Get_Empty_Col = y + 1
End Function

 

[DanteAmor]

Try this
Change data in red by your information

Sub pasteamount()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim emp As String, f As Range
    
    Set sh1 = Sheets("[COLOR=#ff0000]Leave_Paid[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Master[/COLOR]")
    
    emp = sh1.Range("[COLOR=#ff0000]A2[/COLOR]").Value
    amount = sh1.Range("[COLOR=#ff0000]B2[/COLOR]").Value
    
    Set f = sh2.Range("A:A").Find(emp, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        sh2.Cells(f.Row, Columns.Count).End(xlToLeft)(1, 2).Value = amount
    Else
        MsgBox "[COLOR=#ff0000]emp does not exist[/COLOR]"
    End If
End Sub

 

[gssachin]

Dear Sir,

Macro is working fine, but It pasting "amount" in last empty cell/column whereas I want to give specific column list (i.e. c2, e2, f2....etc) In that list whichever 1st column is empty there only I want to paste "amount" also macro should run continuously until "Leave_Paid" Sheet data in A column not get finished.

I have 1000 Emp List in Which some employee already paid 2 or 3 times Leave Salary and some having 1st time getting leave salary, so accordingly if 1st Leave Sal Column not empty then macro should go for next column (which I column no i will specify) and paste the amount.

 

[DanteAmor]

Dear Sir,

Macro is working fine, but It pasting "amount" in last empty cell/column whereas I want to give specific column list (i.e. c2, e2, f2....etc) In that list whichever 1st column is empty there only I want to paste "amount" also macro should run continuously until "Leave_Paid" Sheet data in A column not get finished.

I have 1000 Emp List in Which some employee already paid 2 or 3 times Leave Salary and some having 1st time getting leave salary, so accordingly if 1st Leave Sal Column not empty then macro should go for next column (which I column no i will specify) and paste the amount.

Try with the following.
The macro assumes that in the "Leave_Paid" sheet the employee are in column A and the amount in column B; and that you will find the master sheet in the "A" column.


But there remain some unknowns, for example, what should the macro do if the employee does not exist in the "master" sheet.
Or what should I do, if there are no longer available columns from the list of specific columns?


Fill in the red line the list of specific columns

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([COLOR=#ff0000]"C", "E", "G", "I", "AL", "AW"[/COLOR])   'specific column list
    
    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
                End If
            Next
            If emptyCol <> 0 Then
                sh2.Cells(f.Row, emptyCol).Value = amount
            End If
        End If
    Next
End Sub

 

[gssachin]

Dear Sir,

Thanks you very much, it perfectly working.

As you said "But there remain some unknowns, for example, what should the macro do if the employee does not exist in the "master" sheet.
Or what should I do, if there are no longer available columns from the list of specific columns?"

If employee not exit in "Master" Sheet then macro should give some Colour to that employee in "Leave_Paid" Sheet so user will get idea that this employee not in list

Also for there is no column available then also if amount cell will colour.

Is it possible ???

Thanks in advance .

 

[DanteAmor]

If employee not exit in "Master" Sheet then macro should give some Colour to that employee in "Leave_Paid" Sheet so user will get idea that this employee not in list

Also for there is no column available then also if amount cell will colour.

Use 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
                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]

Thanks your so much..

 

[DanteAmor]

Thanks your so much..

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

 

[gssachin]

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.