CODEWITHSUNDEEP
vbaexcel
morandalex
morandalex

Reputation: 95

Excel VBA - insert formula in a set of rows with variable reference directly or replacing a string for example "\=" with "="

I have the goal to write a formula in a set of rows. Some references in the formula have to change each row.

I implemented the following script:

Dim i As Integer
Dim formcolM As String
Dim temprng As String

For i = 0 To 100  
    formcolM = "NUMBERVALUE(IF(Q" & i & "=""Bedarf kum."";A" & i & ";IF(Q" & i & "=""Ist"";OFFSET(A" & i & ";-1;0);IF(Q" & i & "=""Lz."";OFFSET(A" & i & ";-2;0);IF(Q" & i & "=""Ist+Lz.-Bedarf"";OFFSET(A" & i & ";-3;0);)))))"
Let temprng = "M" & i
Range(temprng).Select
ActiveCell.Value = "\=" & formcolM
next i

With this script I am writing a string each row in my excel table at column M.

I noticed that if the formula hasn't the symbol "\" , you can find an error .

In order to avoid the error I thought to leave the symbol "\" and to use a trick deleting it after (because I don't know how to solve with R1C1 formula. I read some answers on Stackoverflow, but unfortunately I did not understand )

The replacing script after the for cycle:

Columns("M:M").Replace What:="\=", Replacement:="=", LookAt:=xlPart

The strange thing is that the macro doesn't delete it. Infact when the script finishes , it seems that nothing happened, without errors. But if I want substitute "\=" with another symbol, for example "*", the replacing script works.

I did not understand if the problem is :

OR, is there another simplest way to get this task done?

Someone could help me in order to fix? I should have the formula working in the column M , automatically with vba (not with another formula in the excel sheet) .

Thanks in advance for your time.

Upvotes: 0

Views: 2022

Answers (2)

Mitch
Mitch

Reputation: 583

By the time I got this worked out, Scott already had an answer. I just wanted to post your original code modified to work. I would suggest his method.

Sub TestScript()

Dim i As Integer
Dim formcolM As String
Dim temprng As String

For i = 4 To 100
formcolM = "NUMBERVALUE(IF(Q" & i & "=" & "Bedarf kum." & ";A" & i & ";IF(Q" & i & "=" & "Ist" & ";OFFSET(A" & i & ";-1;0);IF(Q" & i & "=" & "Lz." & ";OFFSET(A" & i & ";-2;0);IF(Q" & i & "=" & "Ist+Lz.-Bedarf" & ";OFFSET(A" & i & ";-3;0);)))))"
temprng = "M" & i
Sheets("Sheet1").Range(temprng).Select
ActiveCell.Value = " = " & formcolM
Next i

End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152585

We can apply the formula directly. The issue is that vba is very US-EN Centric and all formula when using the .Formula needs to be in that format.

Also since your formula refers to values in a row 3 above the one in which it is put we need to start the loop at 4 not 0. There is no row 0

There are two ways, in US-En format with English functions and , as the deliminator using .Formula:

Dim i As Integer

For i = 4 To 100
    Range("M" & i).Formula = "=NUMBERVALUE(IF(Q" & i & "=""Bedarf kum."",A" & i & ",IF(Q" & i & "=""Ist"",OFFSET(A" & i & ",-1,0),IF(Q" & i & "=""Lz."",OFFSET(A" & i & ",-2,0),IF(Q" & i & "=""Ist+Lz.-Bedarf"",OFFSET(A" & i & ",-3,0),)))))"
Next i

Or using .FormulaLocal and the formula as you would write it in your native tongue.

Dim i As Integer

For i = 4 To 100
    Range("M" & i).FormulaLocal = "=NUMERO.VALORE(SE(Q" & i & "=""Bedarf kum."";A" & i & ";SE(Q" & i & "=""Ist"";SCARTO(A" & i & ";-1;0);SE(Q" & i & "=""Lz."";SCARTO(A" & i & ";-2;0);SE(Q" & i & "=""Ist+Lz.-Bedarf"";SCARTO(A" & i & ";-3;0);)))))"
Next i

Upvotes: 2

Related Questions