Watch the wrap
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim arr() As Variant
Dim LastPlace As Long
Dim i As Long
On Error GoTo eh
Application.EnableEvents = False
With Target
LastPlace = 1
For i = 1 To Len(.Value)
If Not (IsError(Application.Match(Mid$(.Value, i, 1), _
Array("+", "-", "*", "/", "^", "<>"), 0))) Then
If i > 1 Then
If IsNumeric(Mid$( _
.Value, LastPlace, i - LastPlace)) Then
LastPlace = i + 1
Else
Exit For
End If
End If
End If
Next i
If i > Len(.Value) Then
If IsNumeric(Mid$(.Value, LastPlace, i - LastPlace)) Then
.Formula = "=" & .Value
End If
End If
End With
eh:
Application.EnableEvents = True
End Sub
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Bob Phillips" <(E-Mail Removed)> wrote in message
news:eX%(E-Mail Removed)...
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
> Range)
> Dim arr() As Variant
> Dim LastPlace As Long
> Dim i As Long
>
> On Error GoTo eh
> Application.EnableEvents = False
>
> With Target
>
> LastPlace = 1
>
> For i = 1 To Len(.Value)
>
> If Not (IsError(Application.Match(Mid$(.Value, i, 1),
> Array("+", "-", "*", "/", "^", "<>"), 0))) Then
>
> If i > 1 Then
>
> If IsNumeric(Mid$(.Value, LastPlace, i - LastPlace))
> Then
>
> LastPlace = i + 1
> Else
>
> Exit For
> End If
> End If
> End If
> Next i
>
> If i > Len(.Value) Then
>
> If IsNumeric(Mid$(.Value, LastPlace, i - LastPlace)) Then
>
> .Formula = "=" & .Value
> End If
> End If
> End With
> eh:
> Application.EnableEvents = True
> End Sub
>
>
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
>
>
> "filo666" <(E-Mail Removed)> wrote in message
> news:13A1D507-BD88-4791-80B2-(E-Mail Removed)...
>>I have this code:
>>
>> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
>> Range)
>> Dim arr() As Variant
>> On Error GoTo eh
>> Application.EnableEvents = False
>> For cnt1 = 1 To target.value.words.count
>> arr(cnt1) = Target.Value.words(cnt1)
>> If IsNumeric(arr(cnt1)) Or arr(cnt1) <> "+" Or arr(cnt1) <> "-" Or
>> arr(cnt1)
>> <> "*" Or arr(cnt1) <> "/" Or arr(cnt1) <> "^" Or arr(cnt1) <> "." Then
>> GoTo eh
>> End If
>> wrdd = wrdd + arr(cnt1)
>> Next
>> Target.Value = "=" & wrdd
>> eh:
>> Application.EnableEvents = True
>> End Sub
>>
>> what I want to do is if the user types 3+5 then excell will change 3+5 to
>> =3+5 and will solve the operation; the problem us that
>> target.value.words.count
>> and Target.Value.words(cnt1) dont exist in excel, any suggestion
>> thanks
>>
>
>
|
Similar Threads
