Using IF/ Do Condition with VBA

[gvreddyhr]

Hi,
Thanks for your ultimate support for my earlier posts, now I require small help in writing code using if, below is the scenario
Ex:- I have sheet1 with full salary details(Xl down), A1 – Emp Number, B1-Emp name, C1 – Gross Salary,D-1 Basic Salary,E1 – HRA,F1-Others,G1-PF,H1-ESI, in sheet2, I have created heads as A1-Emp Name,B1-Emp Nmae,C1-Gross Salary, D1-ESI, now I want to write a code, referring to sheet1 if ESI amount is > 0, then only those applicable employee details should furnish in sheet2.

Can anyone help me in writing the code for this.

Thank you so much in advance.

Regards,
GV Reddy

[JKwan]

Try this out:
[vba]Sub ESI()
Dim LastRow As Long
Dim OutputLastRow As Long
Dim InputSheet As Worksheet
Dim OutputSheet As Worksheet
Dim lRow As Long

Set InputSheet = Worksheets("Sheet1")
Set OutputSheet = Worksheets("Sheet2")
LastRow = FindLastRow(InputSheet, "A")
OutputLastRow = FindLastRow(OutputSheet, "A") + 1
For lRow = 2 To LastRow
If InputSheet.Cells(lRow, "H") > 0 Then
OutputSheet.Cells(OutputLastRow, "A") = InputSheet.Cells(lRow, "A")
OutputSheet.Cells(OutputLastRow, "B") = InputSheet.Cells(lRow, "B")
OutputSheet.Cells(OutputLastRow, "C") = InputSheet.Cells(lRow, "C")
OutputSheet.Cells(OutputLastRow, "D") = InputSheet.Cells(lRow, "H")
OutputLastRow = OutputLastRow + 1
End If
Next lRow

Set InputSheet = Nothing
Set OutputSheet = Nothing
End Sub
Public Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
' This function will fine the last row based on the Column that is sent to it.
FindLastRow = WS.Range(ColumnLetter & "65536").End(xlUp).Row
End Function
[/vba]