the entries A1 to A5 are
55 44 32
market value
11 22 33
33 44 55
r venkat


I created a sub like this


Public Sub TEST()
Dim i, j As Long
Dim v
Dim cell, myrange As Range
Set myrange = Range(Range("a1"), Range("a1").End(xlDown))
For Each cell In myrange
If IsNumeric(Left(cell, 1)) Then
i = WorksheetFunction.Search(" ", cell, 1)
j = WorksheetFunction.Search(" ", cell, i + 1)
v = Mid(cell, 1, i - 1) & Mid(cell, i + 1, j - i - 1) & Mid(cell, j + 1,
Len(cell) - j)
msgbox v
cell = v 'the cell value does not change
End If
Next cell
End Sub

but the cell value in A1, A3,A4 do not change why?
msgbox v gives correct value viz 554432
even ?cell in the immediate window gives 554432 but in A1 (and in A3 and
A4) the value does not change

what is the mistake I am doing?

excel 2002 (XP)

when you separate dims with a comma they do not all get dim as whatever
you specify. they each need to be set separatly like this:
Dim cell as Range, myrange as Range

HTH

Die_Another_Day

R..VENKATARAMAN wrote:
the entries A1 to A5 are
55 44 32
market value
11 22 33
33 44 55
r venkat


I created a sub like this


Public Sub TEST()
Dim i, j As Long
Dim v
Dim cell, myrange As Range
Set myrange = Range(Range("a1"), Range("a1").End(xlDown))
For Each cell In myrange
If IsNumeric(Left(cell, 1)) Then
i = WorksheetFunction.Search(" ", cell, 1)
j = WorksheetFunction.Search(" ", cell, i + 1)
v = Mid(cell, 1, i - 1) & Mid(cell, i + 1, j - i - 1) & Mid(cell, j + 1,
Len(cell) - j)
msgbox v
cell = v 'the cell value does not change
End If
Next cell
End Sub

but the cell value in A1, A3,A4 do not change why?
msgbox v gives correct value viz 554432
even ?cell in the immediate window gives 554432 but in A1 (and in A3 and
A4) the value does not change

what is the mistake I am doing?

excel 2002 (XP)

i think my mistak is c=v
it should be
c.value=v


"Die_Another_Day" wrote in message
oups.com...
when you separate dims with a comma they do not all get dim as whatever
you specify. they each need to be set separatly like this:
Dim cell as Range, myrange as Range

HTH

Die_Another_Day

R..VENKATARAMAN wrote:
the entries A1 to A5 are
55 44 32
market value
11 22 33
33 44 55
r venkat


I created a sub like this


Public Sub TEST()
Dim i, j As Long
Dim v
Dim cell, myrange As Range
Set myrange = Range(Range("a1"), Range("a1").End(xlDown))
For Each cell In myrange
If IsNumeric(Left(cell, 1)) Then
i = WorksheetFunction.Search(" ", cell, 1)
j = WorksheetFunction.Search(" ", cell, i + 1)
v = Mid(cell, 1, i - 1) & Mid(cell, i + 1, j - i - 1) & Mid(cell, j + 1,
Len(cell) - j)
msgbox v
cell = v 'the cell value does not change
End If
Next cell
End Sub

but the cell value in A1, A3,A4 do not change why?
msgbox v gives correct value viz 554432
even ?cell in the immediate window gives 554432 but in A1 (and in A3 and
A4) the value does not change

what is the mistake I am doing?

excel 2002 (XP)