# Copy “Comments” using VBA, with a twist.



## julhs (Dec 18, 2022)

Have a large “Input Section” (16 Cols & 500-700 Rows) that has comments scattered throughout it, currently my “Analysis Section” only contains values taken from the “Input Section” using {=IF(SUMPRODUCT,ROWS,INDEX,SMALL………)}

My understanding is that I can’t use a formula to get the “Comments” from the “Input Section” into the corresponding cell in the “Analysis Section”.

What I want to do is get the Values & Comments into the Analysis Section, presently I have to copy/paste "Comments" individually.

Is there a way to do this retrospect fully with VBA from a Command button?
P.S "Comments" dont seem to be appearing on XL2BB, cells with "Fill colour" are ones with Comments
Accounts 2016 - 2019 Final Currant.xlsmABCDEFGHIJKLMN12CASH BOOK3DEBITS4DateInvoice #Payment Method ListPayment Details ListBank & CashRentPowerMotoring Expenses Stock & MaterialsOffice56Jul-05gs0370Direct DebitRent250.00250.00INPUT SECTION All details etc on left are Enterd via a USERFORM, including the comments. It finds the next empty Row & pastes in Userform values, then inserts a new blank row and formats it7Jul-10gs0375Direct DebitEDF35.0035.008Jul-15gs0380Debit CardMicks Garage50.0050.009Jul-20gs0385Debit CardHalfords20.0020.0010Jan-21gs0390Debit CardSouthern Building Supplies31.0031.0011Jul-22gs0415Debit CardAmazon42.0042.0012Jul-24gs0433PayPalDirect Stationary Supplies10.0010.0013Aug-05gs0450Direct DebitRent275.00275.0014Aug-10gs0460Debit CardWessex Steel40.0040.0015Aug-10gs0462Direct DebitEDF50.0050.001617Totals803.00525.0085.0070.0071.0052.00181920ANALYSIS SECTION  RentEDFMicks GarageHalfordsSouthern Building SuppliesWessex SteelDirect Stationary SuppliesAmazon21This section lists all the entries for individual supplier using SUMPRODUCT,ROWS,INDEX,SMALL. It does get bumped down as rows are added above250.0035.0050.0020.0031.0040.0010.0042.0022275.0050.00      232425This is what I would like the desired outcome to beRentEDFMicks GarageHalfordsSouthern Building SuppliesWessex SteelDirect Stationary SuppliesAmazon26250.0035.0050.0020.0031.0040.0010.0042.0027275.0050.00Testing (2)Cell FormulasRangeFormulaF17:K17F17=SUM(F6:F15)G21:N22G21=IF(SUMPRODUCT(--($E$5:$E$15=G$20))>=ROWS(G$21:G21),INDEX($F$5:$F$15,SMALL(IF($E$5:$E$15=G$20,ROW($E$5:$E$15)-ROW($E$5)+1),ROWS(G$21:G21))),"")Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## Micron (Dec 18, 2022)

