problems recording macro_value or formula?

[Guillermo]

I've recently started programming VBA, I didn't believe I needed it,
but I'm recording a macro to replace formulas and if I'm not in an
error I think I can not replace them but using the final value or the
entire formula. I want to explain it::

Of course replace didn't work, that was my first option.

If I use range.value I have to introduce last solution.

If I use range.formula and aI don't use just cell's references but VBA
parameters too, formula doesn't work

For ex.

Sub replace4()
'nofunciona alternar formula con parámetros
Dim truerange As Range
Dim str As String
Dim num As Double
Set truerange = Range("a11", Cells(1084, 107))
If IsArray(truerange) Then
ar = truerange
row1 = truerange.Row
column1 = truerange.Column
lastrow = row1 + UBound(ar, 1) - 1
lastcolumn = column1 + UBound(ar, 2) - 1
For irow = row1 To lastrow
For icol = column1 To lastcolumn
str = Cells(irow, icol).Formula
If Mid(str, 1, 1) = "<" Then
num = CDbl(Mid(str, 2, 1000))
formu = "=" & num & "/2"
Cells(irow, icol).Formula = formu
End If
Next icol
Next irow
End If
End Sub

With that macro I wanted to replace values lower than a number by the
half of that number. The macro doesn't work and I think it's because
divide can't operate with num.

Another thing I want to do is to repalce mean by fcamg (fcamg is a
UDF) but it doesn't work, either. I'm thinking about doing fcamg to
work with mean argument.

Some times I would like to explicit formula so I can deduce what
procedure I have used to obtain that value. My problem is solved for
"<" and I think may solve for "mean", but I won't know what values are
calculated and what way. Before now I always left formulas in cells,
but I can do the same if I use macros to do this?.

Please, anybody who can help me with this interesting issue, Im
attending for your answer,

 

[Bill Renaud]

It appears that you are simply iterating through all of the cells in an
entire range of the worksheet. If so, then you can use a For Each rngCell
.... type of program statement. (I defined rngAllData to be all of the cells
on the active worksheet, but you can comment this line out and put in the
line below it, if you need to.)

Change SomeValue to be the value that you want to compare each cell value
to.
This routine skips over cells that are not a formula, or ones that have a
formula that evaluates to an error for some reason.

This routine encloses the entire formula in parentheses, then adds a "/2"
at the end of the formula.


Public Sub ReplaceFormulas()
Const SomeValue As Double = 1000.123
Dim wsActive As Worksheet
Dim rngAllData As Range
Dim rngCell As Range

Set wsActive = ActiveSheet
Set rngAllData = wsActive.UsedRange 'Use all cells on the worksheet.
'or if you really must use only a fixed range of cells:
'Set rngAllData = wsActive.Range("A11C1084")

For Each rngCell In rngAllData
With rngCell
If .HasFormula _
Then
If Not IsError(.Value) _
Then
If .Value < SomeValue _
Then
.Formula = "=(" _
& Right$(.Formula, Len(.Formula) - 1) _
& ")/2"
End If
End If
End If
End With
Next rngCell
End Sub

 

[Bill Renaud]

<<Another thing I want to do is to replace mean by fcamg (fcamg is a UDF)
but it doesn't work, either.>>

I don't think there is an Excel function named "mean", but there is one
named "Average", so I used that one in my demo routine below. The routine
below checks all cells that have a formula, then replaces any occurence of
"Average" with "fcamg" (your UDF).

I included an error handler (hope it works OK!), and probably should have
on the previous post also.

Public Sub ReplaceAverageWithUDF()
Dim wsActive As Worksheet
Dim rngAllData As Range
Dim rngCell As Range

On Error GoTo ErrHandler
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set wsActive = ActiveSheet
Set rngAllData = wsActive.UsedRange

For Each rngCell In rngAllData
With rngCell
If .HasFormula _
Then
'Do Repace only on formulas,
'not cells that happen to have
'the word "Average" as a comment.
.Replace What:="Average", _
Replacement:="fcamg", _
LookAt:=xlPart, _
MatchCase:=False
End If
End With
Next rngCell

