Excel VBA help (loop)

[maxxev]

Hi can you help me please to identify what is wrong with my code (it doesn't do anything).

I'm not proficient with excel terms.

Sub FillEveryOtherColumn()

    Dim lastCell As Integer
    Dim lastColumn As Integer
    Dim i As Integer

    Dim End1 As Integer
    Dim Start2 As Integer
    Dim End2 As Integer
    
    Application.ScreenUpdating = False

'Find last populated column after Column G
lastCell = Range("G1").End(xlToRight).Column
'Calculate the number of Columns you'll need to fill
lastColumn = (lastCell / 2) + 1

'Find last populated row in first half of column (58 rows before the end)
End1 = Cells(Rows.Count, "A").End(xlUp).Row - 58
'Find first populated row in 2nd half of column (41 rows before the end
Start2 = End1 - 41
'Find last populated row
End2 = Cells(Rows.Count, "A").End(xlUp).Row
'Start with Column G (Column 7), and fill every other column

For i = 7 To lastColumn Step 1
    Range(Cells(7, i), Cells(End1, i)).Select
        Selection.FillDown
    Range(Cells(Start2, i), Cells(End2, i)).Select
        Selection.FillDown
Next i
    Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Hi again,

    'Find last populated row in first half of column (58 rows before the end)
'[b]Why 58 rows before the end???[/b]

    End1 = Cells(Rows.Count, "A").End(xlUp).Row - 58
    'Find first populated row in 2nd half of column (41 rows before the end
'[b]Why 41 rows before the end???[/b]
    
    Start2 = End1 - 41
    'Find last populated row
    End2 = Cells(Rows.Count, "A").End(xlUp).Row
    'Start with Column G (Column 7), and fill every other column
'[b]Shouldn't evey other column be Step [red]2[/red]?[/b]
    
    For i = 7 To lastColumn Step 1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxxev]

The - rows are because there are 2 spread sheets in one if you will, 58 cells from the end the first spreadsheet ends and the 2nd begins (this is essential and can't be done any otherway)

I made a first attempt at this code without a loop which worked but I don't want to have to repeat this codes TONNES of times (there are 16 worksheets and a lot of them are full on columns)
But in case it helps:

Sub Fillcolumns()
    Application.ScreenUpdating = False

    End1= Cells(Rows.Count, "A").End(xlUp).Row - 58
    Start2= End1- 58
    End2= Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("G7").Select
    Selection.AutoFill Destination:=Range("G7:G" & End1), Type:=xlFillDefault
    Range("G568").Select
    Selection.AutoFill Destination:=Range("G" & Start2& ":G" & End2), Type:=xlFillDefault
    Application.ScreenUpdating = True
End Sub

 

[maxxev]

To explain the -58 and -48 in a different way, imagine this is a spread sheet with a lot of totals can calculations at the bottom, I don't want to filldown over the totals, it's not quite accurate, but it's easier to explain!

 

[SkipVought]

and can't be done any otherway

That kind of statement, usually means that you don't know of any other way, but often there REALLY IS ANOTHER WAY!!!

What you have described sounds like a royal mess.

What are the 16 sheets?

Do they contain similar data?

Totals at the BOTTOM of a table is old thinking, tied to accounting books, pencils and adding machines. With applications like Excel, you can put aggregations ANYWHERE, in convenient places so that user do not have to hunt for the bottom of each "page"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxxev]

I knew as soon as i'd posted that, it was a poor choice of words!

What I mean is this is a sheet central to stock ordering to the company with many links coming off of it and i'm not going to be ALLOWED to change the format.

the sheet contains 500 products along the Y axis and 16 spreadsheets with a total of 330 ingredients running along the x axis.

2 columns per ingredient, first column has the usage in the product, the 2nd column calculates the (usage x production)

Then as I said there is a split in the middle of each sheet that is used for shorter shelf life ingredients so ordering can ensure the ingredients for these products have sufficient life and not bulk order them.

