how do i enter R1C1 in VBA for a seperate worksheet w/o skipping

[ungoliath]

Here's my code

For k = 1 To Project_Columns
Sheets(Project_Titles(k)).Activate
Cells(1, 1).Activate
For i = 1 To POC_Rows
If POC_data(i, k) = 1 Then
For j = 1 To 6
Cells(24 + i, 15 + j) = "='POC Information'!R[" & i + 15 & "]C[" & j - 15 & "]"
Next j
End If
Next i

the problem I get is that the R1,C1 are determined based upon the active cell. How do I input this in absolute coordinates so that the cell value doesn't continually skip a cell.

When I run this code I get cell (25,16)="POC Information!R47"
but cell (25,17)="POC Information!T49" instead of "POC Information!S49" like I'd prefer.

 

[Rhino373]

Can't you just select a cell? Where you have Cells(1, 1).Activate change it to Cells(1,1).Select. Your R1C1 should then be based on that selected cell. The rest of your code doesn't seem to ever select a different cell.

 

[ungoliath]

That doesn't work. The R1C1 reference seems to be governed by the cells(i,j) position.

 

[ungoliath]

I don't really want to use the R1C1 reference to begin w/, I just don't know of any other way.

 

[hoosaskin]

Could you use something like this?:

Cells(3, "D").Formula = "=IF(AND(D2>=0,D2<B2*0.05),CEILING(B2*0.05,1),D2)"

 

[jmthompson]

What is it you want to happen?

do you simply want to make one cell (always the same) in the Project Titles sheet to match a specific cell in the POC Information sheet?

Do you need any portion of that to be flexible/variable?

 

[ungoliath]

@JMThompson

that's pretty much what I'm trying to do. I'm actually setting up an update macro that populates a project sheet w/ all the information from my POC sheet that's related to a specific project. It will update a contacts area of the project sheet w/ links to the POC tab. It doesn't need to be linked but that seemed much simpler than trying to populate the chart based on array data (I'm beginning to think I may be wrong on that one).

 

[Andrew Poulsom]

Why don't you use an absolute R1C1 formula?

 

[ungoliath]

@ andrew paulson

What does an absolute R1C1 reference look like, especially if it's on another sheet?

 

[Andrew Poulsom]

Like this:

Cells(24 + i, 15 + j).FormulaR1C1 = "='POC Information'!R" & i + 15 & "C" & j - 15