3 Tips for Writing Formulas with VBA Macros in Excel

Hi!

Can we create a formula that divides cell that is dynamic depending on where the active cell is located?

Sample:
= /

Hi, perkenalkan nama saya novianto saya berasal dari Indonesia

Tolong bantu saya, saya mengalami stak untuk formula yang sedang kita kembangkan

Well I’m sure I’m doing something really dumb with regard to syntax, although I’ve tried every permutation I can think of. This program will read through the data in UsedRange until it sees an empty cell (I realise my counter needs to be recalibrated, not important at the moment). The starting point and end point are present as integers with the variables i and n. These are converted to strStartPoint and strEndPoint, which are both used to produce a string that is the formula I want entered.

I’ve tried creating a string variable that contains the entire formula and just inserting that into the cell, but because it contains the = sign, it inserts a boolean result in the cell.

I would much appreciate any advice about how to do this. I’ve tried using .value =, .formula =, and at no point can I come up with a syntax that allows me to input this formula as I want.

Thanks in advance for your time.

Sub CalculatePeaks(i As Integer)

Dim n As Integer
Dim vCellValue As Variant
Dim strStartPoint As String
Dim strEndPoint As String
Dim W As Variant

Worksheets(“SortedData”).UsedRange

W = Worksheets(“SortedData”).UsedRange.value

n = i

strStartPoint = CStr(i)
vCellValue = W(n, 1)

Do Until vCellValue = Empty

vCellValue = W(n, 1)

n = n + 1

Loop

strEndPoint = CStr(n)

Worksheets(“SortedData”).Range(“K” & n).value = “=ROUND((I” & strStartPoint & “-((AVERAGE(I” & strStartPoint & “:I” & strEndPoint & “))*0.00001)),5)”””

End Sub

I don’t know much about macros at all, i have just learnt what i need from a bit of googling and trial & error!

In my worksheet, I am using a Command button to insert formulas referring to another sheet. This works fine, until I insert a row into the main sheet – because the cell references in the macro are R1C1 style, this means the formula looks in the wrong place on the other sheet.

How should i write the cell references in the code so that it looks at the right place?

I am trying to add a rather cumbersome formula in my Excel macro, but I am lost as to how to separate the text (if needed) versus the cell reference and its erroring out. Any help would be greatly appreciated.

VendorSheet.Range(“AG6”).Formula = “IF(ISBLANK(X6),IF(Z6=”I”,IF(R6=0,TODAY(),””),””),X6)”

Remember that formulas and functions return a value. Always.
When you’re struggling with a formula, sometimes it’s because you think part of the formula is returning a certain value but it fact it is returning something else. To check what is actually being returned by a function or by part of a formula, use the F9 tip below.
Note: The word “return” comes from the world of programing, and it sometimes confuses people new to this concept. In the context of Excel formulas, it’s used like this “The LEN function returns a number” or “The formula in cell A1 returns an error”. Whenever you hear the word “return” with formula, just think “result”.21. Use F9 to evaluate parts of a formula.

This is a tip, to make the work easier,
Which is given in the note. He is not understanding.
pls solve this problem

Hi Jon

this is correct or not because part of B.O. was not working please help

Sub findrep()

Dim Target, cell As Range
Dim i, k As String
i = “C.I.”
k = “=N2”

Set Target = Sheets(“Sheet5”).Range(Range(“E2”), Range(“E65536”).End(xlUp))

For Each cell In Target
If cell.Value = i Then cell.Offset(0, 5) = k

Next cell

i = “M.S.”
k = “=N3”

Set Target = Sheets(“Sheet5”).Range(Range(“E2”), Range(“E65536”).End(xlUp))

For Each cell In Target
If cell.Value = i Then cell.Offset(0, 5) = k

Next cell

i = “SGI”
k = “=N4”

Set Target = Sheets(“Sheet5”).Range(Range(“E2”), Range(“E65536”).End(xlUp))

For Each cell In Target
If cell.Value = i Then cell.Offset(0, 5) = k

Next cell

‘Dim sFormula As String’
‘i = “B.O.”
‘k = sFormula
‘sFormula = “=cell.offset(i,5)*n5”
‘Set Target = Sheets(“Sheet5”).Range(Range(“e1”), Range(“e65536”).End(xlUp))

‘For Each cell In Target
‘If cell.Value = i Then cell.Offset(0, 5) = sFormula

‘ActiveCell.FormulaR1C1 = sFormula

‘ActiveCell.FormulaR1C1 = “=R[-1]C*R[-1]C[1]”
‘ActiveCell.FormulaR1C1 = “=10*RC[1]”
‘Next cell

End Sub

Hello Jon. Thank you for this video. I was searching the web for an answer to:

What would explain why this formula works:
Range(“A1”).Formula = “=Sum(F5:F595)”

while this one does not:
Range(“A1”).Formula = “=Round(Sum(F5:F595);2)”

Using your tip of recording a macro, the formula gave:
Range(“A1”).FormulaR1C1 = “=Round(Sum(R[3]C[-3]:R[593]C[-3],2)”

This is when I noticed that it ends with ,2) instead of ;2) So I changed my formula to:
Range(“A1”).Formula = “=Round(Sum(F5:F595),2)”

When I run the code, it works, however the formula written in the cell ends with ;2)

Do you see an explanation for this? Why VBA needs ,2) to write the formula with ;2) in the cell?

PS: my Excel is setup to work with semi-colon (;) in formulas because of language settings of Win