VBA and Excell – really need you help

[Xaraam]

Hey!

I’m kind of dummie in VBA. I usually just work with it to create macros.
I’ve a important challenge now, that is: using an excel sheet with, average,
20000 cells, I need to replace a part of the cell content several times (i.e.
if my cell is 01-02-2006, the “02†has to be replaced by “MAâ€).
I’m using the VB6 version and excel 2003.

i've tried this code:
sub replace()
worksheets("book1").select
Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
ReplaceFormat:=False
end sub()

the cells are formatted to date.

i've just noticed that the code does work but sometimes, i.e. if i want to
replace the content "-02-" to "-MA-", in all my attempts it just worked once.
But if i replace "2006" to "-MA-", it work all the time and the replace
cells are something like this: "01/02/-MA-", from the previous: "01-02-2006".
Even if i replace "0" to MA on "01-02-2006" the result will be:
"1/2/2-MA--MA-6"

BUt even after all this test and puting the cells on the original values i
try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a
error
message.

Can you help me out here?..thanks in advance.

 

[Jim Rech]

The "-" parts of your "-02-" may just be due to the date formatting rather
than what is "really" in the cell. When you select such a cell what is in
the formula bar? Whatever is up there ("/2/" for me) is what you can find
and replace on.

--
Jim
| Hey!
|
| I'm kind of dummie in VBA. I usually just work with it to create macros.
| I've a important challenge now, that is: using an excel sheet with,
average,
| 20000 cells, I need to replace a part of the cell content several times
(i.e.
| if my cell is 01-02-2006, the "02" has to be replaced by "MA").
| I'm using the VB6 version and excel 2003.
|
| i've tried this code:
| sub replace()
| worksheets("book1").select
| Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
| ReplaceFormat:=False
| end sub()
|
| the cells are formatted to date.
|
| i've just noticed that the code does work but sometimes, i.e. if i want to
| replace the content "-02-" to "-MA-", in all my attempts it just worked
once.
| But if i replace "2006" to "-MA-", it work all the time and the replace
| cells are something like this: "01/02/-MA-", from the previous:
"01-02-2006".
| Even if i replace "0" to MA on "01-02-2006" the result will be:
| "1/2/2-MA--MA-6"
|
| BUt even after all this test and puting the cells on the original values i
| try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a
| error
| message.
|
| Can you help me out here?..thanks in advance.

 

[Xaraam]

In the formula bar is really "01-02-2006" exactly the same thing that in the
cell.
but also my intention is to substitute "-02-" including the "-" coz i have
other row that can have between the "02" numbers.

but even is /02/ was in the formula bar when i tried to replace "02" it
should replace, but it doesn't.

 

[TomPl]

Does your code really have brackets "( )" after the End Sub.
That looks like a problem to me.

Tom

 

[Rick Rothstein \(MVP - VB\)]

