How to insert data by using VBA in excel

Question

I have 3 sheets (sheet1,sheet2,sheet3) in sheet1 has all user ID, sheet2 has logon user ID and sheet3 is empty. The point is... I need to put not logon user ID into sheet3 but my code fail. If it's a stupid question that because I'm newbie with VBA

Here my code:

Sub NotLog()

Dim c1 As Range

Dim c2 As Range

Dim c3 As Range

Dim sh1 As Worksheet

Dim sh2 As Worksheet

Dim sh3 As Worksheet

    Set sh1 = ThisWorkbook.Sheets("ALl USer")
    Set sh2 = ThisWorkbook.Sheets("8_Logon_SOE_by_group")
    Set sh3 = ThisWorkbook.Sheets("Not Logon")

    For Each c1 In sh1.Range("A2:A99")
        For Each c2 In sh2.Range("A3:A99")
            If c1 <> c2 Then
                For Each c3 In sh3.Range("A2:A99")
                    If IsEmpty(Range("c3").Value) = True Then
                        c3 = c1
                    ElseIf IsEmpty(Range("c3").Value) = False Then
                        Exit For
                    End If
                Next c3
            Else
                Exit For
            End If
        Next c2
    Next c1

End Sub

https://i.sstatic.net/2kDEH.png ......It's my output. https://i.sstatic.net/IWSZM.png ......It should be like this.

zedfoxus · Accepted Answer · 2015-06-15 04:55:34Z

Try this out. It has been modified slightly by first removing the contents of Not Logon and then filling one user per row that has not logged in. A counter has been added to increment the next cell to fill if a user has not logged on. A boolean variable has been added to track whether that user has or has not logged on.

Sub NotLog()

    Dim c1 As Range
    Dim c2 As Range
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet

    Set sh1 = ThisWorkbook.Sheets("ALl USer")
    Set sh2 = ThisWorkbook.Sheets("8_Logon_SOE_by_group")
    Set sh3 = ThisWorkbook.Sheets("Not Logon")

    ' empty our not logon sheet
    sh3.Cells.Clear

    ' used to print into sheet 2 line by line a list of
    ' users that have not logged in
    Dim CellCounter As Integer
    Dim TempFound As Boolean
    CellCounter = 1

    For Each c1 In sh1.Range("A2:A99")
        TempFound = False

        ' match user with login
        For Each c2 In sh2.Range("A3:A99")
            If c1.Value = c2.Value Then
                TempFound = True
                Exit For
            End If
        Next c2

        ' if user has not logged in, list the user
        ' in Not Logon sheet
        If Not TempFound Then
            sh3.Cells(CellCounter, 1).Value = c1.Value
            CellCounter = CellCounter + 1
        End If

    Next c1

End Sub

AndrewT · Accepted Answer · 2015-06-15 04:57:05Z

I think I follow what you are doing, is it like the opposite of a vlookup where if its in list a but not list b then put it in list c?

If that is the issues then the problem is that you need

For Each c2 In sh2.Range("A3:A99")
    If c1 <> c2 Then

to complete for all rows before you decide if it is a match, and then write it out to the third sheet.

So if I understood you properly something like this would work:

Sub NotLog()
   Dim c1 As Range
   Dim c2 As Range
   Dim c3 As Range
   Dim sh1 As Worksheet
   Dim sh2 As Worksheet
   Dim isMatch As Boolean

   Set sh1 = ThisWorkbook.Sheets("ALl USer")
   Set sh2 = ThisWorkbook.Sheets("8_Logon_SOE_by_group")
   Set sh3 = ThisWorkbook.Sheets("Not Logon")

   For Each c1 In sh1.Range("A2:A99")
      isMatch = False
      For Each c2 In sh2.Range("A2:A99")
         If c1 = c2 Then
            isMatch = True
            Exit For
         End If
      Next c2
      If Not isMatch Then ' check once you have checked all on second sheet
         'This is quicker than looping to find the bottom blank row
         'It basically says go to bottom row, then ctrl+Up then down one
         sh3.Range("a" & sh3.Rows.Count).End(xlUp).Offset(1, 0) = c1
      End If
   Next c1
End Sub

Good luck

Collectives™ on Stack Overflow

How to insert data by using VBA in excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related