vba dragging formula left to right the number of columns stops based on a value

[armchairandy]

I am attempting to reduce the amount of formulas in my workbook. So I only want to copy enough formula for the individual sheet. I have a cell A1 that contains a number, what I want to do is copy/drag the formula in B2 to the right by the number held in A1. ie if A1 = 12, then it would copy/drag the formula in B2 12 times & then stop.

I would also need to know how to copy formula downwards based on the range above.

I am currently using:
Range("L15").Select

Range("L15:AJ15").AutoFill Destination:=Range("L15:AJ" & Worksheets("SCData").Cells(Rows.Count, "A").End(xlUp).Row + 28)

Counts the rows in SCData that has data in it & copies the formulas between L15:AJ15 downwards by that amount. Although this accounts for the bulk of data I am trying to use - the range is pre-determined & therefore has more formula incl. than is necessary. Each worksheet has this and the amount of formula required differs each time

If I could use the range from the first problem for the autofill in the second my workbook would be more manageable.

Finally, the workbook is for use by multiple users that do not have any excel skills, and the workbook is protected to stop accidental deletions of formula

Any help really appreciated

Andrew

 

[jasonb75]

Something like

Range("B2").Resize(Worksheets("SCData").Cells(Rows.Count, "A").End(xlUp).Row + 28 -Range("B2").Row, Range("A1").Value).Formula = Range("B2").Formula

This is based in B2 as a reference point, not L15, so the row positions may not be as required.

 

[armchairandy]

Jason

Many thanks for your quick response. As you probably guessed Im not experienced in vba. copy the formula by the value in B2

The vba you put up: this takes the number in B2, and the number of rows with data in Column A & drags the formula in B2 to the right by the number in B2 & down by the number of rows with data in Col a?

If so that would definitely work for me, what if I needed to do the first problem, ie copy the formula on a single row by the value in B2.

Again many thanks

Andrew

 

[jasonb75]

Sorry, I think my post was misleading, the code takes the formula in B2 and copies it to the number of columns in A1

If A1 contains 10 then it will copy the formula as far as K2 (10 columns including column B).

The number of rows that it fills down is where I wasn't sure what you wanted, the reference of Rows + 28 and starting at L15 was what threw me when the earlier part of your post asked about starting at B2. This slightly altered version of the line I suggested will count the rows in SCData, then add 28, so if there are 10 rows in SCData, then this will fill the formula down 38 rows. With the formula starting in Row 2, that means it will fill down to row 39 (row 2 is the first of the 38 that is filled).

Range("B2").Resize(Worksheets("SCData").Cells(Rows.Count, "A").End(xlUp).Row + 28, Range("A1").Value).Formula = Range("B2").Formula

Simplifying the code even further to hopefully aid your understanding, the Resize(rows, columns) part is where the fill range is set,

Range("B2").Resize(rows, columns).Formula = Range("B2").Formula

Using Resize(1, 10) will fill 10 columns in the same row, Resize(10, 1) will fill down 10 rows in the same column, Resize(10, 10) will fill 10 rows by 10 columns. As with most things in vba, these numbers can be defined in pretty much any way that suits the task. The hardest part is usually finding the most suitable way.

Hope this helps.

 

[armchairandy]

Jason

No, I think my post was probably misleading. I knew what I meant, but I wrote it without fully explaining what was in my worksheet. You have explained it really well for me to understand. You have saved me hours of work, many thanks

Regards

Andrew