VBA Range.Value will not receive integer value

Garrydos

New Member
Joined
Jun 1, 2018
Messages
2
I am working on a code that counts a number and then puts the integer counted into a specified cell. The code works normally in other instances but will not work in this one and I can not figure out why. Here are snips of the code that are relevant

Code:
    Dim RowCount, EndCount As Integer    Dim MatID, PartNum, strLeft As String
    Dim MatRng, MatCell, StCell As Range
    Dim ShtS, ShtD As Worksheet
    Dim MatCount As Variant
    Dim EndDone, EndVS, EndVT, EndC As Boolean
    
    Set ShtS = Sheets(ActiveSheet.Index - 1)
    Set ShtD = Sheets(ActiveSheet.Index)
    
    Set MatRng = ShtS.Range("A2", ShtS.Range("A1").End(xlDown))
    Set StCell = ShtD.Cells(4, 6)
    
    EndCount = 0
    
    Do Until EndDone = True
    
        MatCount = 0
        RowCount = 1
        
        If EndVS = False Then
            
            MatID = "VS"
                
            For Each MatCell In MatRng
            
                ShtS.Cells(RowCount, 1).NumberFormat = "@"
                PartNum = ShtS.Cells(RowCount, 1)
                strLeft = Left(PartNum, 2)
                
                If MatID = Left(PartNum, 2) Then
                
                    MatCount = MatCount + 1
                    
                End If
                
                RowCount = RowCount + 1
                
            Next MatCell
                
            EndVS = True
            
            StCell.Value = MatCount             'Refuses to put integer value MatCount into the cell StCell
            StCell = StCell.Offset(1, 0)        'Does not change the cell held in variable StCell
            EndCount = EndCount + 1
            
        ElseIf EndVT = False Then

The code continues for a total of 3 of these IF statements and ends with an if end count hits 3 then EndDone is True.

The only issue is that StCell.Value = MatCount appears to do nothing and StCell = StCell.Offset(1, 0) does not change the range of the cell either. I Have used MsgBox at the location to see that MatCount is a number ~15 at the point that it should change StCell to MatCount

Thanks in advance
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
You need to use the Set keyword to set a Range object

in vba a cell is called a range

Code:
StCell = StCell.Offset(1, 0)

that code just copies the value over from the cell below... same as...

Code:
StCell.Value = StCell.Offset(1, 0).Value
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board.

This line
Code:
StCell = StCell.Offset(1, 0)
should be
Code:
Set StCell = StCell.Offset(1, 0)
Also only the values in red below have been properly declared all the rest are variant
Code:
Dim RowCount, [COLOR=#ff0000]EndCount [/COLOR]As Integer
Dim MatID, PartNum, [COLOR=#ff0000]strLeft [/COLOR]As String
Dim MatRng, MatCell, [COLOR=#ff0000]StCell [/COLOR]As Range
Dim ShtS, [COLOR=#ff0000]ShtD [/COLOR]As Worksheet
Dim MatCount As Variant
Dim EndDone, EndVS, EndVT, [COLOR=#ff0000]EndC [/COLOR]As Boolean
 
Last edited:

Garrydos

New Member
Joined
Jun 1, 2018
Messages
2
Ah, Thank you very much this actually fixed everything; through the declaring and adding set. Idk why I declared MatCount as a variant, but i fixed it to an integer as well as properly declared everything, changed the other line to set, and now everything works as intended! Thank you again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top