VBA MoveStartUntil cSet query

Shelley Lou · #1 02-28-2024, 02:55 AM

I have a query regarding the use of cSet. This is just a line from a bigger macro. This line looks for a character before a space (a series of case words) . Can a cSet look for more than one thing at a time - I've been trying to add to look before a space or a non breaking space - I've added the usual ^s or Chr(160) after the space, same within square brackets etc., a line of code on its own but nothing seems to be working. Where am I going wrong?

Code:

Rng.MoveStartUntil cSet:=" ", count:=-10

vivka · #2 02-28-2024, 07:00 AM

Both lines will work:

Code:

Rng.MoveStartUntil cSet:=" " & Chr(160), count:=-10

and

Code:

Rng.MoveStartUntil cSet:=Chr(32) & Chr(160), count:=-10

To see the result, use

Code:

Rng.Select

or

Code:

msgbox Rng

Shelley Lou · #3 02-28-2024, 07:57 AM

Vivka, you are a superstar, my mistake was trying to put Chr(160) within the quotes.

Within the same code I am working on, if the code below looks for the last character at the end of the paragraph and then inserts something after it, what do I need to change with the line below to look for the character before the last character at the end of a paragraph. I've tried adding ,-1 but that is not working.

Code:

.Words.Last.Previous.Previous.Words(1)

vivka · #4 02-28-2024, 08:08 AM

Shelley Lou, modify either of the following lines to meet your needs ("!" is for testing purposes):

Code:

selection.Paragraphs(1).range.Characters.Last.Previous.InsertAfter "!"

or

Code:

selection.Paragraphs(1).range.Characters.Last.InsertBefore "!"

Shelley Lou · #5 02-28-2024, 08:53 AM

Thanks Vivka, that actually fixes something in another code I'm working on but won't work for what I need in this one, I only want it to look for the character before the last character in a paragraph not insert anything as that comes later in the code. I'm thinking I need to insert something like count:=-1 or something like that.

Code:

If sLastWord = ")" Then sLastWord = .Words.Last.Previous.Previous.Words(1) 'Looks for punc after a bracket at end of para
If sLastWord = "]" Then sLastWord = THIS IS WHERE I'M STRUGGLING 'Looks for punc before a closing square bracket at end of para
Debug.Print sFirstChar, sLastWord
If Not sLastWord Like "*[.!?:;,]" Then 'If para ends with any of these characters do nothing

vivka · #6 02-28-2024, 10:37 AM

Shelley Lou, If I correctly understand your aims, I'd use characters, not words because of the MS Office's unusual idea of a word.

Since I have no opportunity to test the following, try experimenting with the code:

Code:

If sLastWord = "]" Then msgbox sLastWord.range.characters.first.previous

Shelley Lou · #7 02-29-2024, 12:53 AM

Hi Vivka, I will certainly play around with that line of code so many thanks. I do have another post I need to solve where this query came from originally called Adding Punctuation to End of Paragraphs which has the full code within it. I'm just having issues trying to get the code to look before the square bracket to see if there is any punctuation, if there is move on to next, if not add punctuation. Many thanks for your help on this query though, much appreciated.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IFS query (I think)	ronsar	Excel	1	12-06-2018 09:04 AM
Conditional query ...	LearnerExcel	Excel	5	02-06-2018 01:13 AM
Query and Power Query not working Excel 2016	bl10	Excel	0	07-22-2016 06:25 PM
Baseline query	joday48	Project	6	01-25-2013 09:50 AM
How to Query Text in MS-Excel 2010 or Import Data & Query outside of Excel?	mag	Excel	0	10-18-2012 11:15 AM