Previous Topic Next Topic

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

Closed
DaliaPerlai Posts 4 Registration date Tuesday 6 November 2012 Status Member Last seen 6 November 2012 - 6 Nov 2012 à 08:11
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 8 Nov 2012 à 10:44
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.
Related:

4 responses

Réponse 1 / 4
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
6 Nov 2012 à 10:01
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
Réponse 2 / 4
DaliaPerlai Posts 4 Registration date Tuesday 6 November 2012 Status Member Last seen 6 November 2012
6 Nov 2012 à 08:47
Anyone can help for lady?
Réponse 3 / 4
DaliaPerlai Posts 4 Registration date Tuesday 6 November 2012 Status Member Last seen 6 November 2012
6 Nov 2012 à 12:58
Thank you very much, it was very useful for me.
Réponse 4 / 4
DaliaPerlai Posts 4 Registration date Tuesday 6 November 2012 Status Member Last seen 6 November 2012
6 Nov 2012 à 13:02
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 Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
8 Nov 2012 à 09:21
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
DaliaPerlai
8 Nov 2012 à 10:08
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 Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
8 Nov 2012 à 10:44
That is exactly what "Range("A1").Value = Date" does.
Why is it not suitable for you?