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

[MrSams]

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

Example Data Distribution.jpg

[SamT]

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]

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]

The error says :
2016-10-03_12-22-59.png

[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?

[Paul_Hossler]

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]

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



OFFSET.jpg

[Paul_Hossler]

Capture.JPG

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]

Thanks Paul

[MrSams]

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]

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