Did you consider copy (from Micks's to EDF, I guess) then paste special>All>transpose? 
Your target columns would have to be in the same order as your copied rows of course. I tried that and the comments were pasted. A lot easier than code?


----------



## julhs (Dec 18, 2022)

Thanks for reply Micron.
Entries in “Input section” will be in utterly random pattern, the “Analysis Section” has fixed Columns, but Row # will change due to being bumped down by the “Input Section”


----------



## Micron (Dec 18, 2022)

Does row# matter when copying/pasting? As for the randomness I presume you mean the order of things like the cells from Mick's to Rent? When you copy/paste, those cells become headers. So input




with output as



Sorry if I'm not following why that can't work. At the very least, I suppose you could go through the motions while recording a macro and get some clue as to the code you'd need if that's the route you need to take. BTW, not sure what those little red triangles are in your analysis section pic. They don't look like they represent errors, notes or threaded comments. The purple ones I show are threaded comments, which do paste into the new cells.


----------



## julhs (Dec 18, 2022)

For some reason XL2BB didn’t upload/show ANY Comments on the Mini Sheet that are on my ACTUAL sheet, had to resort to “Fill Colour” luminous green to indicate which cells had Comments. Ordinary “Comments” are red triangle in cells top right corner, SOME of the cells you fixed with purple triangle DO have “Comments”, but not all.

Just so we on the same lines:

This is an Accounts spreadsheet
Entries in “Input Section” are in random order (so the date/order is based on the date the Debit is made, irrespective of supplier name order)

I’m not using Copy/Paste FROM “Input section” to “Analysis Section”, the “Analysis Section” is using a formula {=IF(SUMPRODUCT,ROWS,INDEX,SMALL………)}
to pull the figures from the “Input Section” and list them under the Individual Suppliers, ie Rent,EDF,Micks Garage etc,

The “Analysis Section” has fixed headers.

What currently happens is, IF ANY entry in Col E matches the cell value in eg G20 (Rent), it pulls value from F20 and THAT value is placed it in the next blank cell under “Rent”, BUT WITHOUT the “Comment.”

What I’m trying do is pull FROM the “Input Section” the “Value” and the “Comment” from ie F13(Rent) into the next blank row under ie Rent

Everything I have posted works fine just to pull values from “Input Section” into “Analysis Section”, but I want to pull “Comments” at the time as well,

If formula option is off the table then maybe use VBA to get the “Comments” from Col F to relavant cells in G26:N27

I do pray this has made things clearer as opposed to more confusion!!!


----------



## Micron (Dec 18, 2022)

Sorry if I seemed to be pushing the transpose thing too much. It's just that without knowing how it all works it just seemed far simpler. I bet it can be done with vba. I have to sign off for a bit, but in the meantime, if you search posts I've made in the last 3 to 5 days, there have been at least two threads where I and others were dealing with notes (formerly known as comments) and "comments" (now called ThreadedComments I believe). Perhaps there is something in those that would get you started on code.
Here's one: Copying and Pasting CommentThreaded

The other also started by same OP as that one.


----------



## julhs (Dec 18, 2022)

Got side tracked and Message edit timed out.
Rephraseing/edit of above.

For some reason XL2BB didn’t upload/show ANY Comments on the Mini Sheet that are on my ACTUAL sheet, had to resort to “Fill Colour” luminous green to indicate which cells had Comments. Ordinary “Comments” are red triangle in cells top right corner; SOME of the cells you fixed with purple triangle DO have “Comments”, but not all.

Just so we on the same lines:
This is an Accounts spreadsheet
Entries in “Input Section” are in random order (so the date is when Debit is made, irrespective of supplier name)

I’m not using Copy/Paste FROM “Input section” to “Analysis Section”, the “Analysis Section” is using a formula {=IF(SUMPRODUCT,ROWS,INDEX,SMALL………)}
to pull the figures from the “Input Section” and list them under the Individual Suppliers, ie Rent,EDF,Micks Garage etc,

The “Analysis Section” has fixed headers.

What currently happens is, IF ANY entry in Col E matches the cell value in eg G20 (Rent), it pulls the next value from Col F and THAT value is placed it in the next blank cell under “Rent” (G20), BUT WITHOUT the “Comment.”

What I’m trying do is FROM the “Input Section” pull the “Value” and the “Comment” from ie, F??(Rent) into the next blank row under ie Rent (G20)

Everything I have posted works fine just to pull values from “Input Section” into “Analysis Section”, but I want to pull “Comments” at the time as well,
if formula option is off the table then maybe use VBA to get the “Comments” from “ Input Section” Col F to relevant cells in G26:N27 retrospect fully using separate VBA code

I do pray this has made things clearer as opposed to add more confusion, but I have my doubts!!!


----------



## julhs (Dec 19, 2022)

Can I stress again XI2BB didn’t show any comments that are in F5:K15 or G26:N27 so used “Fill colour” – luminous green to indicate the cells with “Comments”.
IGNORE the fact there are “Comments” in G6:K15 as they are only replicates of ones in F6:F15

The “Red/Orange” triangles in Analysis section (G21:N22) are {Array formulas} that look for entries in E5:E15 that match the names in G20:N20. If there is a match it then pulls the corresponding value from F5:F15 and puts/adds that value to the list under the “Matched Name” in the “Analysis Section”

The number of rows in “Input Section” continually increases, because the “Userform” enters its data into the first BLANK row of the “Input Section” (ie Row16) and THEN ALSO ADDS a new-blank-formatted row, so effectively bumping everything in E17:N27 down by one row.
(“Input Section” could end up having/being 500-700 rows, “Analysis Section” will then effectively be bumped down by same amount)

Transpose isn’t an option because names in F5:F??? “Input Section” will be in random order while the “Analysis Section” has fixed headings (G20:N20)


----------



## julhs (Dec 27, 2022)

Hi Micron,
I have failed miserably to adapt the code on the  link you posted regarding copying “Comments/Notes”!!
But also been researching alternative methods to achieve what I was planning.

Is there any chance you could have a look at that code and adapt it for me to fit with a slightly different criteria to my original post??

My Minimum thinking right now is to simply copy ALL the Comments/Notes from Col F into Col Q.
Next best thing is to; retrieve values from Col E into Col P and also/then retrieve from Col F the ”Comments & values” into Col Q.

That way I may be able to filter/vLookup those Columns for specific values?

Appreciate any guidence you can give!!
Julhs


----------



## Micron (Dec 27, 2022)

I'll see what I can do with the 2nd option, which seems like it would be your preferred result. Not sure because it seems more robust, but to me, "next best thing" implies that which is secondary, inferior, less desirable, etc.


----------



## julhs (Dec 18, 2022)

Have a large “Input Section” (16 Cols & 500-700 Rows) that has comments scattered throughout it, currently my “Analysis Section” only contains values taken from the “Input Section” using {=IF(SUMPRODUCT,ROWS,INDEX,SMALL………)}

My understanding is that I can’t use a formula to get the “Comments” from the “Input Section” into the corresponding cell in the “Analysis Section”.

What I want to do is get the Values & Comments into the Analysis Section, presently I have to copy/paste "Comments" individually.

Is there a way to do this retrospect fully with VBA from a Command button?
P.S "Comments" dont seem to be appearing on XL2BB, cells with "Fill colour" are ones with Comments
Accounts 2016 - 2019 Final Currant.xlsmABCDEFGHIJKLMN12CASH BOOK3DEBITS4DateInvoice #Payment Method ListPayment Details ListBank & CashRentPowerMotoring Expenses Stock & MaterialsOffice56Jul-05gs0370Direct DebitRent250.00250.00INPUT SECTION All details etc on left are Enterd via a USERFORM, including the comments. It finds the next empty Row & pastes in Userform values, then inserts a new blank row and formats it7Jul-10gs0375Direct DebitEDF35.0035.008Jul-15gs0380Debit CardMicks Garage50.0050.009Jul-20gs0385Debit CardHalfords20.0020.0010Jan-21gs0390Debit CardSouthern Building Supplies31.0031.0011Jul-22gs0415Debit CardAmazon42.0042.0012Jul-24gs0433PayPalDirect Stationary Supplies10.0010.0013Aug-05gs0450Direct DebitRent275.00275.0014Aug-10gs0460Debit CardWessex Steel40.0040.0015Aug-10gs0462Direct DebitEDF50.0050.001617Totals803.00525.0085.0070.0071.0052.00181920ANALYSIS SECTION  RentEDFMicks GarageHalfordsSouthern Building SuppliesWessex SteelDirect Stationary SuppliesAmazon21This section lists all the entries for individual supplier using SUMPRODUCT,ROWS,INDEX,SMALL. It does get bumped down as rows are added above250.0035.0050.0020.0031.0040.0010.0042.0022275.0050.00      232425This is what I would like the desired outcome to beRentEDFMicks GarageHalfordsSouthern Building SuppliesWessex SteelDirect Stationary SuppliesAmazon26250.0035.0050.0020.0031.0040.0010.0042.0027275.0050.00Testing (2)Cell FormulasRangeFormulaF17:K17F17=SUM(F6:F15)G21:N22G21=IF(SUMPRODUCT(--($E$5:$E$15=G$20))>=ROWS(G$21:G21),INDEX($F$5:$F$15,SMALL(IF($E$5:$E$15=G$20,ROW($E$5:$E$15)-ROW($E$5)+1),ROWS(G$21:G21))),"")Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## Micron (Dec 27, 2022)

Q: what does "retrieve" mean? Copy to Q or move to Q? Or something else?


----------



## Micron (Dec 27, 2022)

wish we had more than 10 minutes to edit a post. 
Another question: OK to copy everything from E (such as interior colour) or would just values be a priority?


----------



## julhs (Dec 27, 2022)

My “Minimum” being “Optiom 1” ; ALL the Comments/Notes from Col F into Col Q.
The BEST option is option 2, “Next Best” ; values from Col E into Col P and also/then retrieve from Col F ”Comments & values” into Col Q


----------



## julhs (Dec 27, 2022)

julhs said:


> My “Minimum” being “Optiom 1” ; ALL the Comments/Notes from Col F into Col Q.
> The BEST option is option 2, “Next Best” ; values from Col E into Col P and also/then retrieve from Col F ”Comments & values” into Col Q



Same issue as you with editing time!!
My “Minimum” being “Optimum 1” : ALL the Comments/Notes from Col e into Col Q.

The BEST option is “Next Best”, values from Col E into Col P and also/then retrieve from Col F ”Comments & values” into Col Q .
My “Minimum” is “Optiom 1” : ALL the Comments/Notes from Col E into Col Q.

The BEST option is option 2 (“Next Best”); values from Col D into Col P and also/retrieve from Col E ”Comments/Notes & values” into Col Q

"retrieve" means everything, 'Values and Comments/Notes' from E to Q.

Just had a vague thought that "Comments/Notes" are classed as 'Formatting'??

This post is bcoming a right royal pain in the a****, glichie mouse and intermitant internat!!!

Will leave it with you.
Juhls


----------



## julhs (Dec 27, 2022)

This is is hopless!!!
Any edit i make is just useless!!
To a degree; be vary of Col ref's that I have posted to date!

I will double check things in the morning, hoping for better Internet connection


----------



## julhs (Dec 29, 2022)

Posting a revised Xl2bb in the hope that;
    a)   It is simplified
    b)   Reflects the change of direction we need to take from my initial post #1

