Help with using a macro to write a formula

[furstukin]

I am trying to write a macro that will look at a date I enter and then write a formula based on that date to pull up the correct file. Here is what I have so far to test the idea.

Date1 = " " & Range("AS124").Value
Range("AS126").Select
ActiveCell.FormulaR1C1 = "='N:\WFM\PSG\Comcast\Trending\[Comcast Agent Need Forecasting AHT Trend" & Date1 & ".xlsx]Interval Call Volumes'!L58"

But the problem is whenever I run the macro it adds ' ' markes around the L58 reference so it looks like this

='N:\WFM\PSG\Comcast\Trending\[Comcast Agent Need Forecasting AHT Trend 062011.xlsx]Interval Call Volumes'!'L58'

rather than this

='N:\WFM\PSG\Comcast\Trending\[Comcast Agent Need Forecasting AHT Trend 062011.xlsx]Interval Call Volumes'!L58

Eventually I will have it use this formula to auto fill in the intervals and days for the rest of the week but I can't seem to figure out why the macro is adding the ' ' marks. I have tried all sorts of versions of the formula including

ActiveCell.FormulaR1C1 = "='N:\WFM\PSG\Comcast\Trending\[Comcast Agent Need Forecasting AHT Trend" & Date1 & ".xlsx]Interval Call Volumes'" & "!" & "L" & "58"

but it still adds the ' ' around L58. Is there a way to make this work? My other option will be to make the user select the file from a list then have it pull up the file and reference the cells manually which I would rather avoid.

 

[pgc01]

Hi

You have a syntax error, you are using the .FormulaR1C1 property, but you are specifying the cell address using the A1 notation.

If you want to use the .FormulaR1C1 property, it should be:

ActiveCell.FormulaR1C1 = "= ... !R58C12"

 

[AlphaFrog]

Or use the .Formula property without R1C1

ActiveCell[COLOR="Red"].Formula[/COLOR] = "='N:\WFM\PSG\Comcast\Trending\[Comcast Agent Need Forecasting AHT Trend" & Date1 & ".xlsx]Interval Call Volumes'!L58"

 

[furstukin]

Well that makes sense thanks for the help But now it is auto locking the reference with $ $ signs around the row and column. Is tehre a way to avoid that?

Also is there a macro command to refererence the cells in the A1 notation? I am just using the command that the macro recorder used by default.

 

[pgc01]

I am just using the command that the macro recorder used by default.

You can do it, but if you don't specifically want to use the FormulaR1C1 property then check AlphaFrog's suggestion.

 

[furstukin]

Or use the .Formula property without R1C1

ActiveCell[COLOR=red].Formula[/COLOR] = "='N:\WFM\PSG\Comcast\Trending\[Comcast Agent Need Forecasting AHT Trend" & Date1 & ".xlsx]Interval Call Volumes'!L58"

looks like my question was already answered and if I use this way it is not auto locking the reference.

Thanks for the help

 

[AlphaFrog]

@pgc01

Just out of curiosity, is there a way to use the R1C1 reference style and reference specifically cell L58 with a relative address result (no $s) and irregardless of the ActiveCell?

I'm not looking for an elaborate VBA solution. I just want to know if there is a R1C1 syntax that would do that. It wasn't obvious to me.

 

[pgc01]

@pgc01

Just out of curiosity, is there a way to use the R1C1 reference style and reference specifically cell L58 with a relative address result (no $s) and irregardless of the ActiveCell?

I'm not looking for an elaborate VBA solution. I just want to know if there is a R1C1 syntax that would do that. It wasn't obvious to me.

Hi AlphaFrog

If you really mean "irregardless of the ActiveCell", yes, but if you mean "irregardless of any specific cell", no. "Relative address" in R1C1 means exactly that, "the address is relative to a cell".

When you use the R1C1 notation in a formula you always have the relative row and column addresses relative to the cell where you write the formula.

For ex., if you want in C1 the formula "=F3",

Range("C1").Formula = "=F3"

you'll need to know the distance between the 2 cells, both in terms of rows and columns.

You either play with the row and column values:

Range("C1").FormulaR1C1 = "=R[" & (Range("F3").Row - Range("C1").Row) & "]C[" & (Range("F3").Column - Range("C1").Column) & "]"

or you use the .ConvertFormula() method of the Application object:

Range("C1").FormulaR1C1 = "=" & Application.ConvertFormula("F3", xlA1, xlR1C1, False, Range("C1"))

Anyway, I think that the important point is that when you use the R1C1 notation you don't usually reason in terms of the A1 notation, you use directly the R1C1 logic. For ex.: I want the value of this cell to be the result of the concatenation of the values of the 2 cells to the left. In a case like this one, working with the R1C1 notation makes more sense than with the A1.

 

[AlphaFrog]

Thanks pgc01.

When you use the R1C1 notation in a formula you always have the relative row and column addresses relative to the cell where you write the formula.

That's what I thought. Just wanted to make sure. Thanks again.