PDA

View Full Version : Static Variable



BENatUSGS
11-10-2010, 09:03 AM
Hey Everyone,
Lets start with my code.
Private Sub CheckButton_Click()
Static RowNum As Long
If Worksheets("Sample").Cells(2, 1).Value = "" Then RowNum = 2 Else RowNum = RowNum + 1

Worksheets("Result").Cells(RowNum, 3).Value = rval
Worksheets("Result").Cells(RowNum, 1).Value = rsint
Worksheets("Result").Cells(RowNum, 2).Value = Para
Worksheets("Sample").Cells(RowNum, 1).Value = ssint
Worksheets("Sample").Cells(RowNum, 4).Value = sn
Worksheets("Sample").Cells(RowNum, 5).Value = Msg

End Sub


I am submitting multiple data inputs into a spreadsheet all at one time. I am trying to make it so my new values go into the next row automatically and without the user having to change anything. So I have a command button (CheckButton_Click) that will change my variable “RowNum” based on how the spread sheet is formatted(In other words, is the spreadsheet new or is the spreadsheet just being added on to). So, I have the variable “RowNum” stored so the valued is remembered if I restart the code.
The Reason I cannot use the code...
Private Sub CheckButton_Click()
Static RowNum As Long

RowNum = RowNum + 1

Worksheets("Result").Cells(RowNum, 3).Value = rval
Worksheets("Result").Cells(RowNum, 1).Value = rsint
Worksheets("Result").Cells(RowNum, 2).Value = Para
Worksheets("Sample").Cells(RowNum, 1).Value = ssint
Worksheets("Sample").Cells(RowNum, 4).Value = sn
Worksheets("Sample").Cells(RowNum, 5).Value = Msg

End Sub


...is because I need my RowNum to not start with a value of 0. The reason being is that my spreadsheet has labels in row 1 that for obvious reasons cannot be deleted.

So, is there a way to change this code so that the starting row is row 2 and the rows increment by 1 correctly? Also, is there a way that the value of RowNum is saved even when the spread sheet and code are closed and reopened?

Thanks in advance,

Ben

BENatUSGS
11-10-2010, 09:31 AM
Hey Everyone,

I may have solved it myself.
Private Sub CheckButton_Click()
Static RowNum As Long

RowNum = Worksheets("Result").Range("A65536").End(xlUp).Row


If Worksheets("Sample").Cells(2, 1).Value = "" Then RowNum = 2 Else RowNum = RowNum + 1



Worksheets("Result").Cells(RowNum, 3).Value = rval
Worksheets("Result").Cells(RowNum, 1).Value = rsint
Worksheets("Result").Cells(RowNum, 2).Value = Para
Worksheets("Sample").Cells(RowNum, 1).Value = ssint
Worksheets("Sample").Cells(RowNum, 4).Value = sn
Worksheets("Sample").Cells(RowNum, 5).Value = Msg


End Sub

The last row used code was the ticket!

Thanks All
Ben

p45cal
11-10-2010, 10:00 AM
Can I suggest you
1. take out the If line altogether
2. change Static to Dim
3. change:
RowNum = Worksheets("Result").Range("A65536").End(xlUp).Row
to:
RowNum = Worksheets("Result").Range("A65536").End(xlUp).offset(1).Row


Since you're adding data to column A in the macro (I hope it's never an empty variable)..

oh, I've just noticed something; you're using the same RowNum value to place data on a particular row on two different sheets - is that right?

BENatUSGS
11-12-2010, 08:20 AM
Thanks for the feedback
Yes, that is correct. I am using RowNum for two different sheets in the work book.
Ben

BENatUSGS
11-12-2010, 08:29 AM
Can I suggest you
1. take out the If line altogether
2. change Static to Dim
3. change:
RowNum = Worksheets("Result").Range("A65536").End(xlUp).Row
to:
RowNum = Worksheets("Result").Range("A65536").End(xlUp).offset(1).Row


Since you're adding data to column A in the macro (I hope it's never an empty variable)..

oh, I've just noticed something; you're using the same RowNum value to place data on a particular row on two different sheets - is that right?
This worked much better thank you.