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

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?

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

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

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

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

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


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

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.

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

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.

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

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.

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?

You're fighting the way Excel adjusts formulas

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

Attachment 17238

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

Quote:

---

Originally Posted by 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

---

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

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

I was only addressing

Quote:

---

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

---

Quote:

---

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

Quote:

---

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:

Quote:

---

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

Quote:

---

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


Attachment 17240

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

Paul / SamT,

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


Attachment 17248

Here is the Macro

Code:

---

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

---

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

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

Code:

---


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

Code:

---

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

---

Paul,

If you look at the file you attached TS 4+ BB Game 3 (Macro Numbers Input)_phh2.xlsm.

The 2nd Macro takes the data from the "Counter Totals" sheet and paste them to Game3 sheet rows 50-100-150-200-250. Every time a new number sequences from the Input sheet it changes the data in the "Counter Totals" sheet and that data moves down a row in Game3


This is the same sheet I wanted to move where it paste the results so I can add more rows. In other words, right now I have the following from

Instead of the data going to50-100-150-200-250 I want it to go to 50-3000-6000-9000-12000

Attachment 17252

Quote:

---

After it runs through for about 40 rows we keep getting this error from my macro

---

Sloppy writing again.

You did not tell us what "This Error" was, all you did was show us where the Error occurred.

Sorry, but I really don't understand the requirements, and therefore I don't understand the macros that do the real processing

I jumped in to suggest some VBA functions, etc. about a specific problem that had very limited scope and was VBA & Excel in nature

Without a very clear and very detailed requirements spec, I would not be able to help

The other complicating issue is apparently there are external links and multiple workbooks, which make testing very difficult

The error says :
Attachment 17254

My apologies Paul, I am trying to help another individual and I have very limited experience with vba and excel but have more access to the computer while on campus. If you are willing to help AI will do my best to get good expectations and perhaps just do a new macro?

Quote:

---

Originally Posted by MrSams

My apologies Paul, I am trying to help another individual and I have very limited experience with vba and excel but have more access to the computer while on campus. If you are willing to help AI will do my best to get good expectations and perhaps just do a new macro?

---

No problem, it's just that it's very difficult (and requires more time than I can invest) in working my way through macros and worksheets that I'm not familiar with.

Paul, if you are still monitoring this post. How would I write the offset for 6 locations? Thanks



Attachment 17269

Attachment 17272

Basically the same way

OFFSET (B2,0,7,1,6)

a. using B2 as a base (or reference cell)

b. go up(-) or down(+) some rows, in this case zero rows (i.e. stay on B2's row), and go right(+) or left(-) seven columns from B (i.e. Col I2)

d. get 1 row and 6 columns starting at I2, or I2:N2 as a range

Thanks Paul

Quote:

---

Originally Posted by 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

Code:

---


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

Code:

---

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

---

---

Paul,
im trying very hard to understand VBA, what should 0 be? What does that code say or what does it do? Perhaps if I understand this I can help my student as well as myself to ask a more intelligent question. I'm still writing up a description /procedure what and how his spreadsheet should work. Almost done
as always thanks, and if I need to start a new post with the description please let me know

Actually I was wrong - j does get set

It starts as = 0, but then the first "Game" changes it to 1, then 2, etc.

Code:

---

    If (x(i, 1)) = "Game" Then j = j + 1

---

I didn't look at that part of the code

My bad