VBA issues to copy one cell, then offset, then continue

[fourmurphys]

I'm attaching an example file.
We'll record sales in order, with seller name.
I've tried (unsuccessfully) to create VBA to summarize (cells H2:M12). Goal is to find (for example) "Seller 1" in column B, and each time it finds "Seller 1", offset to column A, copy that cell, and paste to the right of the seller's name in column H. Then keep looking down B, and keep pasting to the right of H...
I know what's going wrong (I keep resetting to the first reference), but I don't know how to fix...

Any help is appreciated. The code is in the file, but also posted here.

Please Login or Register  to view this content.

[Leith Ross]

Hello fourmurphys,

This version of the macro worked in my tests.

Please Login or Register  to view this content.

[fourmurphys]

Fantastic Leath, thank you so much!

I'm truly always amazed by the generosity of folks in this forum... Thanks!

[Leith Ross]

Hello fourmurphys,

You're welcome. If you have any questions about what the code is doing, please ask.

[fourmurphys]

If you have any questions about what the code is doing, please ask.

Actually... am curious about which part of the code is actually copy/pasting the value from column A into the SalesTbl range...?

[Leith Ross]

Hello fourmurphys,

The Order variable holds the value from column "A" for the current Cell in column "B".

The row in the table that matches the Seller in column "B" is done by this line...

Please Login or Register  to view this content.

This line of code copies the Order into the table at the given Row.

Please Login or Register  to view this content.

SpecialCells returns a Range of Blank cells. The first cell in the group is the one where the Order will copied.

Note: If SpecialCells fails to find any Blank Cells it will error. That is why the On Error statement is there.

[fourmurphys]

Wow, thanks Leith! I'm super close to an epiphanal moment...
for the row line of code:

Please Login or Register  to view this content.

The (1,1) makes me think it's offsetting 1 row and 1 column... but isn't?

[spencer_time]

In my experience .cells(r, c) is referring to a cell located at row r and column c, the .value after it would then take the value of it. Therfore I would read the last part as making the value of A1 equal to the variable order.

However, I'm not familiar with specialCells, so it may act differently.

EDIT 15:47CST: Corrected a misspelling.

[Leith Ross]

Hello fourmurphys,

The SpecialCells returns a Range, in this case, of any blank cells in the SalesTbl.Rows(Row). This Range may or may not be contiguous. What we are interested in is the first blank cell in the returned Range of cells. To get that cell I use the Cells method to return the first cell in the first row and column of the SpecialCells Range.

[fourmurphys]

Hello fourmurphys,

The SpecialCells returns a Range, in this case, of any blank cells in the SalesTbl.Rows(Row). This Range may or may not be contiguous. What we are interested in is the first blank cell in the returned Range of cells. To get that cell I use the Cells method to return the first cell in the first row and column of the SpecialCells Range.

Thanks! Perfect.