Cell Value to a Variable

[tanayhan]

I am assigning some cell values to some variables in a macro to use later as default values. My problem is how can I get the values assigned to variables to assign them as default values in the cells? I must do this in another macro.

Thanks,
Ayhan

[Dan Aragon]

Your question is a little vague. Can you provide an example of what you are trying to do? I'm assuming that you know how to assign a value to a cell and that isn't the issue.

[tanayhan]

On 2002-03-10 13:49, Dan Aragon wrote:
Your question is a little vague. Can you provide an example of what you are trying to do? I'm assuming that you know how to assign a value to a cell and that isn't the issue.

In a worksheet, in the beginning I calculate a value for Cell B1. And in a macro called GetX() I assign the calculated value of B1 to variable X to use it later, as following.

Sub GetX()
Dim X
X = Range("B1").Value
End Sub

Then if I don't like the value I calculated in B1, I change the value in B1 manually. The value in the variable in X stays as original value to get later if I need it.

My problem is how to get the X value again if I need it from another macro, and assign it to cel B1.

I hope you understand what I mean.

Thanks,

Ayhan

[Dave Hawley]

Hi

In the sheet that house the value of B1 put this in the Private module of the Worksheet Object(right click on name tab and selec "View Code")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then iVal = Target
End Sub

Then at the top a standard module put:

Public iVal as Integer

[tanayhan]

On 2002-03-11 03:54, Dave Hawley wrote:
Hi

In the sheet that house the value of B1 put this in the Private module of the Worksheet Object(right click on name tab and selec "View Code")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then iVal = Target
End Sub

Then at the top a standard module put:

Public iVal as Integer

Thanks Dave,

It worked, but there is something wrong. Everytime I enter a new value to B1, iVal takes the new value. I want it to take the value at the beginning. For example, in the beginnig I entered 10 for B1. It wil be stored as 10. After that, I can enter several different values for B1. When I want to retreive the first value (that was 10) for B1 the macro should retreive the first value (10). I hope I could explain it.

Thanks,
Ayhan

[Dave Hawley]

Hi

Depends what you mean by "the beggining"? But try this

Private Sub Worksheet_Change(ByVal Target As Range)
iFirstTime = 1 + iFirstTime
If iFirstTime <> 1 Then exit sub
If Target.Address = "$B$1" Then iVal = Target

End Sub

Then at the top a standard module put:

Public iVal as Integer
Public iFirstTime As Long

[tanayhan]

On 2002-03-11 08:44, Dave Hawley wrote:
Hi

Depends what you mean by "the beggining"? But try this

Private Sub Worksheet_Change(ByVal Target As Range)
iFirstTime = 1 + iFirstTime
If iFirstTime <> 1 Then exit sub
If Target.Address = "$B$1" Then iVal = Target

End Sub

Then at the top a standard module put:

Public iVal as Integer
Public iFirstTime As Long

Thanks Dave,

It worked. You asked me what I mean by "the beginning". I mean the first value I entered.
Now I have a new problem with this macro. How can I do this for more than one variables. Say for three variables.

Kind regards,
Ayhan

[Dave Hawley]

Tr this, just alter the cell addresses to suit.

At the top of a standard module put

Public iVal1 As Integer
Public iVal2 As Integer
Public Ival3 As Integer
Public iFirstTime1 As Long
Public iFirstTime2 As Long
Public iFirstTime3 As Long




The in the Worksheet module put

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target) Then Exit Sub

Select Case Target.Address
Case "$B$1"
iFirstTime1 = 1 + iFirstTime1
If iFirstTime1 <> 1 Then Exit Sub
iVal1 = Target

Case "$B$5"
iFirstTime2 = 1 + iFirstTime2
If iFirstTime2 <> 1 Then Exit Sub
iVal2 = Target

Case "$B$5"
iFirstTime3 = 1 + iFirstTime3
If iFirstTime3 <> 1 Then Exit Sub
Ival3 = Target
End Select

End Sub

[tanayhan]

On 2002-03-13 03:32, Dave Hawley wrote:
Tr this, just alter the cell addresses to suit.

At the top of a standard module put

Public iVal1 As Integer
Public iVal2 As Integer
Public Ival3 As Integer
Public iFirstTime1 As Long
Public iFirstTime2 As Long
Public iFirstTime3 As Long




The in the Worksheet module put

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target) Then Exit Sub

Select Case Target.Address
Case "$B$1"
iFirstTime1 = 1 + iFirstTime1
If iFirstTime1 <> 1 Then Exit Sub
iVal1 = Target

Case "$B$5"
iFirstTime2 = 1 + iFirstTime2
If iFirstTime2 <> 1 Then Exit Sub
iVal2 = Target

Case "$B$5"
iFirstTime3 = 1 + iFirstTime3
If iFirstTime3 <> 1 Then Exit Sub
Ival3 = Target
End Select

End Sub

Thanks Dave,

It worked as the way I want. Sorry for being too late to confirm.

Kind regards,

Ayhan