Value not changing of variables, with in procedure & module

[gauravkrgautam]

How could i change value of variable from one line to another, or say it should be calculate every time.
File is attached and the concerned variable is lastrow which is not updating its value even when i have mentioned it in the beginning of module. codi is :

Option Explicit
Dim lastrow As Double
Sub test()
Dim rng As Range
Sheets("week range").Activate
            lastrow = ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row
            MsgBox lastrow
            Range("a" & lastrow).Select
            ActiveCell.Value = ActiveCell.Offset(0, 3)
            ActiveCell.Offset(1, 0).Value = ActiveCell.Offset(0, 4)
            Set rng = ActiveSheet.Range("a1:a" & lastrow)
            MsgBox lastrow

End Sub
' How could i change value of variable from one line to another, or say it should be calculate every time _

Sub test1()
Dim rng As Range
Sheets("sheet2").Activate
            lastrow = ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row
            MsgBox lastrow
            Range("a" & lastrow).Select
            ActiveCell.Value = ActiveCell.Offset(0, 3)
            ActiveCell.Offset(1, 0).Value = ActiveCell.Offset(0, 4)
            Set rng = ActiveSheet.Range("a1:a" & lastrow)
            MsgBox lastrow

End Sub

[\code]

 

[NARAYANK991]

Hi Gaurav ,

In any computer program , execution proceeds one statement at a time ; and any variable which is assigned a value , retains its value till it is assigned a different value.

Thus your statement :

lastrow = ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row

is assigning a particular value to the variable lastrow ; now this variable is supposed to retain this assigned value till it is assigned another value.

You cannot expect the above statement to tell the computer to always calculate the value of the expression on the right hand side ( ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row ) , and assign it to the variable lastrow , so that as and when a new row is added / deleted , the value of lastrow automatically increases / decreases.

This kind of automatic recalculation happens only within the worksheet , because Excel is doing it ; when you code in VBA , you are coding within a more restricted environment , and this kind of automatic recalculation of variables all the time is either not possible or extremely difficult.

This can happen in what is called a multi-threaded environment , where you can calculate lastrow in one thread , while the addition / deletion of rows happens in another thread , and both threads are running concurrently.

Unfortunately , that is either not possible or very difficult ; there is an example of such coding in the following link ; you can see how complex the coding is. And even this is not real multi-threading but only a simulation.

http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html

So the essential point is that if you want the variable lastrow to be updated , you have to assign it a new value by repeating the assignment ; so put the statement :

lastrow = ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row

each time you want lastrow to be recalculated.

Narayan