usedRange problem
 

hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu

usedRange problem
 

I'm not sure, but is this what you want ?
Dim rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
rng.FormulaR1C1 = "Some New Text"

NickHK

P.S It would be better if you pasted your actual code rather than type out
what you think you have.


"Hemant_india" wrote in message
...
hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu

usedRange problem
 

Function nconvert(tsh) As String
If tsh < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
leftside = Left(tsh, Len(tsh) - Len(Right(tsh, 2)))
nconvert = CStr(leftside) & "." & CStr(Right(tsh, 2))

End If

End Function
======
Dim rng As Range
Set rng = ActiveSheet.UsedRange.Range("f:f")
For Each col In rng
col.Select
col.Value = nconvert(col.Text)
Next col

--
hemu


"NickHK" wrote:

I'm not sure, but is this what you want ?
Dim rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
rng.FormulaR1C1 = "Some New Text"

NickHK

P.S It would be better if you pasted your actual code rather than type out
what you think you have.


"Hemant_india" wrote in message
...
hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu

usedRange problem
 

Is that a question or a solution ?
You should Dim "col" so we know what it is.
What if col.text="" ?

NickHK

"Hemant_india" wrote in message
...
Function nconvert(tsh) As String
If tsh < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
leftside = Left(tsh, Len(tsh) - Len(Right(tsh, 2)))
nconvert = CStr(leftside) & "." & CStr(Right(tsh, 2))

End If

End Function
======
Dim rng As Range
Set rng = ActiveSheet.UsedRange.Range("f:f")
For Each col In rng
col.Select
col.Value = nconvert(col.Text)
Next col

--
hemu


"NickHK" wrote:

I'm not sure, but is this what you want ?
Dim rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
rng.FormulaR1C1 = "Some New Text"

NickHK

P.S It would be better if you pasted your actual code rather than type
out
what you think you have.


"Hemant_india" wrote in message
...
hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu

usedRange problem
 

sorry nick
cells in question contains amount imported from a sequential file
e.g 1234500----actual amt is 12345.00
actually i want to limit loop for usedRange only
--
hemu


"NickHK" wrote:

Is that a question or a solution ?
You should Dim "col" so we know what it is.
What if col.text="" ?

NickHK

"Hemant_india" wrote in message
...
Function nconvert(tsh) As String
If tsh < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
leftside = Left(tsh, Len(tsh) - Len(Right(tsh, 2)))
nconvert = CStr(leftside) & "." & CStr(Right(tsh, 2))

End If

End Function
======
Dim rng As Range
Set rng = ActiveSheet.UsedRange.Range("f:f")
For Each col In rng
col.Select
col.Value = nconvert(col.Text)
Next col

--
hemu


"NickHK" wrote:

I'm not sure, but is this what you want ?
Dim rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
rng.FormulaR1C1 = "Some New Text"

NickHK

P.S It would be better if you pasted your actual code rather than type
out
what you think you have.


"Hemant_india" wrote in message
...
hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu

usedRange problem
 

Dim rng As Range

Set rng = Intersect(ActiveSheet.UsedRange, Range("C:C"))
rng.FormulaR1C1 = rng.Text / 100

NickHK

"Hemant_india" wrote in message
...
sorry nick
cells in question contains amount imported from a sequential file
e.g 1234500----actual amt is 12345.00
actually i want to limit loop for usedRange only
--
hemu


"NickHK" wrote:

Is that a question or a solution ?
You should Dim "col" so we know what it is.
What if col.text="" ?

NickHK

"Hemant_india" wrote in message
...
Function nconvert(tsh) As String
If tsh < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
leftside = Left(tsh, Len(tsh) - Len(Right(tsh, 2)))
nconvert = CStr(leftside) & "." & CStr(Right(tsh, 2))

End If

End Function
======
Dim rng As Range
Set rng = ActiveSheet.UsedRange.Range("f:f")
For Each col In rng
col.Select
col.Value = nconvert(col.Text)
Next col

--
hemu


"NickHK" wrote:

I'm not sure, but is this what you want ?
Dim rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
rng.FormulaR1C1 = "Some New Text"

NickHK

P.S It would be better if you pasted your actual code rather than
type
out
what you think you have.


"Hemant_india" wrote in
message
...
hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu

usedRange problem
 

Try this

Dim rng As Range
with ActiveSheet.UsedRange.
For Each col In .range("F1:F" & .usedrange.rows.count - .usedrange.row+1)
col.Select
col.Value = nconvert(col.Text)
Next col
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Hemant_india" wrote in message
...
Function nconvert(tsh) As String
If tsh < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
leftside = Left(tsh, Len(tsh) - Len(Right(tsh, 2)))
nconvert = CStr(leftside) & "." & CStr(Right(tsh, 2))

End If

End Function
======
Dim rng As Range
Set rng = ActiveSheet.UsedRange.Range("f:f")
For Each col In rng
col.Select
col.Value = nconvert(col.Text)
Next col

--
hemu


"NickHK" wrote:

I'm not sure, but is this what you want ?
Dim rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
rng.FormulaR1C1 = "Some New Text"

NickHK

P.S It would be better if you pasted your actual code rather than type
out
what you think you have.


"Hemant_india" wrote in message
...
hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu

usedRange problem
 

ok..
it's very cool
i stupidly made it complicated
thank nic
thanks bob
--
hemu


"Hemant_india" wrote:

hi
i have following code
dim rng as range
dim rng1 as range
set rng=activesheet.usedrange
set rng1=rng.range("E:E")
for each col in rng1
col.text=somethiing
next
the problem is loop go through every cell in the range

help please
--
hemu