Incrementing formulas in VBA
Solved
Mistral_13200
Posted messages
649
Status
Member
-
Mistral_13200 Posted messages 649 Status Member -
Mistral_13200 Posted messages 649 Status Member -
Hello everyone,
In a spreadsheet, I use the following macro to:
- Create formulas.
- Copy these formulas into cells.
- Increment these formulas down to the last row.
When I step through the macro, I get an error "The AutoFill method of the Range class failed".
I'm stuck, can you help me?
Thanks in advance for your help
Mistral
In a spreadsheet, I use the following macro to:
- Create formulas.
- Copy these formulas into cells.
- Increment these formulas down to the last row.
Sub CalculFeuil()
' Variable declaration.
Dim UR As String
Dim Club As String
Dim Adh As String
Dim NumFihier As String
Dim DernLigne As Integer
' Calculate the last row in column AE.
DernLigne = Range("AE" & Rows.Count).End(xlUp).Row
' Declaration of formulas.
UR = "=LEFT(RC[13],2)"
Club = "=MID(RC[12],4,4)"
Adh = "=RIGHT(RC[11],4)"
NumFihier = "=CONCATENATE(RC[-3],RC[-2],RC[-1],""01"")"
' Copy formulas to the locations.
Range("R2").Formula = UR
Range("S2").Formula = Club
Range("T2").Formula = Adh
Range("U2").Formula = NumFihier
' Increment formulas down to the last row.
Range("R2").AutoFill Destination:=Range("R3:D" & DernLigne), Type:=xlFillDefault
Range("S2").AutoFill Destination:=Range("S3:E" & DernLigne), Type:=xlFillDefault
Range("T2").AutoFill Destination:=Range("T3:F" & DernLigne), Type:=xlFillDefault
Range("U2").AutoFill Destination:=Range("U3:G" & DernLigne), Type:=xlFillDefault
End Sub
When I step through the macro, I get an error "The AutoFill method of the Range class failed".
I'm stuck, can you help me?
Thanks in advance for your help
Mistral
2 answers
Hello
Without looking at your entire code, I notice that your Autofill does not contain, in the destination, the original cell, which is mandatory.
When you encounter an error like this, click on the property or method (Autofill here), and press F1 and you will get the answer:
https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofill?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk%28vbaxl10.chm144083%29%3Bk%28TargetFrameworkMoniker-Office.Version%3Dv16%29%26rd%3Dtrue
m@rina
--
Do not bother sending me questions privately. I do not respond.
Without looking at your entire code, I notice that your Autofill does not contain, in the destination, the original cell, which is mandatory.
When you encounter an error like this, click on the property or method (Autofill here), and press F1 and you will get the answer:
https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofill?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk%28vbaxl10.chm144083%29%3Bk%28TargetFrameworkMoniker-Office.Version%3Dv16%29%26rd%3Dtrue
m@rina
--
Do not bother sending me questions privately. I do not respond.
Hello Marina,
Thank you for your response, which was very helpful since this morning, after a good night's sleep, I resolved my issue.
I found a macro on the web that met my needs, but I had never used "Autofill," which led to my mistake in adapting it to my file.
I often rely on the F1 key, but I admit that I sometimes have difficulty understanding everything.
Once again, thank you.
Mistral
Thank you for your response, which was very helpful since this morning, after a good night's sleep, I resolved my issue.
I found a macro on the web that met my needs, but I had never used "Autofill," which led to my mistake in adapting it to my file.
I often rely on the F1 key, but I admit that I sometimes have difficulty understanding everything.
Once again, thank you.
Mistral