Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with USEDRANGE.ROWS.COUNT reporting one row too many | Excel Programming | |||
Excel VBA - UsedRange problem | Excel Programming | |||
Problem with UsedRange.Rows.Count | Excel Programming | |||
UsedRange problem | Excel Programming | |||
UsedRange problem | Excel Programming |