Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Setting an ActiveCell equal to another cell

  1. #1
    Join Date
    23rd August 2005
    Posts
    11

    Setting an ActiveCell equal to another cell

    As the title suggests, I am trying to set the ActiveCell equal to another cell in another sheet:

    ActiveCell.FormulaR1C1 = "='coefficients line 1'!RC"

    is what I have right now. This was the easy part of what I wish to do. Moving cells from one sheet to another with a similar cell is easy, but how do you do this in a Do While loop as the other cells change in my second sheet?

    What I mean is info is going into the activecell A11 this time, but coming from F1 on my second sheet. After the loop goes again, it is going into A21 from K1, etc. I know how to change the location of the ActiveCell, but do not quite understand how to change what that cell equals when it is not from the same location ever time (not Sheet 1 A1 equals Sheet 2 A1 everytime, but Sheet 1 A11 equals Sheet 2 F1 and Sheet 1 A21 equals Sheet 2 K1 and Sheet 1 A31 equals Sheet 2 P1, etc.)

    So basically what I am asking is does anybody know if there is a way to have my ActiveCell.FormulaR1C1 = to a changing location from another sheet?

    Please let me know if this does not make any sense and I will try to help clarify more. Thanks a lot in advance!!!

    Mark

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,786

    Re: Setting an ActiveCell equal to another cell

    MAS1683,

    Need more .... info!!!!

    You appear to specify two, and indicate that in the loop there will be more, "activecells" and differeing "cells" where data for those cells is to come from.


    1. Use of the Activecell, for that fact active anything just slows things down.
    2. You all ready appear to know that VBA / Macro will be needed.
    3. What I need is
    .....A sample workbook
    .....A clearer explanation as to what the formulat should be based on which cell the formula is going into.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?

  3. #3
    Join Date
    23rd August 2005
    Posts
    11

    Re: Setting an ActiveCell equal to another cell

    Ok, I have information in two different sheets that I am trying to use references from and put them into one new sheet. Here is the file format for the sheet called "coefficients line 1"

    VB:
    Name (A1) 
    Range (A2) 
    134-214 (A3) 
    214-294 (A4) 
    294-374 (A5) 
    374-454 (A6) 
    454-534 (A7) 
    
    
    Here is the format for the other sheet I am using called "IHR"

    VB:
    134 (A5) 
    214 (A6) 
    294 (A7) 
    374 (A8) 
    454 (A9) 
    534 (A10) 
    
    
    And here is the code VBA code I'm using.

    VB:
     
    Do While ActiveCell <> "" 
         
        nBP = 0 ' Dumby counter
         
        Do While ActiveCell <> "" 'Checks to see if cell is blank
             
            Worksheets("Break Points").Activate 'program ready to import data
            Sheets("Break Points").Range("A1").Select 'program looking at top left of input sheet
             
            ActiveCell.Offset(y, 0).Select 
             
             
            ActiveCell.FormulaR1C1 = "='coefficients line 1'!RC" 
            ActiveCell.Offset(1, 0).Select 
            ActiveCell.FormulaR1C1 = "='coefficients line 1'!RC" 
            ActiveCell.Offset(1, 0).Select 
            ActiveCell.FormulaR1C1 = "='coefficients line 1'!RC" 
            ActiveCell.Offset(0, 1).Select 
            ActiveCell.FormulaR1C1 = "=IHR!R[2]C[-1]" 
            ActiveCell.Offset(0, 1).Select 
            ActiveCell.FormulaR1C1 = "=IHR!R[3]C[-2]" 
            ActiveCell.Offset(0, 1).Select 
            ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]" 
            ActiveCell.Offset(0, 1).Select 
            ActiveCell.FormulaR1C1 = "=((('coefficients line 1'!RC[-3]*((RC[-2])^2))+('coefficients line 1'!RC[-2]*RC[-2])+('coefficients line 1'!RC[-1]))-(('coefficients line 1'!RC[-3]*((RC[-3])^2))+('coefficients line 1'!RC[-2]*RC[-3])+('coefficients line 1'!RC[-1])))/(RC[-2]-RC[-3])" 
             
             
            Worksheets("coefficients line 1").Activate 
            Sheets("coefficients line 1").Range("A3").Select 
             
            nBP = nBP + 1 ' Increment dumby counter
            y = y + 1 ' Increment vertical counter
             
            ActiveCell.Offset(y, xCL).Select ' Allows to check to see if is empty, breaking the loop if necessary
             
        Loop 
         
        If nBP = 5 Then ' If statement to allow for proper vertical spacing (y)
            y = y + 5 ' Forces y to have proper spacing
        ElseIf nBP = 7 Then 
            y = y + 3 ' Forces y to have proper spacing
        End If 
         
    Loop 
    
    
    Basically, the code starts by pulling information from 'coefficients line 1'!A1 and puts the information into the new sheet called "Break Points" into cell A1. Everything works the way that it should the first time through the loop. It is once I try to pull information the next time that problems occur. The next time the information that is desired from the sheet 'coefficients line 1' starts in cell F1 and is as follows:

    VB:
    Name 2 (F1) 
    Range (F2) 
    134-214 (F3) 
    214-294 (F4) 
    294-374 (F5) 
    374-454 (F6) 
    454-534 (F7) 
    
    
    It also should pull information from the other sheet IHR as well:

    VB:
    134 (D5) 
    214 (D6) 
    294 (D7) 
    374 (D8) 
    454 (D9) 
    534 (D10) 
    
    
    This should put the new information in starting at cell A11 in "Break Points"... Information is put into cell A11 in Break Points, but not the information that I want, it just takes cell A11 from "coefficients line 1" instead of F1 from that sheet.

    So, to make a long story short... I am looking for a way to put the information from F1 in "coefficients line 1" and paste it into "break points" cell A11, IHR cell D5 and paste it into "Break Points" cell B13; Information from K1 in "coefficients line 1" and paste it into "Break Points" A21, IHR cell G5 and paste it into "Break Points" B23 and so on, all while progressing through my Do While loop.

    Hopefully this helps clarify, but if not feel free to ask for my clarification.

    Thanks a lot in advance,

    Mark

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,786

    Re: Setting an ActiveCell equal to another cell

    MAS1683,

    Ok I'm thoroghly lost. Can you attach a sample workbook?
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?

  5. #5
    Join Date
    26th July 2004
    Posts
    1,293

    Re: Setting an ActiveCell equal to another cell

    Hi MAS


    while I havent understood your post, this code will do what your title suggests...It will set the Activecell to the value of another cell

    VB:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        If Not Intersect(Target, ActiveCell) Is Nothing Then 
            ActiveCell.Value = Sheets("sheet2").Range("B26") 
        End If 
    End Sub 
    
    
    HTH

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Setting a cell equal to another cell but as an equation
    By the_crooked_toe in forum EXCEL HELP
    Replies: 4
    Last Post: July 2nd, 2010, 03:47
  2. Replies: 2
    Last Post: March 26th, 2007, 23:04
  3. Setting A Cell Value Equal To Another Code
    By jeffmoseler in forum EXCEL HELP
    Replies: 6
    Last Post: November 22nd, 2006, 11:49
  4. Setting a cell value equal to a data series point value
    By consigliere in forum EXCEL HELP
    Replies: 1
    Last Post: May 15th, 2006, 17:09
  5. VBA: Setting one array equal to another
    By Bazman in forum EXCEL HELP
    Replies: 2
    Last Post: November 19th, 2003, 22:02

Bookmarks

Posting Permissions

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