mardi 22 septembre 2015

Exce VBA: How to transfer data to another sheet if not found?

I have three sheets in Excel. Data Sheet, Roles Sheet and Report Sheet.

The program is gonna lookup the data of Data Sheet to Roles Sheet. If the data is not found in the Role Sheet, that data will be copied to Rerport sheet.

But my program has a problem on getting the right data to Report Sheet. It transfers all the data from Data Sheet to Report sheet.

Dim iRowRoleSummary As Integer
Dim sGetRoleData As String
Dim sGetRoleValid As String
Dim sGetRoleSummary As String

iRowCountData = 2
iRowCountValidRoles = 2
iRowRoleSummary = 2

While Sheets("Data").Cells(iRowCountData, 2) <> ""

    While Sheets("Approved Roles List").Cells(iRowCountValidRoles, 1) <> ""
        If Sheets("Data").Cells(iRowCountData, 2) <> Sheets("Approved Roles List").Cells(iRowCountValidRoles, 1) Then
            sGetRoleSummary = Sheets("Data").Cells(iRowCountData, 2)
            Sheets("Report Role Summary").Cells(iRowRoleSummary, 1) = sGetRoleSummary
        End If
        iRowCountValidRoles = iRowCountValidRoles + 1
    Wend
    iRowCountData = iRowCountData + 1
    iRowRoleSummary = iRowRoleSummary + 1
    iRowCountValidRoles = 2
Wend

Aucun commentaire:

Enregistrer un commentaire