ErrHandler:
With Application
.Calculation = xlCalculationAutomatic
.CalculateFull
.ScreenUpdating = True
End With
End Sub

 

[Guillermo]

<<Another thing I want to do is to replace mean by fcamg (fcamg is a UDF)
but it doesn't work, either.>>

I don't think there is an Excel function named "mean", but there is one
named "Average", so I used that one in my demo routine below. The routine
below checks all cells that have aformula, then replaces any occurence of
"Average" with "fcamg" (your UDF).

I included an error handler (hope it works OK!), and probably should have
on the previous post also.

Public Sub ReplaceAverageWithUDF()
Dim wsActive As Worksheet
Dim rngAllData As Range
Dim rngCell As Range

On Error GoTo ErrHandler
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set wsActive = ActiveSheet
Set rngAllData = wsActive.UsedRange

For Each rngCell In rngAllData
With rngCell
If .HasFormula _
Then
'Do Repace only on formulas,
'not cells that happen to have
'the word "Average" as a comment.
.Replace What:="Average", _
Replacement:="fcamg", _
LookAt:=xlPart, _
MatchCase:=False
End If
End With
Next rngCell

ErrHandler:
With Application
.Calculation = xlCalculationAutomatic
.CalculateFull
.ScreenUpdating = True
End With
End Sub

Ican see now, you don't receive my messages with attached files. This
is a piece of worksheet.


IDMUESTRA;MUESTRAS;NOMBRE;FECHA;CAUDAL (l/s);NIVEL (m)
4;1;Fuente de Almajalejo;01/04/2005;..........;..........
5;1;Fuente de Almajalejo;01/04/2005;..........;..........
4,5;1;"=SUBTOTALES(1;C12:C13)";01/04/2005;#¡DIV/0!;#¡DIV/0!
929;1;Fuente de Almajalejo;27/11/2006;..........;..........
975;1;Fuente de Almajalejo;27/11/2006;..........;..........
952;1;#¡DIV/0!;27/11/2006;#¡DIV/0!;#¡DIV/0!
949;1;Fuente de Almajalejo;30/04/2007;8;..........
992;1;Fuente de Almajalejo;30/04/2007;..........;..........
970,5;1;#¡DIV/0!;30/04/2007;8;#¡DIV/0!
11;2;Las Minas;01/04/2005;3,0;..........
12;2;Las Minas;01/04/2005;..........;..........
11,5;2;#¡DIV/0!;01/04/2005;3,0;#¡DIV/0!
921;2;Las Minas;27/11/2006;1,3;..........
982;2;Las Minas;27/11/2006;..........;..........
951,5;2;#¡DIV/0!;27/11/2006;1,3;#¡DIV/0!
935;2;Las Minas;30/04/2007;6;..........
1016;2;Las Minas;30/04/2007;..........;..........
975,5;2;#¡DIV/0!;30/04/2007;6;#¡DIV/0!
.......................................................................................
#1Div/0 is always a subtota(1; )formula.These are the two macros that
properly work replacing "<",but they don't insert formulas:

Sub replace6()
Dim truerange As Range
Dim str As String
Dim num As Double
Set truerange = Range("a11", Cells(1084, 107))
If IsArray(truerange) Then
ar = truerange
row1 = truerange.Row
column1 = truerange.Column
lastrow = row1 + UBound(ar, 1) - 1
lastcolumn = column1 + UBound(ar, 2) - 1
For irow = 16 To lastrow
For icol = 31 To lastcolumn
str = Cells(irow, icol).Value
If Mid(str, 1, 1) = "<" Then
Cells(irow, icol).Interior.ColorIndex = 30
num = CDbl(Mid(str, 2, 1000)) / 2
Cells(irow, icol) = num
End If
Next icol
Next irow
End If
End Sub

This may be expressed with for each
.........................................................................................................

Sub replace5()

'Dim truerange As Range
'Set truerange = Range("a11", Cells(1084, 107))
Dim FoundCell As Range
Do
Set FoundCell = Selection.Find(what:="<", LookIn:=xlValues,
lookat:=xlPart, _
SearchOrder:=xlByRows, searchdirection:=xlNext,
MatchCase:=False)
FoundCell.Value = Replace(FoundCell.Value, "<", "DL*")
Loop
End Sub
..............................................................................................................
This is the function fcamg designed to locate a cell corresponding to
a row defined in column 10:

