One too many...

**daowen** · 07-29-2009, 04:00 PM

Seems like I found a good area to ask questions in.

Okay, this snip-it is an inserted formula in a cell. It looks at a certain sheet named with a variable.

Problem - The formula is off by one column. How do I change it to the right column?

            ActiveCell.Offset(0, 29).Range("A1").Select  'goto Column AB of the same row.
            ActiveCell.FormulaR1C1 = "=IF(" & today & "!R[+45]C[-21]=" & Chr$(34) & Chr$(34) & _
                    ",IF(" & today & "!R[-9]C[-16]=" & "3" & "," & Chr$(34) & "(" & Chr$(34) & _
                    ",IF(" & today & "!R[-8]C[-16]=TRUE," & Chr$(34) & "K" & Chr$(34) & _
                    ",IF(" & today & "!R[+32]C[-26]=" & Chr$(34) & Chr$(34) & _
                    ",IF(" & today & "!R[+39]C[-26]=" & Chr$(34) & Chr$(34) & _
                    ",IF(" & today & "!R[+48]C[-26]=" & Chr$(34) & Chr$(34) & _
                    "," & Chr$(34) & "*" & Chr$(34) & "," & Chr$(34) & "ü" & Chr$(34) & Chr$(41) _
                    & "," & Chr$(34) & "ü" & Chr$(34) & Chr$(41) & "," & Chr$(34) & "ü" _
                    & Chr$(34) & Chr$(41) & Chr$(41) & Chr$(41) & "," & Chr$(34) & "[" & Chr$(34) & Chr$(41)

Returns this formula:

=IF(Jul28!H58="",IF(Jul28!M4=3,"(",IF(Jul28!M5=TRUE,"K",IF(Jul28!C45="",IF(Jul28!C52="",IF(Jul28!C61="","*","ü"),"ü"),"ü"))),"[")

I need it to return this formula:

=IF(Jul28!G58="",IF(Jul28!L4=3,"(",IF(Jul28!L5=TRUE,"K",IF(Jul28!B45="",IF(Jul28!B52="",IF(Jul28!B61="","*","ü"),"ü"),"ü"))),"[")

Any help is greatly appreciated and warmly received.

Doug

**JBeaucaire** · 07-29-2009, 04:16 PM

It's the "C" values you need to adjust:

IF(" & today & "!R[+45]C[-21]

becomes

IF(" & today & "!R[+45]C[-22]

All the "C" values that are off, adjust them as shown.

**daowen** · 07-29-2009, 04:29 PM

YOU THA MAN!

Thanks! My one project is finished now for work. It was a long week and that was the only thing really that tied me up.

I really appreciate the fast response.

Doug

**JBeaucaire** · 07-29-2009, 04:33 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)