On the Fly Formulas

[ChrisY]

Let me start with a fact. I know just enough about VBA to make myself very dangerous. Having said that, I have been playing with a formula that uses Offset. What I am trying to do is get VBA to read a variable value (CellOffset) from a cell on another worksheet, and have the offset value in the foumula changed to the value of CellOffset and then written into a cell on the active worksheet. I can see that the CellOffset is taking the right value - still can't get it into the formula below.

=IF(ISNUMBER($D2),OFFSET(('DN01'!$A$1),(MATCH(($D2),'DN01'!$D:$D,0)-1),CellOffset),"")

I have been able to get the formula into the target cell; however, the offset value is entered as "CellOffset", not the variable value.

Is this possible or am I just being dumb?

Any suggestions would be greatly appreciated.

Thanks

ChrisY

[Tom Schreiner]

I do not know how to put a formula into a cell via code, but I am guessing that it would be treated by VB as a string. It is passing a string in your case "CellOffset"

To include a Variable in a string, the typical sytax is thus:


IntegerVariable = 31

StringPlusVar = "I am" & IntegerVariable & " years old."

Yields

I am 31 years old

In other words, include use " & before the variable you are passing and & " after the variable.

Have Fun!


[ This Message was edited by: TsTom on 2002-03-22 19:31 ]

[Jay Petrulis]

Hi Chris,

=IF(ISNUMBER($D2),OFFSET(('DN01'!$A$1),(MATCH(($D2),'DN01'!$D:$D,0)-1),CellOffset),"")

In VBA would be inserted as

=IF(ISNUMBER(R2C4),OFFSET(('DN01'!R1C1),(MATCH((R2C4),'DN01'!C4,0)-1)," & CellOffset.value & "),"""")"

Note the double, double-quotes to give the blank.

Warning, your reference to $D2 is a relative row reference. The VBA line I gave you makes it fully absolute. If you need relative row, you will change it to R[?]C4.

HTH,
Jay

[Al Chara]

First off, I think your formula is wrong, try the following:

=IF(ISNUMBER($D2)=TRUE,OFFSET('DN01'!$A$1,MATCH($D2,'DN01'!$D:$D,0)-1,CellOffset),"is not a number")

Secondly you cannot use variable names in the formula for a cell. You can do all the calculations in VBA and then have the result put into a cell. Try the following VB code:

Dim ColumnOffset, RowOffset, FirstRow, LastRow As Integer
LastRow= 2
LastRow= 3
ColumnOffset = 9
For i = FirstRow To LastRow
RowOffset = Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("d" & i), Worksheets("DN01").Columns(4), 0)
If Application.WorksheetFunction.IsNumber(Worksheets("Sheet1").Range("d" & i).Value) = True Then
ActiveCell.Offset(i - 2, 0).Value = Worksheets("DN01").Range("a1").Offset(RowOffset, ColumnOffset)
Else: ActiveCell.Offset(i - 2, 0).Value = "D2 is not a number"
End If
Next i

Substitute the 9 in ColumnOffset with the value or range of your choice, substitute 2 in FirstRow for the row you want to start on in column D, and substitute 3 in LastRow for the row you want to finish on in column D.

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-03-22 20:44 ]

[ChrisY]

Sorry for not getting back to you and thanks for all the input. I was out of town for a few days * not * thinking. However, this is still bothering me. I have tried several of the solutions offered but still can't get it to work when the "=" is included in the formula.

What I'm trying to do is limit the size of the appllication and reduce the amount of traffic over the network by just passing the offset required in each formula, as the formula is basically the same thoughout the worksheet. As it is now, each time the user calculates a report, a 10 column X 75 row area of one of eight different worksheets gets copied from a server to the local machine. These worksheets presently include both the titles of the various sections and the formulas that pull the data. As the server-based reports may change, I was trying to keep them in a central area so I didn't have to update 300 copies of the application after it is distributed.

Any thoughts?

Thanks again for all the help.

ChrisY