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

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
72
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.

VBA Code:
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


Capture.JPG
 

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
72
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
72
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,670
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You have it solved but since I was trying different things, you could try this also, or keep it as a souvenir.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,320
Latest member
sixnine0312
Top