Macro - Add row, auto fill text and formulas with relative OFFSS [Closed]

DaliaPerlai 4 Posts Tuesday November 6, 2012Registration date November 6, 2012 Last seen - Nov 6, 2012 at 08:11 AM - Latest reply: TrowaD 2299 Posts Sunday September 12, 2010Registration dateContributorStatus February 15, 2018 Last seen
- Nov 8, 2012 at 10:44 AM
Hello,

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.

And......

Maybe You can help me?

In advance thank you sincerely.
See more 

7 replies

TrowaD 2299 Posts Sunday September 12, 2010Registration dateContributorStatus February 15, 2018 Last seen - Nov 6, 2012 at 10:01 AM
+1
Helpful
Dear Lady,

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?  
DaliaPerlai 4 Posts Tuesday November 6, 2012Registration date November 6, 2012 Last seen - Nov 6, 2012 at 08:47 AM
0
Helpful
Anyone can help for lady?
DaliaPerlai 4 Posts Tuesday November 6, 2012Registration date November 6, 2012 Last seen - Nov 6, 2012 at 12:58 PM
0
Helpful
Thank you very much, it was very useful for me.
DaliaPerlai 4 Posts Tuesday November 6, 2012Registration date November 6, 2012 Last seen - Nov 6, 2012 at 01:02 PM
0
Helpful
3
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...
TrowaD 2299 Posts Sunday September 12, 2010Registration dateContributorStatus February 15, 2018 Last seen - Nov 8, 2012 at 09:21 AM
Hi DaliaPerlai,

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?

In case of the last use this:
Range("A1").Value = Date
Best regards,
Trowa
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
TrowaD 2299 Posts Sunday September 12, 2010Registration dateContributorStatus February 15, 2018 Last seen - Nov 8, 2012 at 10:44 AM
That is exactly what "Range("A1").Value = Date" does.
Why is it not suitable for you?