Simple Sub finding blanks not writing to cell

[dellehurley]

Hi,
Can someone explain to me why the first Sub finds the answer but does not write to the cell but the second (which is almost identical) does?

Sub mrexcel()
'check for BLANKcell cells in A:G and K:L
Dim DbWs As Worksheet
Dim FileName As String
Dim DbLastRow As Long
Dim i, j, CountBlanks, CountRecs As Long
Dim FindName As Range
Dim BLANKcell As Variant

Set DbWs = ThisWorkbook.Sheets("Database")
DbLastRow = DbWs.Cells(Rows.Count, "A").End(xlUp).Row

    CountBlanks = DbWs.Range("A:G,J:L").SpecialCells(xlCellTypeBlanks).Cells.Count
    MsgBox CountBlanks & " Errors Have Been Detected.", vbOKCancel + vbExclamation, "Database Trouble Shooting"

For i = 2 To DbLastRow
FileName = DbWs.Cells(i, 1)
    
    For j = 11 To 11 'missing Rec No in Col K
    BLANKcell = DbWs.Cells(i, j).Value
        If BLANKcell = "" Then     'countif and write to cell
            CountRecs = Application.WorksheetFunction.CountIf(DbWs.Range("A:A"), FileName)
            BLANKcell = CountRecs
            Debug.Print BLANKcell
        End If
    Next j
Next i
End Sub

Sub mrexcel2()
'check for BLANKcell cells in A:G and K:L
Dim DbWs As Worksheet
Dim FileName As String
Dim DbLastRow As Long
Dim i, j, CountBlanks, CountRecs As Long
Dim FindName As Range
Dim BLANKcell As Variant

Set DbWs = ThisWorkbook.Sheets("Database")
DbLastRow = DbWs.Cells(Rows.Count, "A").End(xlUp).Row

    CountBlanks = DbWs.Range("A:G,J:L").SpecialCells(xlCellTypeBlanks).Cells.Count
    MsgBox CountBlanks & " Errors Have Been Detected.", vbOKCancel + vbExclamation, "Database Trouble Shooting"

For i = 2 To DbLastRow
FileName = DbWs.Cells(i, 1)
    
    For j = 11 To 11 'missing Rec No in Col K
    BLANKcell = DbWs.Cells(i, j).Value
        If BLANKcell = "" Then     'countif and write to cell
            CountRecs = Application.WorksheetFunction.CountIf(DbWs.Range("A:A"), FileName)
            DbWs.Cells(i, j).Value = CountRecs
            Debug.Print DbWs.Cells(i, j).Value
        End If
    Next j
Next i
End Sub

 

[6StringJazzer]

The first one does not write to any cell. It save the result of the COUNTIF in the local variable BLANKcell. What were you expecting it to do? They are almost identical, but the thing that makes them different is that the first one does not write to a cell.

First writes to a variable

            BLANKcell = CountRecs

Second writes to a cell

            DbWs.Cells(i, j).Value = CountRecs

 

[dellehurley]

The first one does not write to any cell. It save the result of the COUNTIF in the local variable BLANKcell. What were you expecting it to do?

Hi Jeff,
I thought that because I had set the variable BLANKcell = DbWs.Cells(i, j).Value it would write to it with this line BLANKcell = CountRecs.
I thought BLANKcell = CountRecs & DbWs.Cells(i, j).Value = CountRecs were essentially the same. Why aren't they. I thought because BLANKcell is equal to DbWs.Cells(i, j).Value I became interchangeable.
Dannielle

 

[Alex Blakenburg]

For them to be interchangeable you would use:

Set BLANKcell = DbWs.Cells(i, j)

If you did this it would also then be better to recognise that this is the case by using

Dim BLANKcell as Range

The Set command causes the variable to be just a link / pointer to the Object.
Without it you are just putting a value into your variable, which is independent of the object

 

[6StringJazzer]

I was going to write what Alex just wrote.

Since maybe you do not have much background in this type of programming, I'll give a little tutorial. There are three types of variables in VBA:

Scalar: Variables that can hold a single value, like Long, Real, String
Arrays: Variables that can hold multiple values
Objects: Instances of a class, which you can think of as a pointer to some chunk of data. That chunk can take many forms.

BLANKCell is declared as Variant. I generally discourage the use of Variant since it is what we call a weak type--it can transmogrify into any of the three types of variables. When you have a statement like

 

[6StringJazzer]

I was going to write what Alex just wrote.

Since maybe you do not have much background in this type of programming, I'll give a little tutorial. There are three categories of data elements (and therefore variables) in VBA:

Scalar: Variables that can hold a single value, like Long, Real, String
Arrays: Variables that can hold multiple values
Objects: Instances of a class, which you can think of as a pointer to some chunk of data. That chunk can take many forms.

