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
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
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