Add formulas to macro

[pwill]

Hi can anyone help with adding formula's to a macro?

I copy data with a Userform from columns ("A2:A" & ", D2:K) to columns ("AI2:AQ)

Column A2:A has Dates
Column D2:D has count values from 1 -12000
Columns E2:K has Data

I am try to find a way of adding formula's to columns ("AI2:AQ) after data has been copied.

ie
If I have copied data from ("A11" & ", D11:K11") to columns ("AI2:AQ2)
Column "AJ2" value would be 10
Then add formula's -
Column "AI2" = "A11"
Column "AJ2" = "D11"
Colums "AK2:AQ2" = "E11:K11"

And then with the data in cells "AK2:AQ2" a formula to arrange the data small to large.
So if the data copied from cells "E2:K2" ie is (5,3,6,7,1,2,4) the formula arranges the data in cells "AI2:AQ2" from (5,3,6,7,1,2,4) to (1,2,3,4,5,6,7)

Any help would be appreciated

Regards
pwill


Edit: Sorry I should have said Sheet1
The code I am using was kindly provided by Roderick_E in another thread 'User Form to Copy Rows

 

[pwill]

Re: Add formula's to macro

Just thought I'd say, the code Roderick_E provided for the Userform works fine and is used to quickly add a range of data. I have added +1 to the startrow and -1 to the endrow in the code to adjust the rows.

The reason for adding formula's at the end of the code (if possible? ) is to be able to auto fill down a few rows if need be? rather than have to keep opening the Userform, just to add a few more rows.

Regards

pwill

 

[pwill]

Re: Add formula's to macro

Hi can anyone help with this? I was using my phone when I post this thread and was limited to text.

Hear is an example of Sheet1 with the data before copying Rows with the UserForm.​

Columns "A2:A" & ", D2:K" have the raw Data
Column "AI2" has a formula (=A2)
Column "AJ2" has a formula (=D2)
Columns "AK2:AQ2" have an array formula '=SMALL(E2:K2,{1,2,3,4,5,6,7})'

And then drag down to autofill the rows "AI2:AQ22"

My problem is when I copy rows with the UserForm the formulas get wiped out in columns "AI:AQ" and I need to put them back with the correct row reference as the data range copied with the UserForm could be anything from 1 to 12000.

	A	B	C	D	E	F	G	H	I	J	K	L	M					AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	Date/Year			Cnt			Results													Date/Year	Cnt		Ascending
2	01/04/18			1	10	3	40	42	9	48	12									01/04/18	1	3	9	10	12	40	42	48
3	01/04/18			2	30	12	6	17	12	28	37									01/04/18	2	6	12	12	17	28	30	37
4	02/04/18			3	25	30	4	34	48	3	12									02/04/18	3	3	4	12	25	30	34	48
5	02/04/18			4	15	42	11	23	62	19	20									02/04/18	4	11	15	19	20	23	42	62
6	03/04/18			5	5	56	8	19	82	4	20									03/04/18	5	4	5	8	19	20	56	82
7	03/04/18			6	4	8	20	26	42	47	13									03/04/18	6	4.42	8	13	20	26	42	47
8	04/04/18			7	15	14	15	24	34	47	3									04/04/18	7	3	14	15	15	24	34	47
9	04/04/18			8	25	26	31	38	39	47	46									04/04/18	8	25	26	31	38	39	46	47
10	05/04/18			9	32	34	33	41	35	47	54									05/04/18	9	32	33	34	35	41	47	54
11	05/04/18			10	43	43	39	47	34	47	70									05/04/18	10	33.8	38.5	43	43	47	47	70
12	06/04/18			11	54	52	44	53	32	47	87									06/04/18	11	32.3	44	47	52	53	54	87
13	06/04/18			12	65	61	50	59	31	47	4									06/04/18	12	4	30.8	47	50	59	61	65
14	07/04/18			13	76	70	55	65	29	47	8									07/04/18	13	8	29.3	47	55	65	70	76
15	07/04/18			14	87	79	61	71	28	47	6									07/04/18	14	6	27.8	47	61	71	79	87
16	08/04/18			15	10	22	24	32	47	49	9									08/04/18	15	9	10	22	24	32	47	49
17	08/04/18			16	2	3	13	23	34	48	4									08/04/18	16	2	3	4	13	23	34	48
18	09/04/18			17	5	7	78	6	42	49	4									09/04/18	17	4.33	5	6.333	7	42	49	78
19	09/04/18			18	56	79	39	10	46	50	3									09/04/18	18	3.33	10	39	46	50	56	79
20	10/04/18			19	54	72	66	27	52	50	10									10/04/18	19	10	27	50.33	52	54	66	72
21	10/04/18			20	68	89	77	44	58	51	3									10/04/18	20	2.89	44	51.08	58	68	77	89
22	11/04/18			21	0	0	0	0	0	0	0									11/04/18	21	0	0	0	0	0	0	0
23
24
25

<tbody>
</tbody>

Here is the code for the UserForm

Private Sub cmdbOk_Click()
Call CopyRows
End Sub
Private Sub UserForm_Initialize()
    Me.Height = 174.75
End Sub
Sub CopyRows()
    Dim Sht1 As Worksheet: Set Sht1 = Worksheets("Sheet1")
    Dim startrow As Long
    Dim endrow As Long
    Dim lRowAI As Long
    Dim cnt As Long
    Dim x As Long
    
        lRowAI = Sht1.Cells(Rows.Count, "AI").End(xlUp).Row
        
            With Sht1
                .Range("AI2:AQ" & lRowAI).ClearContents
            End With
        startrow = Int(UserForm1.TextBox1.Value) + 1
        endrow = Int(UserForm1.TextBox2.Value) - 1
    
            If startrow < 1 Or endrow < 1 Then
                MsgBox "Invalid start or end rows", vbCritical, "ALERT"
                Exit Sub
            End If
            
        'indicate first row where data should copy (could use lastrow+1 of destination column if you want to keep appending)
        cnt = 2
        endrow = startrow + endrow
    
        For x = startrow To endrow
            Cells(cnt, "AI") = Cells(x, "a")
            Cells(cnt, "AJ") = Cells(x, "d")
            Cells(cnt, "AK") = Cells(x, "e")
            Cells(cnt, "AL") = Cells(x, "f")
            Cells(cnt, "AM") = Cells(x, "g")
            Cells(cnt, "AN") = Cells(x, "h")
            Cells(cnt, "AO") = Cells(x, "i")
            Cells(cnt, "AP") = Cells(x, "j")
            Cells(cnt, "AQ") = Cells(x, "k")
            
            cnt = cnt + 1
        Next x
        
End Sub

Any help would be appreciated

regards

pwill

 

[pwill]

Re: Add formula's to macro

I have managed to find myself a solution to my problem by keeping "AI2:AQ2" Row in play with the formulas.

I have the UserForm copy the Rows to start at "AI3:AQ3".
Allowing to AutoFill back Down with the formulas from in "AI2:AQ2" when finished with the Data added from the UserForm.
I have also added code to sort the rows in ascending order left to right in columns "AK:AQ" for the Rows added with the UserForm.

New Code for the UserForm as -

Private Sub cmdbOk_Click()
Call CopyRows
End Sub
Private Sub UserForm_Initialize()
    Me.Height = 174.75
End Sub
Sub CopyRows()
    Dim Sht1 As Worksheet: Set Sht1 = Worksheets("Sheet1")
    Dim startrow As Long
    Dim endrow As Long
    Dim lRowAI As Long
    Dim cnt As Long
    Dim x As Long
    
        lRowAI = Sht1.Cells(Rows.Count, "AI").End(xlUp).Row
        
        Range("AI3:AQ" & lRowAI).ClearContents
        startrow = Int(UserForm1.TexBox1.Value) + 1
        endrow = Int(UserForm1.TexBox1.Value) - 1
    
            If startrow < 1 Or endrow < 1 Then
                MsgBox "Invalid start or end rows", vbCritical, "ALERT"
                Exit Sub
            End If
            
        lRowAI = Sht1.Cells(Rows.Count, "AI").End(xlUp).Row
            
        'indicate first row where data should copy (could use lastrow+1 of destination column if you want to keep appending)
        cnt = 3
        endrow = startrow + endrow
    
        For x = startrow To endrow
            Cells(cnt, "AI") = Cells(x, "a")
                Cells(cnt, "AJ") = Cells(x, "d")
                    Cells(cnt, "AK") = Cells(x, "e")
                        Cells(cnt, "AL") = Cells(x, "f")
                            Cells(cnt, "AM") = Cells(x, "g")
                        Cells(cnt, "AN") = Cells(x, "h")
                    Cells(cnt, "AO") = Cells(x, "i")
                Cells(cnt, "AP") = Cells(x, "j")
            Cells(cnt, "AQ") = Cells(x, "k")
            
            cnt = cnt + 1
        Next x
        
        Call SortRows
        
End Sub
Sub SortRows()
        
    Dim iRow As Long
    Dim FirstRow As Long
    Dim LastRow As Long
        
       With ActiveSheet
            FirstRow = 3 'change to 2 if there are headings
            LastRow = .Cells(.Rows.Count, "AK").End(xlUp).Row
            For iRow = FirstRow To LastRow
                With .Cells(iRow, "AK").Resize(1, 7)
                    .Sort Key1:=.Columns(1), _
                    Order1:=xlAscending, _
                    Header:=xlNo, _
                    OrderCustom:=1, _
                    MatchCase:=False, _
                    Orientation:=xlLeftToRight
                End With
            Next iRow
        End With
End Sub

So if the Data on Sheet1 "AI2:AQ" with formulas before copying new Rows was -

Columns "A2:A" & ", D2:K" have the raw Data
Column "AI2" has a formula (=A18)
Column "AJ2" has a formula (=D18)
Columns "AK2:AQ2" have an array formula '=SMALL(E18:K18,{1,2,3,4,5,6,7})'

And then drag down from "AI2:AQ2" to autofill the rows "AI2:AQ15"

ie​

	A	B	C	D	E	F	G	H	I	J	K	L	M					AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	Date/Year			Cnt			Results													Date/Year	Cnt		Ascending
2	28/03/18			1	140	139	138	137	136	135	134									05/04/18	17	22	23	24	25	26	27	28
3	28/03/18			2	133	132	131	130	129	128	127									05/04/18	18	15	16	17	18	19	20	21
4	29/03/18			3	126	125	124	123	122	121	120									06/04/18	19	8	9	10	11	12	13	14
5	29/03/18			4	119	118	117	116	115	114	113									06/04/18	20	1	2	3	4	5	6	7
6	30/03/18			5	112	111	110	109	108	107	106									07/04/18	21	1	2	3	4	5	6	7
7	30/03/18			6	105	104	103	102	101	100	99									07/04/18	22	1	2	3	4	5	6	7
8	31/03/18			7	98	97	96	95	94	93	92									08/04/18	23	1	2	3	4	5	6	7
9	31/03/18			8	91	90	89	88	87	86	85									08/04/18	24	1	2	3	4	5	6	7
10	01/04/18			9	84	83	82	81	80	79	78									09/04/18	25	1	2	3	4	5	6	7
11	01/04/18			10	77	76	75	74	73	72	71									09/04/18	26	1	2	3	4	5	6	7
12	02/04/18			11	70	69	68	67	66	65	64									10/04/18	27	1	2	3	4	5	6	7
13	02/04/18			12	63	62	61	60	59	58	57									10/04/18	28	1	2	3	4	5	6	7
14	03/04/18			13	56	55	54	53	52	51	50									11/04/18	29	1	2	3	4	5	6	7
15	03/04/18			14	49	48	47	46	45	44	43									11/04/18	30	0	0	0	0	0	0	0
16	04/04/18			15	42	41	40	39	38	37	36
17	04/04/18			16	35	34	33	32	31	30	29
18	05/04/18			17	28	27	26	25	24	23	22
19	05/04/18			18	21	20	19	18	17	16	15
20	06/04/18			19	14	13	12	11	10	9	8
21	06/04/18			20	7	6	5	4	3	2	1
22	07/04/18			21	7	6	5	4	3	2	1
23	07/04/18			22	7	6	5	4	3	2	1
24	08/04/18			23	7	6	5	4	3	2	1
25	08/04/18			24	7	6	5	4	3	2	1
26	09/04/18			25	7	6	5	4	3	2	1
27	09/04/18			26	7	6	5	4	3	2	1
28	10/04/18			27	7	6	5	4	3	2	1
29	10/04/18			28	7	6	5	4	3	2	1
30	11/04/18			29	7	6	5	4	3	2	1
31	11/04/18			30	0	0	0	0	0	0	0

<colgroup><col><col><col><col><col><col span="5"><col><col><col><col><col span="6"><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

​

 

[pwill]

Re: Add formula's to macro

Just thought someone might find it useful?

Regards

pwill

 

[Roderick_E]

Re: Add formula's to macro

nice work pwill. Sorry I couldn't help in time. I'm off on weekends.

 

[pwill]

Re: Add formula's to macro

Thanks Roderick_E, no worries I got there in the end

 

[alerolim1]

Re: Add formula's to macro

O que tem de errado na minha macro

Option Explicit


Sub GeraNovaPlanilha()
ActiveCell.Value = ActiveCell.Value


Sheets("ATUAL").Select
Sheets("ATUAL").Copy After:=Sheets(1)
Range("R9:S9").Select
Sheets("ATUAL (2)").Select
Sheets("ATUAL (2)").Name = ActiveCell.Value
ActiveSheet.Shapes.Range(Array("Button 94")).Select
Selection.Delete
Sheets("ATUAL").Select
Range("C9:P9").Select
Selection.ClearContents
Range("R9:S9").Select
Selection.ClearContents
Range("V9:W9").Select
Selection.ClearContents
Range("F10:P10").Select
Selection.ClearContents
Range("D11:G11").Select
Selection.ClearContents
Range("K11:W11").Select
Selection.ClearContents
Range("D13:W13").Select
Range("c99").Select

End Sub

 

[pwill]

Re: Add formula's to macro

re: Add formula's to macro

i have managed to find myself a solution to my problem by keeping "ai2:aq2" row in play with the formulas.

I have the userform copy the rows to start at "ai3:aq3".
Allowing to autofill back down with the formulas from in "ai2:aq2" when finished with the data added from the userform.
I have also added code to sort the rows in ascending order left to right in columns "ak:aq" for the rows added with the userform.

New code for the userform as -​

change line in red:

​

From-
        startrow = int(userform1.texbox1.value) + 1
       endrow = int(userform1.texbox1.value) - 1​
​
To-
​
        startrow = int(userform1.texbox1.value) + 1
​
       endrow = int(userform1.texbox2.value) - 1​​
​​
<strike></strike><strike></strike>
​

Rehards
​