So essentially what we are NOW looking to do is compile a list by:-
1)  Look in Col F for cells with “Comments/Notes”
      (I’m using Excel 2010, so the old “Comments”, xl2b didn’t transfer them so resorted to using luminous green fill colour to indicate cells that are supposed to contain “Comments”)
2)  IF there is a “Comment” in any cell in Col F; the corresponding Details + Formatting in Col E, the Value + Formatting + ”Comment” in Col F are copied over to Col P & Q respectfully

That will give me a list in Col P & Q to work from; I could then potentially apply a filter to it !!??

I have yet to decide as to whether to run the code via a command button on demand, or incorporate it into the Sheets main code. My thinking is the former as I won’t utilise the data in Col P & Q until ALL entries have been made in the INPUT SECTION (upwards of 700 rows)
Accounts 2016 - 2019 Final Currant.xlsmBCDEFGHIJKLMNOPQ3DEBITS4DateInvoice #Payment Method ListPayment Details ListBank & CashRentPowerMotoring Expenses Stock & MaterialsOffice56Jul-05gs0370Direct DebitRent250.00250.00INPUT SECTION All details etc on left are Enterd via a USERFORM, including the comments. It finds the next empty Row & pastes in Userform values, then inserts a new blank row and formats itMicks Garage50.007Jul-10gs0375Direct DebitEDF35.0035.00Halfords20.008Jul-15gs0380Debit CardMicks Garage50.0050.00Rent275.009Jul-20gs0385Debit CardHalfords20.0020.00EDF50.0010Jan-21gs0390Debit CardSouthern Building Supplies31.0031.0011Jul-22gs0415Debit CardAmazon42.0042.0012Jul-24gs0433PayPalDirect Stationary Supplies10.0010.0013Aug-05gs0450Direct DebitRent275.00275.0014Aug-10gs0460Debit CardWessex Steel40.0040.0015Aug-10gs0462Direct DebitEDF50.0050.0016Testing (3)