I think the problem you are having is those entries are true Excel dates,
not text. If that is the case, then what you see is not really what is in
the cell. You can see that easy enough by selecting one of the cells and
changing its format to General. If you had selected the cell with 01-02-2006
in it, you would now see 38719. That is because Excel stores the date
portion of a date as the number of days since 12/31/1899 (hence, 1 is
January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will
probably have to do something like this to accomplish what you want...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
End If
End If
Next
End Sub

Rick

 

[Jim Rech]

I think you're at least partially right, Rick. But if Excel is really
looking at the serial number in the cells why can you replace the year
portion of the date?

Anyway, by changing my regional settings date I reproduced the problem.
This is a variation for your macro that preserves leading zeros and is
regional settings agnostic:

Sub Replace02WithMA2()
Dim Cell As Range
Dim CellString As String
For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
CellString = Cell.Value
Cell.Value = Replace(CellString, "-02-", "-MD-")
Next
End Sub


--
Jim
message |I think the problem you are having is those entries are true Excel dates,
| not text. If that is the case, then what you see is not really what is in
| the cell. You can see that easy enough by selecting one of the cells and
| changing its format to General. If you had selected the cell with
01-02-2006
| in it, you would now see 38719. That is because Excel stores the date
| portion of a date as the number of days since 12/31/1899 (hence, 1 is
| January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will
| probably have to do something like this to accomplish what you want...
|
| Sub Replace02WithMA()
| Dim C As Range
| For Each C In Worksheets("Sheet2").UsedRange
| If IsDate(C.Value) Then
| If Day(C.Value) = 2 Then
| C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
| End If
| End If
| Next
| End Sub
|
| Rick
|
|
| | > Hey!
| >
| > I'm kind of dummie in VBA. I usually just work with it to create macros.
| > I've a important challenge now, that is: using an excel sheet with,
| > average,
| > 20000 cells, I need to replace a part of the cell content several times
| > (i.e.
| > if my cell is 01-02-2006, the "02" has to be replaced by "MA").
| > I'm using the VB6 version and excel 2003.
| >
| > i've tried this code:
| > sub replace()
| > worksheets("book1").select
| > Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
| > SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
| > ReplaceFormat:=False
| > end sub()
| >
| > the cells are formatted to date.
| >
| > i've just noticed that the code does work but sometimes, i.e. if i want
to
| > replace the content "-02-" to "-MA-", in all my attempts it just worked
| > once.
| > But if i replace "2006" to "-MA-", it work all the time and the replace
| > cells are something like this: "01/02/-MA-", from the previous:
| > "01-02-2006".
| > Even if i replace "0" to MA on "01-02-2006" the result will be:
| > "1/2/2-MA--MA-6"
| >
| > BUt even after all this test and puting the cells on the original values
i
| > try to do the "-02-" to "-MA-" replacement and nothing happens, nor even
a
| > error
| > message.
| >
| > Can you help me out here?..thanks in advance.
|

 

[Jim Rech]

Upon further review I believe this is the ole "Excel operates with US
settings when a macro is running" problem. The OP I believe is not using US
settings.

So his macro uses: Replace(CellString, "-02-", "-MD-")

And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006
with UK settings. Excel, being in US mode, sees this cell as having
2/8/2006 in it so there is no match.

But if you change the macro to Replace(CellString, "2/", "-MD-") Excel does
find that (the first 2 characters in the cell when viewed with US settings)
and changes the cell to -MA-8/2006. Not too helpful.

So, short of changing to US settings, I think the OP should use sub
Replace02WithMA2 I posted.

--
Jim
|I think you're at least partially right, Rick. But if Excel is really
| looking at the serial number in the cells why can you replace the year
| portion of the date?
|
| Anyway, by changing my regional settings date I reproduced the problem.
| This is a variation for your macro that preserves leading zeros and is
| regional settings agnostic:
|
| Sub Replace02WithMA2()
| Dim Cell As Range
| Dim CellString As String
| For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
| CellString = Cell.Value
| Cell.Value = Replace(CellString, "-02-", "-MD-")
| Next
| End Sub
|
|
| --
| Jim
| message ||I think the problem you are having is those entries are true Excel dates,
|| not text. If that is the case, then what you see is not really what is in
|| the cell. You can see that easy enough by selecting one of the cells and
|| changing its format to General. If you had selected the cell with
| 01-02-2006
|| in it, you would now see 38719. That is because Excel stores the date
|| portion of a date as the number of days since 12/31/1899 (hence, 1 is
|| January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will
|| probably have to do something like this to accomplish what you want...
||
|| Sub Replace02WithMA()
|| Dim C As Range
|| For Each C In Worksheets("Sheet2").UsedRange
|| If IsDate(C.Value) Then
|| If Day(C.Value) = 2 Then
|| C.Value = Month(C.Value) & "-MA-" & Year(C.Value)
|| End If
|| End If
|| Next
|| End Sub
||
|| Rick
||
||
|| || > Hey!
|| >
|| > I'm kind of dummie in VBA. I usually just work with it to create
macros.
|| > I've a important challenge now, that is: using an excel sheet with,
|| > average,
|| > 20000 cells, I need to replace a part of the cell content several times
|| > (i.e.
|| > if my cell is 01-02-2006, the "02" has to be replaced by "MA").
|| > I'm using the VB6 version and excel 2003.
|| >
|| > i've tried this code:
|| > sub replace()
|| > worksheets("book1").select
|| > Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _
|| > SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
|| > ReplaceFormat:=False
|| > end sub()
|| >
|| > the cells are formatted to date.
|| >
|| > i've just noticed that the code does work but sometimes, i.e. if i want
| to
|| > replace the content "-02-" to "-MA-", in all my attempts it just worked
|| > once.
|| > But if i replace "2006" to "-MA-", it work all the time and the replace
|| > cells are something like this: "01/02/-MA-", from the previous:
|| > "01-02-2006".
|| > Even if i replace "0" to MA on "01-02-2006" the result will be:
|| > "1/2/2-MA--MA-6"
|| >
|| > BUt even after all this test and puting the cells on the original
values
| i
|| > try to do the "-02-" to "-MA-" replacement and nothing happens, nor
even
| a
|| > error
|| > message.
|| >
|| > Can you help me out here?..thanks in advance.
||
|
|

 

[Rick Rothstein \(MVP - VB\)]

That sounds reasonable (thankfully, having worked in the US for my entire
working career, I never had to deal with international issues in any of my
programs... it seems like such a nightmare to me<g>).

As for my macro not having preserved leading zeroes (your original comment
to me), that can be easily fixed...

Sub Replace02WithMA()
Dim C As Range
For Each C In Worksheets("Sheet2").UsedRange
If IsDate(C.Value) Then
If Day(C.Value) = 2 Then
C.Value = Format(C.Value, "mm-\M\A-yyyy")
End If
End If
Next
End Sub

I'm not entirely sure which of our macros is physically replacing the
smallest number of cell values.

Rick

 

[Xaraam]

Hey Rick!!

it works perfectly!!..thank you a lot!!..the only change i'd to make was:

If IsDate(C.Value) Then
If Month(C.Value) = 2 Then
C.Value = Format(C.Value, "dd-\M\A-yyyy")

coz my format is : "dd-mm-yyyy". all my date is on that format, more used
here in europe.
The code also work without the "\" or even if like this:
C.Value = Format(C.Value, "dd-\MA\-yyyy")

Just clarify me something, what's the porpuse of "usedrange" and with do u
use "c.value" and not just "c"?

 

[Xaraam]

hey Jim!

your code works almost perfectly, probably once i wasn't clear about my date
format you wouldn't guess i was using the "dd-mm-yyyy" format.

the problem on your code is the with the exception of the cell that "mm"
corresponds to "02" it swaps the "dd" value for the "mm" value.
i-e. if the original code is: 09-04-2006, after running the code :
04-09-2008. however this happens only until the "dd" value reachs "12".

thank you very much, anyway, for your code and effort!!

 

[Rick Rothstein \(MVP - VB\)]

The UsedRange is an automatically tracked range by Excel and it covers the
maximum extent of cells ever put into use on the worksheet. Note I said
"ever put into use"... one would have hoped UsedRange covered the maximum
extent of cells currently being used but, alas, it doesn't. If you used a
cell outside of your current ranged of used cells and later on deleted it,
UsedRange still thinks that cell is in use... UsedRange is not supposed to
reset itself smaller. Now I am sure I seen it do that (set itself smaller,
but I have also seen it not do so... I have not been able to divine the rule
behind it). Anyway, using UsedRange, under a normally maintained worksheet,
means the For-Each loop can iterate a smaller-than-the-whole-worksheet range
and still guarantee it will 'hit' every in-use cell on your worksheet.

As for why I use C.Value instead of C... personal preference. A range is an
object and objects usually have a default property. A default property means
if you don't specify it, VB will assume the default property was intended
and automatically reference it. That is what happens when you use C by
itself... VB assumes C.Value. The reason I don't like relying on default
properties is that without specify any property, the object name looks like
a simple variable name making it harder to maintain or modify in the future
(when all the stuff that was fresh in mind when you first created your code
has long since left your memory)... always attaching a property (or method)
reference alerts me that I'm looking at an object, not a variable.

Rick

 

[Rick Rothstein \(MVP - VB\)]

As for what controls the setting/resetting of the UsedRange, it looks like
deleting entire columns and/or rows is the key. See the "To programmatically
reset the used range" section on this webpage...

http://www.contextures.on.ca/xlfaqApp.html

Rick

 

[Jim Rech]

it seems like such a nightmare to me

Yes. Stephen Bullen devoted a lot of ink to it in the Excel VBA books he did
with Rob Bovey and John Green. Too bad MS couldn't have used an approach
that didn't make it so much harder for non-US users.

 

[Xaraam]

woh!..what an explanation!..questions completely vanished!

once again thanks for your help and explanations..

 

[Xaraam]

Rick,

now i have another challenge that probably you can help: i've got 2 row of
values, 1 with those modified date and another one with corresponding integer
values.
I want to turn them into a chart line type, with the date on the x axis and
the integer values on the y axis.
any idea how to do that on VBA?

 

[Rick Rothstein \(MVP - VB\)]

I'm going to suggest you post this as new question to the newsgroup... I
have not very much (read that as almost nothing) with charting, so I am not
the right person to seek advice from on them. Posting the question new
should expose it to many who are more qualified to answer it than I am.

Rick

 

[Xaraam]

ok..no problem..thanks anyways.

I'm going to suggest you post this as new question to the newsgroup... I
have not very much (read that as almost nothing) with charting, so I am not
the right person to seek advice from on them. Posting the question new
should expose it to many who are more qualified to answer it than I am.

Rick

 

[Gord Dibben]

Rick

I stumbled across this UDF from Bob Flanagan which ignores those once filled
then cleared cells.

Function RangeToUse(anySheet As Worksheet) As Range

'this function returns the range from cells A1 to cell which is the
'intersection of the last row with an entry and the last column with an
'entry.

Dim I As Integer, c As Integer, r As Integer

With anySheet.UsedRange
I = .Cells(.Cells.Count).Column + 1
For c = I To 1 Step -1
If Application.CountA(anySheet.Columns(c)) > 0 _
Then Exit For
Next
I = .Cells(.Cells.Count).Row + 1
For r = I To 1 Step -1
If Application.CountA(anySheet.Rows(r)) > 0 Then _
Exit For
Next
End With

With anySheet
Set RangeToUse = .Range(.Cells(1, 1), .Cells(r, c))
End With
End Function

Then a statement like Set rng = RangeToUse(ActiveSheet)


Gord

 

[Rick Rothstein \(MVP - VB\)]

That reminded me of these two functions that I dummied up awhile ago (one
for determining the maximum row in use and the other for determining the
maximum column in use), but I don't think I ever posted them to any
newsgroups before. If you don't specify a worksheet in the first (optional)
argument, then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows or
columns when determining the maximum row and column that are in use; that
is, if a hidden row or column contains the maximum row or column, it will be
ignored unless the second argument is set to True. This allows you to get
the maximum row or column for what you see on the worksheet rather than for
what what any hidden data would return. I wasn't sure which would be the
most logical default for this second argument, so I chose not factor in
hidden rows or columns (that is, the functions return the maximum row and
column for only the visible data); if desired, this can be easily changed in
the declaration headers for each function. I hope you and others will find
this of some use...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Width = 0) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow > MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Function MaxColumnInUse(Optional WS As Worksheet, Optional _
FactorInHiddenColumns As Boolean = False) As Long
Dim X As Long
Dim LastColumn As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Rows.Count
If Not (Not FactorInHiddenColumns And Rows(X).Height = 0) Then
LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column
If LastColumn > MaxColumnInUse Then MaxColumnInUse = LastColumn
End If
Next
End With
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

In looking over my code again, I guess it would be more Excel-like if I used
the Hidden properties of the Columns and Rows in my tests rather than test
for their being "equal to zero" (even though there is no practical
difference between doing either)...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow > MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Function MaxColumnInUse(Optional WS As Worksheet, Optional _
FactorInHiddenColumns As Boolean = False) As Long
Dim X As Long
Dim LastColumn As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Rows.Count
If Not (Not FactorInHiddenColumns And Rows(X).Hidden) Then
LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column
If LastColumn > MaxColumnInUse Then MaxColumnInUse = LastColumn
End If
Next
End With
End Function

Rick