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

 

[Michael M]

Probably because some of your "x" are in lower case and you specified an Upper case "X"
Before the start of the Sub put this line
Option Compare Text

 

[daithiboy]

Probably because some of your "x" are in lower case and you specified an Upper case "X"
Before the start of the Sub put this line
Option Compare Text

Thanks for getting back Michael. I had though of this and changed the case on some of them to see if it made a difference but it didn't. In fact, originally the X's were 1's but I changed them to X to see if it made a difference!

I can't figure it!

 

[Michael M]

In that case check for leading /
trailling spaces in any cell !

 

[daithiboy]

In that case check for leading /
trailling spaces in any cell !

Nada, all seem to only contain the X and nothing else. Does the code look ok to you at a glance?

 

[Norie]

You could reformat the data using PowerQuery with a few simple steps.

1 Select the data on RawData.

2 Goto Data>Get & Transform Data>From Table/Range.

3 Right click the Date column and select Unpivot columns.

4 Filter to remove blanks from the second column in the new table.

5 Close & Load to return the data to Excel.

P.S. In your code you have i and j the wrong way round in the If statement

 

[GWteB]

As an alternative you could try ...

If InStr(rawdata.Cells(i, j).Text, "x", vbTextCompare) <> 0 Then

 

[jolivanes]

If you would use

If Len(RawData.Cells(i, j).Value <> 0 Then

Would that work?
Assuming that the empty cells are indeed empty

 

[daithiboy]

You could reformat the data using PowerQuery with a few simple steps.

1 Select the data on RawData.

2 Goto Data>Get & Transform Data>From Table/Range.

3 Right click the Date column and select Unpivot columns.

4 Filter to remove blanks from the second column in the new table.

5 Close & Load to return the data to Excel.

Thanks for the suggestion, but unfortunately I'm using a work laptop which doesn't allow me install extra utilites without getting IT involved.

Does the code in general seem ok to you Norie. Should it work??

 

[Norie]

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.