----------



## Micron (Jan 4, 2023)

Based on your posted data sample.
This should find the last row with a comment in column F then loop from row 6 to that point and copy those comments as well as data from F
 and E columns for those rows. Then it should paste the data and comments into P and Q columns beginning at row 6. Hopefully, it is compatible with the older versions of Excel where what you want was characterized as "comments". Change your sheet name accordingly.

I don't know how you actually intend to use this so it may need some tweaking. F'rinstance, perhaps PQ should be cleared because if you run this and there are 6 notes copied, then run again and there are only 5, you'll likely end up with 6 because one will remain from a prior operation. In that case, add this bold line at this location:

Set sht = ThisWorkbook.Sheets("005")
*sht.Range("P:Q").Clear*


```
Sub juhls2()
Dim Lrow As Long, DestRow As Long, i As Integer
Dim rng As Range
Dim sht As Worksheet

Set sht = ThisWorkbook.Sheets("005")
Lrow = sht.Cells(Rows.count, "F").End(xlUp).Row
DestRow = 6

For i = 6 To Lrow
     Set rng = Range("F" & i)
     If Not rng.Comment Is Nothing Then
         sht.Range("E" & i & ":F" & i).Copy
         sht.Range("P" & DestRow & ":Q" & DestRow).PasteSpecial xlPasteAll
         DestRow = DestRow + 1
     End If
Next

Application.CutCopyMode = False
sht.Range("P" & DestRow + 1).Offset(1, 0).Select

End Sub
```