BLANKcell is declared as Variant. It can transmogrify into any of the three categories of variables, and any data type. When you have a statement like

            BLANKcell = CountRecs

VBA evaluates the right side of the equals sign, and says, "Oh, we have a Long value here, so I am going to use the Variant BLANKcell to hold a Long value and copy that value." Now you have two variables that each have their own copy of the data. Changing one does not affect the other.

When you have a statement like

    BLANKcell = DbWs.Cells(i, j).Value

VBA evaluates the right side of the equals sign, and says, "Oh, we have some kind of value in this cell, so I am going to use BLANKcell to hold the same data type as whatever is in the cell and copy it." (If you do not know in advance what the data type of the cell is, that is the one time that I would use a Variant.) Now BLANKcell has its own copy of whatever data is in the cell. If you change the value in the cell, BLANKcell will not change.

When you have a statement like what Alex suggests:

Dim BLANKcell As Range
Set BLANKcell = DbWs.Cells(i, j)

You are saying that BLANKcell is a Range object. DbWs.Cells(i, j) is also a Range object. The Set statement tells VBA "I don't want to copy the value into my variable, I want my variable to point to the same object that DbWs.Cells(i, j) points to." After you execute that Set statement, any time the cell changes value, both DbWs.Cells(i, j) and BLANKcell both reflect the change in that value. A change to either one will also change the other because now they both point to the same thing.

 

[6StringJazzer]

Oh, one other thing. In a Dim statement you must specify the data type for each variable. If you do not, it will be assumed to be Variant.

Dim i, j, CountBlanks, CountRecs As Long

This does not declare four Long variables. The first three will be Variant, and only CountRecs will be Long.

 

[dellehurley]

For them to be interchangeable you would use:

Set BLANKcell = DbWs.Cells(i, j)

If you did this it would also then be better to recognise that this is the case by using

Dim BLANKcell as Range

The Set command causes the variable to be just a link / pointer to the Object.
Without it you are just putting a value into your variable, which is independent of the object

Thanks for the lesson. You would not believe (well maybe you would considering this post) how much time I have spent working on object errors.
Dannielle

 

[dellehurley]

I was going to write what Alex just wrote.

Since maybe you do not have much background in this type of programming, I'll give a little tutorial. There are three categories of data elements (and therefore variables) in VBA:

Scalar: Variables that can hold a single value, like Long, Real, String
Arrays: Variables that can hold multiple values
Objects: Instances of a class, which you can think of as a pointer to some chunk of data. That chunk can take many forms.

BLANKcell is declared as Variant. It can transmogrify into any of the three categories of variables, and any data type. When you have a statement like

            BLANKcell = CountRecs

VBA evaluates the right side of the equals sign, and says, "Oh, we have a Long value here, so I am going to use the Variant BLANKcell to hold a Long value and copy that value." Now you have two variables that each have their own copy of the data. Changing one does not affect the other.

When you have a statement like

    BLANKcell = DbWs.Cells(i, j).Value

VBA evaluates the right side of the equals sign, and says, "Oh, we have some kind of value in this cell, so I am going to use BLANKcell to hold the same data type as whatever is in the cell and copy it." (If you do not know in advance what the data type of the cell is, that is the one time that I would use a Variant.) Now BLANKcell has its own copy of whatever data is in the cell. If you change the value in the cell, BLANKcell will not change.

When you have a statement like what Alex suggests:

Dim BLANKcell As Range
Set BLANKcell = DbWs.Cells(i, j)

You are saying that BLANKcell is a Range object. DbWs.Cells(i, j) is also a Range object. The Set statement tells VBA "I don't want to copy the value into my variable, I want my variable to point to the same object that DbWs.Cells(i, j) points to." After you execute that Set statement, any time the cell changes value, both DbWs.Cells(i, j) and BLANKcell both reflect the change in that value. A change to either one will also change the other because now they both point to the same thing.

Brilliant. This is the issue with how I have learnt VBA (by watching Youtube, Googling and Mr Excel posts) I am missing some of the fundamental. While I have come a very long way there is much to learn.

...as for the "Dim i, j, CountBlanks, CountRecs As Long" OMG the lesson of the day. I picked this up form somewhere...I thought I was simply condensed the space. I have option explicit at the top of each module I thought that would have picked them up as being undeclared. I suppose excel sees them as declared but not names.
Thanks for your time and help.
Dannielle

 

[Alex Blakenburg]

Thanks for the feedback. Glad you found the information we provided useful.

In terms of:-

I am missing some of the fundamental. While I have come a very long way there is much to learn.

I quite like Paul Kelly's Excel Macro Mastery Youtube videos, if that is of any help.