VBA For every two cell group, give each cell in the group the same value.

[wabrown90]

Hope I explain this well enough for some help. I have tried multiple ways of coding this with no real success, so I figured it would be best for me to explain and start from scratch.

I have a spreadsheet with approximately 11000 rows of data. Rows 4&5 represent a group of data, 5&6 a group of data, and so on.

For each group of rows, one of the cells in column T already has data stored. For example, in the first group (rows 4&5), cell T4 holds the data.

However, there is not a pattern to the cells in which the data is stored, such as odd or even numbered rows. In group of rows 8&9, group T9 stores the data.

For each group of rows, I need for each cell to hold the same number. Example: Cells T4 and T5 should both hold the value of 78 (originally found in T4) . Cells T8 and T9 should both hold the value of 100 (originally found in T9)



Any help with this is greatly appreciated. New to VBA and struggling with where to go with this problem.

Thanks!

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[wabrown90]

Please read the yellow banner at the top of this page on how to attach a file.

Thanks - corrected

[alansidman]

Your sample does not match your explanation in post #1. Additionally, you have not provided a mocked up solution for your example. We truly only need 8-10 records -- not 10,000. Please elaborate on your explanation to match your sample data as it does not make sense currently.

[wabrown90]

Your sample does not match your explanation in post #1. Additionally, you have not provided a mocked up solution for your example. We truly only need 8-10 records -- not 10,000. Please elaborate on your explanation to match your sample data as it does not make sense currently.

I had to delete the remainder for confidentiality purposes. The groupings still apply (rows 2&3, 4&5, etc. as groups).

[Fluff13]

How about

Please Login or Register  to view this content.

[wabrown90]

How about

Please Login or Register  to view this content.

Fluff - this worked perfectly. Thank you for the help!

If you don't mind - could you explain this line of code? Still new to much of this.

.SpecialCells(xlBlanks).FormulaR1C1 = "=if(iseven(row()),r[1]c,r[-1]c)"

[Fluff13]

This part looks for all the blank cells in the range

Please Login or Register  to view this content.

and then it puts this formula into those cells (using R1C1 notation)

Please Login or Register  to view this content.

[wabrown90]

As a followup to this - I am now operating with a different column of data, all negative numbers. I am looking to copy within the groups as above, but now turn these numbers into positive numbers. The code I am trying to use will copy the numbers exactly, but will not turn them positive. Below is the modification to the above code that I am attempting to use:

Please Login or Register  to view this content.

[Fluff13]

Can you please post a sample workbook along with expected results?

[wabrown90]

Can you please post a sample workbook along with expected results?

Sorry about that. Should have posted that originally.

Rows 2 and 3 are what the expected results should be. The blank cell within the respective group (2&3, 4&5, etc.) should be the same number, but with a positive number instead of a negative.

[Fluff13]

How about

Please Login or Register  to view this content.

[wabrown90]

How about

Please Login or Register  to view this content.

Fluff - you did it again! Thanks again. Pretty clear I got too carried away with the withs/loops in my attempt.

[Fluff13]

You're welcome & thanks for the feedback.