Public Function fcamg(ByRef ar As Range) As String

If IsArray(ar) Then
myarray = ar
irow1 = ar.Row
irow2 = irow1 + UBound(myarray, 1) - 1
icolumn = ar.Column
myrange = Range(Cells.Item(irow1, 106), Cells.Item(irow2, 10))
Position = WorksheetFunction.Match(1, myrange, 0)
irow = irow1 + Position - 1
fcamg = Cells.Item(irow, icolumn).Value
End If
End Function
..........................................................................................................................
bill, you can see your macro doesnt work setting:

Set rngAllData = Selection

and

.Replace what:="subtotal((1;", _
Replacement:="fcamg", _
lookat:=xlPart, _
MatchCase:=False

so I don't know what can be wrong. Please, any more suggestion?

 

[Bill Renaud]

Your country settings are preventing me from clearly seeing what you are
doing. I am seeing character 161 in your # DIV/0! values in the data you
posted.

<<Ican see now, you don't receive my messages with attached files.>>
I don't believe that the newsgroups allow attachments.

<<#1Div/0 is always a subtota(1; )formula.>>
I get "#DIV/0!" for an error value in a cell that has an error. How does
the "1" show up in your (Spanish?) version?

Your "replace6" routine simply checks to see if the first character in the
cell value is a "less than" ("<") symbol (?). I don't understand what you
are trying to do.

Routine "replace5" loops forever, without end.

All I can suggest at this point is to turn on the macro recorder while
editing one of the cells that you want to change, then look carefully at
the result to incorporate it into the loop in your routines (or post the
recorded code in the newsgroup).

 

[Guillermo]

Your "replace6" routine simply checks to see if the first character in the
cellvalueis a "less than" ("<") symbol (?). I don't understand what you
are trying to do.

Routine "replace5" loops forever, without end.

All I can suggest at this point is to turn on the macro recorder while
editing one of the cells that you want to change, then look carefully at
the result to incorporate it into the loop in your routines (or post the
recorded code in the newsgroup).

Hi Bill, thanks for answering:

Character 161 is allways in error "#DIV/0!". The macro recorder is the
first thing I tried but replace is different and it doesn't work as
macro.

If I could send you my excel file you could see better what I'm doing.
Subtotal(1, is the same as average that we said first.Now I'm
developing macros with for each and select case to replae that
subtotal(1, by fcamg(, but the formula inserted doesn't calculates, I
think it may be a string.

Replace 6 substitutes a number less than a value by the half of this
value. The only problem is that I want to explicit formula so I want
to enter the formula with cell.formula but the formula introduced
appears like a string and excel doesn't calculate it.

Replace 5 doesn't loop forever because there is an error when Find
doesn't find a proper cell.

 

[Bill Renaud]

<<If I could send you my excel file you could see better what I'm doing.>>

If you send me a file, please save it in Excel 2000 version, as that is the
version I am using, otherwise I may not be able to open it. Also, if the
file is large, then perhaps you could create a much smaller version to
send.

<<Replace 5 doesn't loop forever because there is an error when Find
doesn't find a proper cell.>>

Relying on an error to terminate a routine is BAD programming practice!

 

[Guillermo]

<<If I could send you my excel file you could see better what I'm doing.>>

If you send me a file, please save it in Excel 2000 version, as that is the
version I am using, otherwise I may not be able to open it. Also, if the
file is large, then perhaps you could create a much smaller version to
send.

<<Replace 5 doesn't loop forever because there is an error when Find
doesn't find a proper cell.>>

Relying on an error to terminate a routine is BAD programming practice!
--
Regards,
Bill Renaud
Remove the No.Spam part of my e-mail address.
Leave only one period between the first and last names.

Now I can see, you are right. It was a problem with my country
options. VBA can't manage with spanish numbers with comma. I must
substitute comma with points before proceed.

Thanks a lot, you are a genious of programming. Merry christmas Bill.