Run Replace command in VisualBasics to replace a formula

Question

Run Replace command in VisualBasics to replace a formula

Navigation

#1 by (1 votes)

1

I'm trying to create a macro and I have problems trying to use the Replace command when it affects a formula in VisualBasics.

My situation is this:

In column L I have calculations of slope of different ranges: =PENDIENTE(G33:G60;C33:C60) =PENDIENTE(G95:G122;C95:C122) etc.

I have to change the PENDING calculation by AVERAGE (the excel is in Spanish so the operations have it in Spanish) so that it is: =PROMEDIO(G33:G60) =PROMEDIO(G95:G122) etc.

After some attempts I stayed here (I do not know if it's okay either):

Dim Cadena1 As String
    Cadena1 = Replace("=PENDIENTE(G*:G*;C*:C*)", "PENDIENTE", "PROMEDIO")


Dim Cadena2 As String
    Cadena2 = Replace("=PROMEDIO(G*:G*;C*:C*)", ";C*:C*", " ")


Dim iRow, StartRow As Integer
Dim Col1, ColValues As String
    Col1 = "L"
    ColValues = "M"
    StartRaw = 2

NumRows = Range(Col1 & StartRow, Range(Col1 & StartRow).End(xlDown)).Rows.Count

Any advice? If anyone knows a VisualBasics manual that could help or something similar, I would also appreciate it.

vba excel vb6

asked by Albert G 26.04.2017 в 10:19

source

1 answer

How to make a tour of N records in Javascript Ion Auth create user (not administrator) without login

score 1 · Accepted Answer

With the .formula property you can assign the formula to the range of cells.

I put average instead of promedio and slope instead of pendiente because VBA uses the names in English

Sub prueba()

    Dim cell As Range

    Dim celda_inicio As String
    Dim celda_fin As String

    celda_inicio = "A7"
    celda_fin = "A15"

    Dim formula_vieja As String

    Dim posicion As Integer


    For Each cell In Range(celda_inicio, celda_fin)

        formula_vieja = cell.formula

        formula_vieja = Replace(formula_vieja, "SLOPE", "AVERAGE")

        posicion = InStr(1, formula_vieja, ",")

        If posicion > 0 Then
            cell.formula = Left(formula_vieja, posicion - 1) + ")"
        End If

    Next

End Sub

Operation:

Go cell by cell in the specified range with the variables, and take the current formula and change slope by average

Then find where the comma is. The formula in English is not =PENDIENTE(G33:G60;C33:C60) if not =PENDIENTE(G33:G60,C33:C60)

If you find the comma, pick up the%% witch from the left and then add a% co_of% to close the formula