Resize and a question

  • Thread starter Thread starter a
  • Start date Start date
A

a

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
 
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
 
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
 
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
 
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
Click to expand...
 
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
 
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
 
Back
Top