PDA

View Full Version : Using IF/ Do Condition with VBA



gvreddyhr
10-31-2011, 10:11 PM
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
11-01-2011, 07:28 AM
Try this out:
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