Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize and a question
If anybody could help with this - I would so much appreciate it.
I import a csv file and the negative numbers have a trailing negative sign. My process below will take the trailing negative sign and put it where it should be - in front. My problem is that the code below is only for the 10th column. I know that I could write code that would repeat for the columns starting with column 4 but it seems to me that a resize should work. Apparently I have completely lost my capacity to use "resize". Also - I think that I use the "myRange.Rows(i).cells.activate" and then the replace part of the code must be causing me problems. Please - I just don't get it. As always - any help would be much appreciated. Thanks in advance! Anita Dim dateInRow As Boolean Application.ScreenUpdating = False With ActiveSheet Set myRange = .Cells(2, 10).Resize(.Cells(Rows.Count, _ 1).End(xlUp).Row, 10) For i = 1 To myRange.Rows.Count For Each cell In myRange.Rows(i).Cells If Right(Trim(cell.Value), 1) = "-" Then myRange.Rows(i).Cells.Activate ActiveCell.Replace What:="-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate myRange.Rows(i).Cells.Activate ActiveCell.Value = "-" & ActiveCell.Value Exit For End If Next cell If Not dateInRow Then myRange.Rows(i).Hidden = False Next i End With Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize and a question
Hi a, Sorry not sure exactry what you would like to do, but regarding RESIZE please try this sample code. Code: -------------------- Sub ResizeTest() Dim i As Long, j As Long For i = 1 To 10 For j = 1 To 10 Cells(1, 1).Resize(i, j).Select MsgBox Cells(1, 1).Resize(i, j).Address(0, 0) & _ ": The code is Cells(1, 1).Resize(" & i & "," & j & ").Address(0, 0)" Next j, i End Sub -------------------- And your code can be written something like this. Code: -------------------- Sub Test() Dim myRange As Range, c As Range Dim preRow As Long Dim dateInRow As Boolean Application.ScreenUpdating = False With ActiveSheet Set myRange = .Cells(2, 4).Resize(.Cells(Rows.Count, 1).End(xlUp).Row, 7) End With myRange.Select: Stop 'Don't need this line, it's just for your confirmation. On Error GoTo Terminate For Each c In myRange.SpecialCells(xlCellTypeConstants, xlTextValues) c.Value = Val(c.Value) * -1 If preRow < c.Row Then If Not dateInRow Then c.EntireRow.Hidden = False preRow = c.Row Next Terminate: Set myRange = Nothing Application.ScreenUpdating = True End Sub -------------------- :D ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize and a question
Dim myRange as Range, Cell as Range
set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) set myRange = myRange.Offset(0,-8).Resize(,9) for each cell in myRange.SpecialCells(xlConstants,xlTextValues) if right(cell,1)="-" then cell.Value = cdbl(cell.value) end if Next to demonstrate from the immediate window: set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) ? myRange.Address $J$2:$J$11 ? myRange.Offset(0,-6).Resize(,7).Address $D$2:$J$11 so it processes column 4 to column 10 -- Regards, Tom Ogilvy "a" wrote in message link.net... If anybody could help with this - I would so much appreciate it. I import a csv file and the negative numbers have a trailing negative sign. My process below will take the trailing negative sign and put it where it should be - in front. My problem is that the code below is only for the 10th column. I know that I could write code that would repeat for the columns starting with column 4 but it seems to me that a resize should work. Apparently I have completely lost my capacity to use "resize". Also - I think that I use the "myRange.Rows(i).cells.activate" and then the replace part of the code must be causing me problems. Please - I just don't get it. As always - any help would be much appreciated. Thanks in advance! Anita Dim dateInRow As Boolean Application.ScreenUpdating = False With ActiveSheet Set myRange = .Cells(2, 10).Resize(.Cells(Rows.Count, _ 1).End(xlUp).Row, 10) For i = 1 To myRange.Rows.Count For Each cell In myRange.Rows(i).Cells If Right(Trim(cell.Value), 1) = "-" Then myRange.Rows(i).Cells.Activate ActiveCell.Replace What:="-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate myRange.Rows(i).Cells.Activate ActiveCell.Value = "-" & ActiveCell.Value Exit For End If Next cell If Not dateInRow Then myRange.Rows(i).Hidden = False Next i End With Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize and a question
Thanks Tom Ogilvy,
Unfortunately - this doesn't work because the csv file has headers in between the different reports and the "cdbl" doesn't seem to like that. By the way - why does the function cdbl - which I think means "Change to a double" work at changing the value with the trailing "-" to a negative? As always - thanks for all of your help. The resize part works great and so now I just need to tinker with your code to get something that will work with my file. Thanks again, Anita Tom Ogilvy wrote: Dim myRange as Range, Cell as Range set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) set myRange = myRange.Offset(0,-8).Resize(,9) for each cell in myRange.SpecialCells(xlConstants,xlTextValues) if right(cell,1)="-" then cell.Value = cdbl(cell.value) end if Next to demonstrate from the immediate window: set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) ? myRange.Address $J$2:$J$11 ? myRange.Offset(0,-6).Resize(,7).Address $D$2:$J$11 so it processes column 4 to column 10 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revisit Resize with Cdbl question
Thanks Tom Ogilvy, Unfortunately - this doesn't work because the csv file has headers in between the different reports and the "cdbl" doesn't seem to like that. By the way - why does the function cdbl - which I think means "Change to a double" work at changing the value with the trailing "-" to a negative? As always - thanks for all of your help. The resize part works great and so now I just need to tinker with your code to get something that will work with my file. Thanks again, Anita Tom Ogilvy wrote: Dim myRange as Range, Cell as Range set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) set myRange = myRange.Offset(0,-8).Resize(,9) for each cell in myRange.SpecialCells(xlConstants,xlTextValues) if right(cell,1)="-" then cell.Value = cdbl(cell.value) end if Next to demonstrate from the immediate window: set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) ? myRange.Address $J$2:$J$11 ? myRange.Offset(0,-6).Resize(,7).Address $D$2:$J$11 so it processes column 4 to column 10 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize and a question
The check
if right(cell,1)="-" then should eliminate any problems with headers - cdbl then only looks at cells containing a "-" as the rightmost character. cdbl converts this a negative number ? cdbl("231-") -231 if you having problems, add an isnumeric test ? isnumeric("231-") True Dim myRange as Range, Cell as Range set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) set myRange = myRange.Offset(0,-8).Resize(,9) for each cell in myRange.SpecialCells(xlConstants,xlTextValues) if right(cell,1)="-" then if isnumeric(cell) then cell.Value = cdbl(cell.value) end if end if Next -- Regards, Tom Ogilvy "a" wrote in message hlink.net... Thanks Tom Ogilvy, Unfortunately - this doesn't work because the csv file has headers in between the different reports and the "cdbl" doesn't seem to like that. By the way - why does the function cdbl - which I think means "Change to a double" work at changing the value with the trailing "-" to a negative? As always - thanks for all of your help. The resize part works great and so now I just need to tinker with your code to get something that will work with my file. Thanks again, Anita Tom Ogilvy wrote: Dim myRange as Range, Cell as Range set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) set myRange = myRange.Offset(0,-8).Resize(,9) for each cell in myRange.SpecialCells(xlConstants,xlTextValues) if right(cell,1)="-" then cell.Value = cdbl(cell.value) end if Next to demonstrate from the immediate window: set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) ? myRange.Address $J$2:$J$11 ? myRange.Offset(0,-6).Resize(,7).Address $D$2:$J$11 so it processes column 4 to column 10 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks Tom Ogilvy!
Thanks Tom Ogilvy!
I haven't tried this but the "isnumeric" check I think will do the trick. The if right(cell,1) = "-" didn't help because the headers have dashes and so it would see the dash and then try to change it to double. Thanks again! Anita Tom Ogilvy wrote: The check if right(cell,1)="-" then should eliminate any problems with headers - cdbl then only looks at cells containing a "-" as the rightmost character. cdbl converts this a negative number ? cdbl("231-") -231 if you having problems, add an isnumeric test ? isnumeric("231-") True Dim myRange as Range, Cell as Range set myRange = Range(Cells(2,10),cells(rows.count,10).End(xlup)) set myRange = myRange.Offset(0,-8).Resize(,9) for each cell in myRange.SpecialCells(xlConstants,xlTextValues) if right(cell,1)="-" then if isnumeric(cell) then cell.Value = cdbl(cell.value) end if end if Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I could NOT resize the axis title but excel allows me to resize gr | Charts and Charting in Excel | |||
I could NOT resize the axis title but excel allows me to resize gr | Charts and Charting in Excel | |||
Resize a cell | Excel Discussion (Misc queries) | |||
Resize name box | Setting up and Configuration of Excel | |||
Need some help with Resize. Thanks | Excel Programming |