PDA

View Full Version : [SOLVED] insert formula via loop including cell contents in a range



SteveM99
11-15-2018, 01:19 PM
I am trying to write code that will take the current value of cells in range and add an excel if formula to analyze if the cell value in a few columns over is zero to display or not display information of the current cell value

Here is my code
'-----------
Dim RNG As Range
Dim Cell As Range

Set RNG = Worksheets("report").Range("a8:c11")

For Each Cell In RNG
Cell.Formula = "=if($g8=0,""""," & """" & Cell.Value & """" & ")"

' comment - this is the equivalent of =if(g8=0,"",cell value) meaning if g8 s zero take the current cell value and show nothing or "" in my case

'Next
'-----------
I need g8 to change to g9, g10, etc to the end of range (row only change) but it does not. It uses G8 in each formula for the range.

rlv
11-15-2018, 02:25 PM
Maybe somthing like this?



Cell.Formula = "=if($G" & Cell.Row & "=0,""""," & """" & Cell.Value & """" & ")"

SteveM99
11-15-2018, 02:44 PM
Dear rlv, thank you, this worked perfectly. I appreciate you providing this solution to me.