----------



## Micron (Jan 6, 2023)

Revised to clear PQ range and restrict copying of comments to the row containing the Totals.

```
Sub juhls2()
Dim Lrow As Long, DestRow As Long, i As Integer
Dim rng As Range
Dim sht As Worksheet

Set sht = ThisWorkbook.Sheets("005")
sht.Range("P:Q").Clear

Set rng = sht.Range("D:D").Find(What:="Totals", LookIn:=xlValues, LookAt:=xlWhole)
Lrow = rng.Row
DestRow = 6

For i = 6 To Lrow
     Set rng = Range("F" & i)
     If Not rng.Comment Is Nothing Then
          sht.Range("E" & i & ":F" & i).Copy
          sht.Range("P" & DestRow & ":Q" & DestRow).PasteSpecial xlPasteAll
          DestRow = DestRow + 1
     End If
Next

Application.CutCopyMode = False
sht.Range("P" & DestRow + 1).Offset(1, 0).Select

End Sub
```


----------



## julhs (Jan 6, 2023)

Micron, many thanks!!!!!!!

That entirely deals with the required change in tack that was required; it gets the results that I can use to get to my ultimate goal.
Your help is GREATLY  appreciated!!!!

For those that read this later:
This is a work around to the inability of formulas and vLookup to return anything other than values, also to a limited extent the filter function. (Yes a filter function will return the comments but only for the single filter criteria, so you need to keep re-filtering).
This will return a complete contiguous list (to Col P & Q ) of *All* cells in Col F  that contain a “Comment” (as they appeared in the source cell), along with the corresponding values of Col E.
The line;

```
Set rng = sht.Range("D:D").Find(What:="Totals", LookIn:=xlValues, LookAt:=xlWhole)
```
is a range limiter for the search. By doing it this way, it doesn’t matter how many rows there are between Row 6 and the “Totals” row (so you can insert as many rows as you like between row 6 & “Totals” row, but still get a complete list regardless of how many rows there are.

 I’m more than expecting to get corrected/shot down on my statements but I am prepared to make then.


----------



## Micron (Jan 6, 2023)

Hope it continues to work for a long time!
You're welcome.


----------

