Whats wrong with my code, copy paste does not change Array formula

[MrSams]

Basically the Macro copies data from The Drawn Numbers sheet to the Input sheet starting at row 49. When it paste it in I49:N101 the Array formula in B49:G101 does not change or match its corresponding cells I49:N101 and I can not figure out why. Once Macro is ran B49:G49 is not the same as I49:N49 and so on down

I am not concerned with the Macro error because it works fine in a normal book. I want to know why the Array 49:G101 does not follow the numbers in I49:N101 when you click on the Grabber button

perhaps a calculate statement somewhere?

[mana]

Ypur code is not "copy & paste".
"copy & paste" is different from "insert copied cells".

[MrSams]

So...... What do. Need t change in the macro to fx our issue?

[mana]

Which operation do you want to do?
"copy & paste" or "insert copied cells"

[MrSams]

I want to copy rows on the Drawn Numbers sheet and paste them on to the Input sheet

[mana]

Why? unmatch no.of rows between source and destination.


Drawn Numbers sheet:1605rows
Input sheet I49:N101:53rows

[mana]

1)at first you try "copy & paste" manually with 2 rows data.
2)then you try recording a macro.
3)plese post the recorded macro.

[MrSams]

What the grabber macro to does is takes a row of numbers from the drawn number sheet and places them in row 49 Input sheet each time moving all the numbers "down" 1 row. So the numbers in row 49 go to row 50, row 50 goes to row 51 and so on. Then the other Macro does another function.

i need to figure out why the Array formula numbers don't follow the pasted numbers. If I copy and paste manually it works every time but when I use the macro t does not

both macros do what I need. I Need to fix this portion

thanks for helping

[mana]

When you repeat "insert copied cells",
rows count increse in Columns "I"-"N" only ,
but Columns "A"-"G" don't change.


Is it OK?


Sorry, I can't understand what you want to do.

[MrSams]

this is not okay, I want A-G to change the same way

[SamT]

Professor, You will find that short sentences constructed in Proper English, using the correct Excel and VBA terminology, will get you better and faster results.

I am an American member of the 9⁴ Group, and I don't understand you.

[MrSams]

Lol SamT, replying on a cell phone has its faults , I was relying to mana

i need both areas to do the same thing. What's happening is when we run the macro they do as needed "but" because A:G has an Array in them they are not moving down nor are they matching what's happening in I:N columns.

If you manually copy from the Drawn numbers sheet and paste to the Input sheet without the macro it does fine. When you run the Macro it does not.

Does this make better sense?

[Paul_Hossler]

You're fighting the way Excel adjusts formulas

Change the array formula so there are no dependences on the shifted cells

Capture.JPG

[MrSams]

How do we do this Paul? If We do a simple =I:49 in B49 once the macro runs the numbers don't move down nor match each other

[Paul_Hossler]

How do we do this Paul? If We do a simple =I:49 in B49 once the macro runs the numbers don't move down nor match each other

The formula in the formula bar for the orange cells in the screen shot seems to work for me

As you can see, I inserted 7/18/2016 data into I49:N49 and the B49:E49 data was updated

Change ALL of the B:E array formulas to that and then see if the Insert Copied Cells is correctly updated

Do something similar for the formulas in col N


If you want to look at the answers in the back of the book for even numbered problems, look at this attachment

[MrSams]

Paul.

I am trying hard to follow you. You said you inputted data from7/18/2016 into I49:N49. This row has 9/29/2016.

Where does the 0,7,1,6 come from in the Array formulas?
Row 49 has 7-8-24-32 BB 5 in I:M and we have 5-7-8-24 BB5, what happened to 32 and the position of the numbers changed

Position #1 - 7 and 5
Position #2 - 8 and 7
Position #3 - 24 and 8
Position #4 - 32 and 24

It appears the numbers move down and follow but out of order as mentioned above plus We really appreciate your help with this but I for one have no clue what to do with this to get the correct order

[Paul_Hossler]

I was only addressing

When it paste it in I49:N101 the Array formula in B49:G101 does not change or match its corresponding cells I49:N101 and I can not figure out why. Once Macro is ran B49:G49 is not the same as I49:N49 and so on down

I am trying hard to follow you. You said you inputted data from7/18/2016 into I49:N49. This row has 9/29/2016

Not after I inserted some test data. I didn't care about data fidelity, only the formulas working right

Where does the 0,7,1,6 come from in the Array formulas?

The original array formula in B49:E49 referred to I49:L49

When your macro inserts data into I49:L49 it pushes down (as far as I can tell), but the references in B49:E49 adjust to now point to I50:L50

To avoid that you need to NOT explicitly refer to other cells,

So I used OFFSET. For more information read the online help:

OFFSET function

This article describes the formula syntax and usage of the OFFSET function in Microsoft Excel.
Description

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
Syntax

OFFSET(reference, rows, cols, [height], [width])
The OFFSET function syntax has the following arguments:

• Reference Required. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
• Rows Required. The number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
• Cols Required. The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
• Height Optional. The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
• Width Optional. The width, in number of columns, that you want the returned reference to be. Width must be a positive number.

I grabbed 7/18/2016 data to insert for testing and only in updated the formulas in row 49 when I made the screen shot to make sure that the use of OFFSET would point to the newly inserted values in I49:L49 and not to the original data

Row 49 has 7-8-24-32 BB 5 in I:M and we have 5-7-8-24 BB5, what happened to 32 and the position of the numbers changed

Position #1 - 7 and 5
Position #2 - 8 and 7
Position #3 - 24 and 8
Position #4 - 32 and 24

I had a typo: the formula should be =SMALL(OFFSET(B49,0,7,1,4),{1,2,3,4}) array entered

To manually test again (since your macro doesn't work as is) I changed the formulas in B:G and inserted shift down some test data in I49:L49 (red) shifting everything down


Capture.JPG

[MrSams]

Paul ....... BRILLIANT!!!!!! thank you thank you thank you!!!!!

[MrSams]

Paul / SamT,

After it runs through for about 40 rows we keep getting this error from my macro. Do you know why?


Area J.jpg

Here is the Macro

Option Explicit

Sub ertert()
Dim x, i&, j&
With Sheets("Counter Totals")
    x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x)
    If (x(i, 1)) = "Game" Then j = j + 1
    If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then
        With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
            .Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0)
        End With
    End If
Next i
End Sub

Sub ClearGames()
Dim wsh As Worksheet, r As Range
For Each wsh In ThisWorkbook.Sheets
    If Not wsh Is ActiveSheet Then
        For Each r In wsh.Columns(1).SpecialCells(2).Areas
            r.Resize(, 91).Offset(1).CLEAR
        Next
    End If
Next wsh
End Sub

[Paul_Hossler]

1. You never set j so it's = 0

2. No idea what this is intended to do, but the syntax is probably wrong

With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2) 
      .Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0) 
 End With

but if sub ClearGames works, maybe something based on it's logic

For Each r In wsh.Columns(1).SpecialCells(2).Areas 
           r.Resize(, 91).Offset(1).Value = Application.Index(x, i, 0) 
 Next