Announcement

Collapse
No announcement yet.

Setting an ActiveCell equal to another cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      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"

      Code:
      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"

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

      Code:
      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:

      Code:
      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:

      Code:
      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

      Comment


      • #4
        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?
        3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

        Comment


        • #5


          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

          Code:
          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

          Comment

          Working...
          X