Problem with relative reference when pasting formula from VB into Excel

I'm transferring data from a VB form into an Excel spreadsheet. Clicking OK on the form adds a new row to the spreadsheet containing the form data. Some of the spreadsheet cells, though, need to calculate their values from the form data; i.e., they contain formulas. So in the VB script I've included those cells and their formulas.

But when VB puts a formula into the spreadsheet, the cell references are not relative. For example, in the spreadsheet the formula in row 1 might be

=A1*26

In the VB script, if I just put

Please Login or Register  to view this content.

in the new row 2 in the spreadsheet it still uses the value from the old row's cell A1, not the new row's A2.

So I've added an Offset to the VB formula, and VB doesn't complain about my syntax but in the pertinent spreadsheet cell I'm getting a #NAME? error now.

Here's a portion of the VB script. I've colored the Offset bits:

Please Login or Register  to view this content.

So my question is, What's the best way to produce a relative cell reference in a VB script?

When you use the formula property of a range you're setting the text of the formula, so where you've got Offsets from the ActiveCell being added into your formula it will add in the default property of that range, which is the value, whereas you probably want to add the .Address of the cell.

The more usual way of adding relative cell references is to set the R1C1 formula property of the range.

So, in your initial example, if you were putting that formula into cell B1 you could go with:

Please Login or Register  to view this content.

Where RC[-1] Refers to the cell on the same row as formula and one column to the left.

Thanks for your reply, Andrew-R. But I couldn't get your solution to work. (Here's where I show my shocking ignorance of VB.) Now I'm getting a VB error: "Compile error: Expected: end of statement." In the code, VB highlighted the "[-1]" (in red in the following line; your code's in green):

Please Login or Register  to view this content.

(I changed the multiplier 26 to 1000.) I assume I've implemented your suggestion incorrectly, so my syntax is off. Can you correct my error?

I can do my best. Based on your original post I think you want:

Please Login or Register  to view this content.