Macro - Add row, auto fill text and formulas with relative OFFSS [Closed]
DaliaPerlai4
Posts Tuesday November 6, 2012Registration date
November 6, 2012
Last seen
-
Nov 6, 2012 at 08:11 AM - Latest reply: TrowaD2355
Posts Sunday September 12, 2010Registration dateContributorStatus
May 17, 2018
Last seen
- Nov 8, 2012 at 10:44 AM
I have very big problem and I can't find answer for it. Maybe You can halp me.
1. In Sheet "Intro" I have row (5), in wich are formulas:
(i) C5(='Sheet1'!$A$1);
(ii) I5(=OFFSET('Sheet1'!$A$1;17;1;1;));
(iii) In this row also is number of my tables line, i. e B5(=1). This number "1" is also a hyperlink to Sheet1.
2. I have Macro, which every time automaticaly insert new row after last filled row, i. e. when I have filled 5 row and run my Macro, it inserts line 6. Therefore necessary to automatically fill this row with my formulas, that I mentioned above.
More specifically, the new row should be filled in like this:
When new row is 6:
(i) C6(='Sheet2'!$A$1);
(ii) I6(=OFFSET('Sheet2'!$A$1;17;1;1;));
(iii) B6(=2). This number "2" is also a hyperlink to Sheet2.
When new row is 7:
(i) C7(='Sheet3'!$A$1);
(ii) I7(=OFFSET('Sheet3'!$A$1;17;1;1;));
(iii) B7(=3). This number "3" is also a hyperlink to Sheet3.
When new row is 8:
(i) C8(='Sheet4'!$A$1);
(ii) I8(=OFFSET('Sheet4'!$A$1;17;1;1;));
(iii) B8(=4). This number "4" is also a hyperlink to Sheet4.
Select a cell in the newly added row and run the following code:
Sub AddFormula()
Dim x As Integer
x = ActiveCell.Row
Range("B" & x).Value = x - 4
ActiveSheet.Hyperlinks.Add Range("B" & x), "", "Sheet" & x - 4 & "!A1"
Range("C" & x).Formula = "=Sheet" & x - 4 & "!$A$1"
Range("I" & x).Formula = "=OFFSET(Sheet" & x - 4 & "!$A$1,17,1,1)"
End Sub
Best regards,
Trowa
Was this answer helpful?Thanks for your feedback.
Don't forget to take a few seconds to say thanks: Thanks for your feedback.
We're sorry. Our aim is to give you the best possible answer.
But I have one more problem.
I have button, which instert date in the cell A1
I need, that in the cell a1 will be date of the time, when I pushed the button.
Now I use function =TODAY(), but this function is not suitable for me. He show actualy date every day, but not the date of the time, when I pushed the button...
Not clear to me what you mean by "Date of the time".
Do you want to display time only or date only or both?
Or do you mean that you want to keep the date and don't want it to change once it is entered?
It is not suitable for me.
I need the same effect like in function NOW or TODAY. I need fix the date, when row are inserting (date of insert). But in these functions (NOW or TODAY) is one wrong thing that Excel renews date each time I open the document.
Maybe do You have answer? Thank you
