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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,515
Office Version
  1. 2013
Platform
  1. Windows
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

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,515
Office Version
  1. 2013
Platform
  1. Windows
In that case check for leading /
trailling spaces in any cell !
 

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
72

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,092
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

As an alternative you could try ...

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

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,673
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If you would use
Code:
If Len(RawData.Cells(i, j).Value <> 0 Then
Would that work?
Assuming that the empty cells are indeed empty
 

daithiboy

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,092
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,223
Messages
5,576,827
Members
412,748
Latest member
MikeyP14
Top