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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Glad it's sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top