Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No sure why this isn't working
Here is the code that I used in a sheet to move cell contents from one row to
another. I'm using in a another sheet and it isn't working....it is moving the entire row to the new column, not just the cells with greater than 7 characters. Any ideas? Thanks Set rng = Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp)) For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value Next cell.ClearContents End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No sure why this isn't working
Is it possible that you need your code to look like this:
Set rng = Range("A1:A10") For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value cell.ClearContents End If Next "SITCFanTN" wrote: Here is the code that I used in a sheet to move cell contents from one row to another. I'm using in a another sheet and it isn't working....it is moving the entire row to the new column, not just the cells with greater than 7 characters. Any ideas? Thanks Set rng = Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp)) For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value Next cell.ClearContents End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
No sure why this isn't working
Whoops sorry, ignore the Range("A1:A10"), I just put that in there to test
"SITCFanTN" wrote: Here is the code that I used in a sheet to move cell contents from one row to another. I'm using in a another sheet and it isn't working....it is moving the entire row to the new column, not just the cells with greater than 7 characters. Any ideas? Thanks Set rng = Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp)) For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value Next cell.ClearContents End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
No sure why this isn't working
Hi Paul,
My range length is not definate. The report can be 50 rows or 5000 rows. How would I code that? Just ("A:A") "Paul Mathews" wrote: Is it possible that you need your code to look like this: Set rng = Range("A1:A10") For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value cell.ClearContents End If Next "SITCFanTN" wrote: Here is the code that I used in a sheet to move cell contents from one row to another. I'm using in a another sheet and it isn't working....it is moving the entire row to the new column, not just the cells with greater than 7 characters. Any ideas? Thanks Set rng = Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp)) For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value Next cell.ClearContents End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
No sure why this isn't working
Hi again, one approach would be to define a data count variable and use the
CountA worksheet function to figure out how many non-blank items are in the column, e.g., to figure out how many non-blank items are listed in column A: Dim i as Integer, DataCount as Integer DataCount = Application.WorksheetFunction.CountA(Columns(1)) For i = 1 to DataCount Blah Blah Next i The only caveat here is that you'll need to get rid of any embedded blank items in the list (you need a continuous list of non-blank items). "SITCFanTN" wrote: Hi Paul, My range length is not definate. The report can be 50 rows or 5000 rows. How would I code that? Just ("A:A") "Paul Mathews" wrote: Is it possible that you need your code to look like this: Set rng = Range("A1:A10") For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value cell.ClearContents End If Next "SITCFanTN" wrote: Here is the code that I used in a sheet to move cell contents from one row to another. I'm using in a another sheet and it isn't working....it is moving the entire row to the new column, not just the cells with greater than 7 characters. Any ideas? Thanks Set rng = Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp)) For Each cell In rng If Len(cell.Value) 7 Then cell.Offset(0, 1).Value = cell.Value Next cell.ClearContents End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
Making weekend days working days - the system cuts the working tim | Excel Discussion (Misc queries) | |||
macro was working, now it's not working | Excel Programming | |||
Macro working in Excel 2003; not working in Excel 2000 | Excel Programming | |||
Adding sales from a non working day to the previous working day | Excel Programming |