PDA

View Full Version : Whats wrong with my code, copy paste does not change Array formula



MrSams
10-01-2016, 06:11 PM
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
10-01-2016, 09:52 PM
Ypur code is not "copy & paste".
"copy & paste" is different from "insert copied cells".

MrSams
10-01-2016, 11:24 PM
So...... What do. Need t change in the macro to fx our issue?

mana
10-02-2016, 12:04 AM
Which operation do you want to do?
"copy & paste" or "insert copied cells"

MrSams
10-02-2016, 04:12 AM
I want to copy rows on the Drawn Numbers sheet and paste them on to the Input sheet

mana
10-02-2016, 04:32 AM
Why? unmatch no.of rows between source and destination.


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

mana
10-02-2016, 04:47 AM
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
10-02-2016, 05:55 AM
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
10-02-2016, 07:03 AM
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
10-02-2016, 02:37 PM
this is not okay, I want A-G to change the same way

SamT
10-02-2016, 03:48 PM
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 94 Group, and I don't understand you.

MrSams
10-02-2016, 04:19 PM
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
10-02-2016, 05:42 PM
You're fighting the way Excel adjusts formulas

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

17238

MrSams
10-02-2016, 06:00 PM
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
10-02-2016, 07:22 PM
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
10-02-2016, 08:04 PM
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
10-02-2016, 08:41 PM
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


17240

MrSams
10-02-2016, 09:18 PM
Paul ....... BRILLIANT!!!!!! thank you thank you thank you!!!!!

MrSams
10-03-2016, 08:15 AM
Paul / SamT,

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


17248

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
10-03-2016, 08:41 AM
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

MrSams
10-03-2016, 10:06 AM
Paul,

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

(http://www.vbaexpress.com/forum/attachment.php?attachmentid=17241&d=1475465900) 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

17252

SamT
10-03-2016, 10:34 AM
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.

Paul_Hossler
10-03-2016, 11:48 AM
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

MrSams
10-03-2016, 12:10 PM
The error says :
17254

MrSams
10-03-2016, 01:07 PM
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?

Paul_Hossler
10-03-2016, 01:24 PM
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.

MrSams
10-05-2016, 08:22 PM
Paul, if you are still monitoring this post. How would I write the offset for 6 locations? Thanks



17269

Paul_Hossler
10-06-2016, 07:04 AM
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

MrSams
10-06-2016, 08:15 AM
Thanks Paul

MrSams
10-07-2016, 12:30 PM
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


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

Paul_Hossler
10-07-2016, 02:24 PM
Actually I was wrong - j does get set

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




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


I didn't look at that part of the code

My bad