Change value of Cell to the integer in VBA

[cmoore3984]

This is easy, but I just cannot get there.

I am using Excel 2007.

I want cell O1 to equal the new test value at end of this macro. To begin Cell O1 = 0. After the macro runs the first time it should equal 1, runs a second time = 2, etc. It does not change with what I have below. Please advise.:banghead:



Dim Test As Integer
Test = Range("O1")
If Test = 0 Then

ActiveWindow.SmallScroll Down:=24
Range("B43:B46").Select
Selection.Copy
Range("C43").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B10:B23").Select
Selection.ClearContents
Range("B9").Select
End If
If Test = 1 Then

ActiveWindow.SmallScroll Down:=24
Range("B43:B46").Select
Selection.Copy
Range("D43").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B10:B23").Select
Selection.ClearContents
Range("B9").Select
End If
If Test = 2 Then

ActiveWindow.SmallScroll Down:=24
Range("B43:B46").Select
Selection.Copy
Range("E43").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B10:B23").Select
Selection.ClearContents
Range("B9").Select
End If
If Test > 2 Then

MsgBox ("Please start a new document, this one is full")

End If
Test = Test + 1
Range("O1").Value = Test


End Sub

[omnibuster]

Try.
Sub Testing1()
Dim Test As Integer
Test = Range("O1").Value

Select Case Test

Case Is < 3
Range("O1").Value = Range("O1").Value + 1

Case Else
MsgBox ("Please start a new document, this one is full")
Range("O1").Value = 0
Exit Sub

End Select

'ActiveWindow.SmallScroll Down:=24 '??
Range("B43:B46").Copy
Range("B43").Offset(, Test + 1).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B10:B23").ClearContents
Range("B9").Activate
End Sub

[cmoore3984]

This would work, but all i really want to know is how to make cell O1 read what the new test number is.

They need to see how many times the macro has ran which is tracked in O1

[mbarron]

If I run your macro on a clean worksheet, O1 increments with each running of the macro.