Hello all,

I have a problem he

For each cell in my range I would like to look for this words:
Some value has the word EUR and others USD.

If (Intstr = Application.WorksheetFunction.Search("USD",
MyCell.Value)) Then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, Intstr
- 1)

ElseIf (Intstr = Application.WorksheetFunction.Search("EUR",
MyCell.Value)) Then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, Intstr
- 1)

End If

I have an error here because it executes the two if and the same time.


Thanks,

Ina

ilocU = Instr(1,MyCell.Value,"USD",vbTextcompare)
ilocE = Instr(1,MyCell.Value,"EUR",vbTextcompare)
if ilocU 0 then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, IlocU - 1)
elseif iLocE 0 then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, iLocE - 1)
End If

--
Regards,
Tom Ogilvy


"ina" wrote:

Hello all,

I have a problem he

For each cell in my range I would like to look for this words:
Some value has the word EUR and others USD.

If (Intstr = Application.WorksheetFunction.Search("USD",
MyCell.Value)) Then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, Intstr
- 1)

ElseIf (Intstr = Application.WorksheetFunction.Search("EUR",
MyCell.Value)) Then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, Intstr
- 1)

End If

I have an error here because it executes the two if and the same time.


Thanks,

Ina

Thanks you a lot.

Could you explain me why ilocU and ilocE. I am newbie in VBA :)

Ina

any variable would do. I use iloc out of habit. i for integer (although I
would probably dim them as Long), Loc for location (location in the string)

the U was for USD
the E was for EUR

In summary, no special significance for ilocE or iLocU

If you mean what do they do, then the Instr function returns the location in
the string where the substring is found. If not found, it returns zero. So
ilocU and ilocE just store the result of instr.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"ina" wrote:

Thanks you a lot.

Could you explain me why ilocU and ilocE. I am newbie in VBA :)

Ina

Why are you using Instr, a VBA keyword as a variable name?

Try:

If 0< Instr(MyCell.Value,"USD") then ActiveCell.Offset(0, 2).Value =
Mid(MyCell.Value, 1, Intstr(MyCell.Value,"USD")- 1)

If 0< Instr(MyCell.Value,"EUR") then ActiveCell.Offset(0, 2).Value =
Mid(MyCell.Value, 1, Intstr(MyCell.Value,"EUR")- 1)

i.e. 2 completely separate/independent statements.

"ina" wrote:

Hello all,

I have a problem he

For each cell in my range I would like to look for this words:
Some value has the word EUR and others USD.

If (Intstr = Application.WorksheetFunction.Search("USD",
MyCell.Value)) Then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, Intstr
- 1)

ElseIf (Intstr = Application.WorksheetFunction.Search("EUR",
MyCell.Value)) Then
ActiveCell.Offset(0, 2).Value = Mid(MyCell.Value, 1, Intstr
- 1)

End If

I have an error here because it executes the two if and the same time.


Thanks,

Ina