Cell Value to a Variable
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Cell Value to a Variable

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Minnesota
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com