Copy “Comments” using VBA, with a twist.

[julhs]

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.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		CASH BOOK
3		DEBITS
4		Date	Invoice #	Payment Method List	Payment Details List	Bank & Cash	Rent	Power	Motoring Expenses	Stock & Materials	Office
5
6		Jul-05	gs0370	Direct Debit	Rent	250.00	250.00						INPUT 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 it
7		Jul-10	gs0375	Direct Debit	EDF	35.00		35.00
8		Jul-15	gs0380	Debit Card	Micks Garage	50.00			50.00
9		Jul-20	gs0385	Debit Card	Halfords	20.00			20.00
10		Jan-21	gs0390	Debit Card	Southern Building Supplies	31.00				31.00
11		Jul-22	gs0415	Debit Card	Amazon	42.00					42.00
12		Jul-24	gs0433	PayPal	Direct Stationary Supplies	10.00					10.00
13		Aug-05	gs0450	Direct Debit	Rent	275.00	275.00
14		Aug-10	gs0460	Debit Card	Wessex Steel	40.00				40.00
15		Aug-10	gs0462	Direct Debit	EDF	50.00		50.00
16
17					Totals	803.00	525.00	85.00	70.00	71.00	52.00
18
19
20				ANALYSIS SECTION			Rent	EDF	Micks Garage	Halfords	Southern Building Supplies	Wessex Steel	Direct Stationary Supplies	Amazon
21				This section lists all the entries for individual supplier using SUMPRODUCT,ROWS,INDEX,SMALL. It does get bumped down as rows are added above			250.00	35.00	50.00	20.00	31.00	40.00	10.00	42.00
22							275.00	50.00
23
24
25				This is what I would like the desired outcome to be			Rent	EDF	Micks Garage	Halfords	Southern Building Supplies	Wessex Steel	Direct Stationary Supplies	Amazon
26							250.00	35.00	50.00	20.00	31.00	40.00	10.00	42.00
27							275.00	50.00
Testing (2)

Cell Formulas
Range	Formula
F17:K17	F17	=SUM(F6:F15)
G21:N22	G21	=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]

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

 

[Micron]

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]

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]

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]

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]

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.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	DEBITS
4	Date	Invoice #	Payment Method List	Payment Details List	Bank & Cash	Rent	Power	Motoring Expenses	Stock & Materials	Office
5
6	Jul-05	gs0370	Direct Debit	Rent	250.00	250.00						INPUT 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 it			Micks Garage	50.00
7	Jul-10	gs0375	Direct Debit	EDF	35.00		35.00								Halfords	20.00
8	Jul-15	gs0380	Debit Card	Micks Garage	50.00			50.00							Rent	275.00
9	Jul-20	gs0385	Debit Card	Halfords	20.00			20.00							EDF	50.00
10	Jan-21	gs0390	Debit Card	Southern Building Supplies	31.00				31.00
11	Jul-22	gs0415	Debit Card	Amazon	42.00					42.00
12	Jul-24	gs0433	PayPal	Direct Stationary Supplies	10.00					10.00
13	Aug-05	gs0450	Direct Debit	Rent	275.00	275.00
14	Aug-10	gs0460	Debit Card	Wessex Steel	40.00				40.00
15	Aug-10	gs0462	Direct Debit	EDF	50.00		50.00
16
Testing (3)

 

[Micron]

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]

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]

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]

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