there are totals for both sides of that split, those totals then pull through to another spread sheet for the stock guys.

The workbook is archaic, and is due for a replacement with a database as soon as we can (but that wont be very soon).

I am simply trying to make the adminiatration of the sheet as easy as possible by this addition.

I created a newer version of the sheet ages ago, but it was 200mb is size and literally took 10 mins to do anything due to the complex formula, I don't doubt there is a better way of calculating stock usage, I just don't have the time to investigate or the freedom with all the workbooks that link from this one workbook.

Cheers

 

[SkipVought]

the sheet contains 500 products along the Y axis

The workbook is archaic,

This is puzzeling.

What do you mean by the Y axis? It CANNOT mean 500+ columns, if your workbook is "archaic.

Does your TABLE on your master sheet contain information defining the 500 products and the shelf-life?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxxev]

Worksheet 1 =
500 rows of products and columns for production by day for a week.

Worksheets 2-19 (counted them)
Ingredients & packaging (cereals, dairy, chocolate, etc).
format is:
Products on the Y axis (down the side) ingredients accross the x axis (accross the top).

Totals on these 19 workbooks have g / kg/ number of containers*.

Worksheet 20
Jas a list of ingredients down the side with the weight of a container and the totals pulled through from the 19 other forms, this sheet is the one the stock guys link to, but all the totals on this sheet come from the other 19.

I could probably change all that but it's a lot of work for little benifit.

The sheet workbook is 6 years old in design and will be replaced by a database (at last) but not for probably 6 months to a year yet as the database is going to be bespoke.

 

[SkipVought]

So what is the objective for filling every other column from G to the end?

Is your data contiguous? (ie are there empty rows/columns?)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxxev]

When a new product is added to the sheet it is done so by inserting a new line alphabetically, this means that on those 19 sheets there is now a totally blank row across all columns.

Because the sheet is formatted with 2 columns per ingredient, one for the ingredient usage and the other with formula to calculate the production vs ingredient usage you cannot fill down the entire line as it would be filling down ingredient usage from the product above.

So I want to do this code in order to fill down the formula on every other column.

 

[SkipVought]

FYI, both INSERT and DELETE methods are fraught with pitfalls. I rarely use either but with caution that comes with a decade and a half of Excel experience.

A much better way is to add to the first available row below your table and SORT into the desired position. It is also why I NEVER EVER put aggregations below my tables.

However, if you are using Excel 2007, Insert a Table using your existing data, and your aggregations will automatically adjust to new rows of data that are added to the bottom, as I suggested.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxxev]

excel 2003

 

[SkipVought]

2003 and earlier versions have a feature Data > List > Create List which will, for instance, propogate formulas to new rows as data is entered at TO THE BOTTOM of the table. The Automatic Aggragations feature is new to 2007.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What your code would need to do is INSERT on the row containing the aggregations to push them down. Then add your data to the next empty row in the table. Use Dynamic Named Ranges to automatically adjust the ranges needed for calculation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxxev]

The reason I never used the sort acsending option and enter the products at the end is that you cannot do this on 19 tables at once, it meant selecting each tab and sorting individually.

Back then I didn't have the knowledge to sort all tabs using a macro...

 

[SkipVought]

I hope that when the time comes to convert to a database, they employ a good consultant that will give the proper direction for what they need and generage a sound design and process.

But for now, my former question...

So what is the objective for filling every other column from G to the end?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxxev]

Every other column starting at G contains the formula to calcualte the ingredient usage vs the production qty.

 

[SkipVought]

And this step would be totally eliminated if you were using the LIST feature of Excel, and adding your new data in the new row below the table.

    'Find last populated row in first half of column (58 rows before the end)
    End1 = Cells(Rows.Count, "A").End(xlUp).Row - 58

Is the ALWAYS 58? If this range defines a certain type of ingredient, I think you said, what if some rows gets inserted or deleted that would CHANGE that "offset"?

So what information in your table could logically determine what that range should be?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}