Easy VBA but stumped why it won't work as expected

[daithiboy]

Hey Folks,

Someone decided on a terrible way to record attendances which I would like to remedy.
As you can, hopefully, see below, I would like to translate this into a proper spreadsheet format with column A for Name and column E for Date attended.

I have some simple code which should loop through each column and row to achieve this. The code works as expected until it gets to "B5" and instead of if statement seeing the "X" and carrying out the code, it skips over as if it doesn't fit the criteria. This happens at random spots throughout and therefore misses a number of attendances.

I can't figure it out!! Any help would be greatly appreciated.

Sub Transpose()

Dim wb As ThisWorkbook
Dim LastCol As Long
Dim LastRow As Long
Dim NxtRow As Long
Dim i As Integer
Dim j As Integer

LastCol = RawData.Range("A1").CurrentRegion.Columns.Count
LastRow = RawData.Cells(RawData.Rows.Count, "A").End(xlUp).Row
NxtRow = Attendances.Cells(RawData.Rows.Count, "A").End(xlUp).Row + 1

        For i = 1 To LastCol
        
            For j = 2 To LastRow
            
                If RawData.Cells(i, j).Value = "X" Then
                    
                    Attendances.Range("E" & NxtRow).Value = RawData.Cells(1, i)
                    Attendances.Range("A" & NxtRow).Value = RawData.Cells(j, 1)
                    NxtRow = NxtRow + 1
                                                    
                End If
            
            Next j
            
        Next i

End Sub

 

[daithiboy]

Which version of Excel are you using?

I think from Excel 2016 PowerQuery has been made available by standard.

As for the code, have a look at what I added when I edited my previous post.

Turns out I do have PowerQuery in this 2016. Very good to know. I tried doing it the way you suggested but couldn't get it the way I wanted.

Moot point though, as your edit above solved the problem!! It had to be something silly.

Thanks a million and thanks to the other lads who responded, very much appreciated.

Dave

 

[daithiboy]

Which version of Excel are you using?

I think from Excel 2016 PowerQuery has been made available by standard.

As for the code, have a look at what I added when I edited my previous post.

I though I had replied to this but it seems to have been lost.

It turns out I do have PowerQuery as default in this version of Excel. Good to know. I must look at some tutorials. Following your instructions, I couldn't get it exactly right but I must tinker some more/might have gotten a step wrong.

It's a moot point though, as your edited post above has solved the problem. It had to be something silly/embarrassing.

Thanks a million Norie and many thanks to all the lads who took the time to respond.

Dave

 

[jolivanes]

You have it solved but since I was trying different things, you could try this also, or keep it as a souvenir.

Sub Maybe()
Dim a, b
Dim x As Long, i As Long, j As Long
x = 0
a = Cells(1).CurrentRegion.Value
ReDim b(1 To WorksheetFunction.CountA(Cells(1).CurrentRegion.Offset(1, 1)), 1 To 2)
    For i = 2 To UBound(a, 1)
        For j = 2 To UBound(a, 2)
            If a(i, j) <> "" Then
                x = x + 1
                    b(x, 1) = a(i, 1)
                        b(x, 2) = a(1, j)
            End If
        Next j
    Next i
Cells(1, UBound(a, 2) + 2).Resize(UBound(b, 1), 2) = b
End Sub