Cell Value to a Variable

tanayhan

New Member
Joined
Feb 20, 2002
Messages
36
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top