Constant Expression Required

[Steved]

Hello from Steved

A message box comes up with "Constant Expression Required".
It highlights What:=sFIND, _ sFIND,_

Also can I run this code as an Event please

Thankyou.
Sub test()
Const sFIND As String = "TOTAL SHIFT HOURS"
Dim vArr As Variant
Dim rFound As Range
Dim nCount As Long

vArr = Array
("City", "Roskill", "Papakura", "Wiri", "Shore", "Orewa", "
Swanson")
nCount = 0
Set rFound = Cells.Find( _
What:=sFIND, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
Do While Not rFound Is Nothing And nCount <= UBound
(vArr)
rFound.Value = vArr(nCount)
nCount = nCount + 1
Set rFound = Cells.FindNext(after:=rFound)
Loop
End Sub

 

[JE McGimpsey]

Did you paste this in? code works fine as written.

If typing it in, make sure you use the correct line continuation
characters " _" (i.e., space-underscore).

Which event do you want to run it on?

 

[Steved]

Hello JE from Steved

As you wrote it works as written I open a new workbook
and pasted it in and as you rightfully say it works, but
JE it does Swanson 1st but I've typed it last, is it
possible please to put Swanson Last.
Ok what I do is import a CSV file and " TOTAL SHIFT
HOURS" is 7 spaces in from Left margin, I am thinking this
is probable cause as to why it is not working.
2nd What I am saying is I value paste special into the
file with the above I would like it to replace "TOTAL
SHIFT HOURS" without having to use a macro button.

Thankyou for bearing with me on this.
Cheers

 

[Steved]

Hello JE from Steved

JE please ignore my second post. As i must off pasted it
in wrong as it now works, just as you set it out, but JE
can I run your macro without using a command button

Thankyou.

-----Original Message-----
Hello from Steved

A message box comes up with "Constant Expression Required".
It highlights What:=sFIND, _ sFIND,_

Also can I run this code as an Event please

Thankyou.
Sub test()
Const sFIND As String = "TOTAL SHIFT HOURS"
Dim vArr As Variant
Dim rFound As Range
Dim nCount As Long

vArr = Array

("City", "Roskill", "Papakura", "Wiri", "Shore", "Orewa", "

 

[JE McGimpsey]

First, spaces have nothing to do with anything. Since Find's LookAt
argument is xlPart, it finds the text regardless of how many leading
spaces.

Having said that, if you're pasting from a web site, it's possible that
some of what appear to be spaces are non-breaking space characters, i.e.
CHAR(160) rather than CHAR(32). David McRitchie's TrimALL macro will
remove extra spaces and nbsp's:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Next, the order can be fixed by changing

What:=sFIND, _
LookIn:=xlValues, _

to

What:=sFind, _
After:=Cells(Cells.Count), _
LookIn:=xlValues

Third, as for an event, I can't think of a good way to do this - Pasting
fires the Change event. I suppose you could check for "TOTAL SHIFT
HOURS" anywhere in the sheet, and if found, replace them:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.CountIf(Cells, "TOTAL SHIFT HOURS") Then
Application.EnableEvents = False
test
Application.EnableEvents = True
End If
End Sub

 

[Steved]

Thankyou JE

-----Original Message-----
First, spaces have nothing to do with anything. Since Find's LookAt
argument is xlPart, it finds the text regardless of how many leading
spaces.

Having said that, if you're pasting from a web site, it's possible that
some of what appear to be spaces are non-breaking space characters, i.e.
CHAR(160) rather than CHAR(32). David McRitchie's TrimALL macro will
remove extra spaces and nbsp's:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Next, the order can be fixed by changing

What:=sFIND, _
LookIn:=xlValues, _

to

What:=sFind, _
After:=Cells(Cells.Count), _
LookIn:=xlValues

Third, as for an event, I can't think of a good way to do this - Pasting
fires the Change event. I suppose you could check for "TOTAL SHIFT
HOURS" anywhere in the sheet, and if found, replace them:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.CountIf(Cells, "TOTAL SHIFT HOURS") Then
Application.EnableEvents = False
test
Application.EnableEvents = True
End If
End Sub





.

 

[JE McGimpsey]

My mistake: Since there *are* leading spaces, change

If Application.CountIf(Cells, "TOTAL SHIFT HOURS") Then

to

If Application.CountIf(Cells, "*TOTAL SHIFT HOURS*") Then

 

[Steved]

Hello JE thankyou for staying the distance on this one.
Below is what I have When the "TOTAL SHIFT HOURS" is that
it will find it seven times and put City, if I change the
line nCount = 0 to nCount = 1 it will type Roskill, 7
times, JE we are nearly there please put the below in a
worksheet and type TOTAL SHIFT HOURS 7 times and you will
see what is happening.

Once again thanks for your patience


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sFIND As String = "TOTAL SHIFT HOURS"
Dim vArr As Variant
Dim rFound As Range
Dim nCount As Long
vArr = Array
("City", "Roskill", "Papakura", "Wiri", "Shore", "Orewa", "
Swanson")
nCount = 0
Set rFound = Cells.Find( _
What:=sFIND, _
After:=Cells(Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
Do While Not rFound Is Nothing And nCount <= UBound
(vArr)
rFound.Value = vArr(nCount)
nCount = nCount + 1
Set rFound = Cells.FindNext(After:=rFound)
Loop
End Sub

-----Original Message-----
My mistake: Since there *are* leading spaces, change

If Application.CountIf(Cells, "TOTAL SHIFT HOURS") Then

to

If Application.CountIf(Cells, "*TOTAL SHIFT HOURS*") Then

 

[JE McGimpsey]

No need to try it - the problem isn't in the replacement part of the
macro itself.

Take a look at my Worksheet_Change() code - the

Application.EnableEvents = False
'Replacement code
Application.EnableEvents = True

wrapper is REQUIRED.

If events are not disabled, the first time "TOTAL SHIFT HOURS" is
replaced by "City", that replacement causes a second Worksheet_Change()
event to immediately fire, *before* the original event macro is
completed. That in turn will find the *next* "TOTAL SHIFT HOURS" and
replace it with "City", which causes a third Worksheet_Change() event to
fire...and so on until every instance of "TOTAL SHIFT HOURS" is replaced
by "City".

The last-called Worksheet_Change() macro then looks for more instances,
but there are none, so it finishes and the next-to-last
Worksheet_Change() event macro then continues, but of course it finds no
instances of "TOTAL SHIFT HOURS" to replace, so it ends, returning
control to the third-to-last Worksheet_Change() macro called, etc.,
etc., until the first-called Worksheet_Change() macro finishes and
returns control to XL.