Help with writing formulas to cells using VBA

[Thomas12209]

I am trying to put formulas on one sheet to reference values from another sheet in the same workbook. I will need to populate about 182,500 cells in total. I was going to use a simple loop program to accomplish this. The trouble I am running into is that I am not sure how to keep the R1C1 format so that I can move right and down the sheet during the loops. What I would like it to say is

='Baltec 1'!G10.

Colum G, is only the column for the first 135 examples of the first row. When I move to row 2 on the sheet I will need Column H. Every row I move down, Will be one Column higher from the source sheet. Here is what I was trying.

Cells(r, c).FormulaR1C1= "='Baltec 1!" & sr & sc.

Where sr is the source row and sc is the source column.

 

[Joe4]

Welcome to the Board!

Loops are notoriously slow, and show be avoided whenever possible. I am pretty sure you can do this without looping through 182,500 cells.

What is the first cell you are putting this formula in?

='Baltec 1'!G10

And what should the formula look like for the next few rows down under that?

 

[Thomas12209]

The first cell is on a Sheet named PBIOEE source Cell C4
PBIOEE source C4 ='Baltec 1'!G10
PBIOEE source C5 ='Baltec 1'!H10
PBIOEE source C6 ='Baltec 1'!I10
PBIOEE source C7 ='Baltec 1'!J10

At the same time
PBIOEE source C4 ='Baltec 1'!G10
PBIOEE source D4 ='Baltec 1'!G11
PBIOEE source E4 ='Baltec 1'!G12
PBIOEE source F4 ='Baltec 1'!G13

The data from the Baltec 1 sheet is being transposed. Also the data on Baltec 1 sheet does not just continue down the sheet, there are gaps that need to be skipped. There is what I have so far that works so long as I call out the column on the Baltec1 sheet.

Sub AddFormulas()

Dim i As Integer 'move through the sheets
Dim sr As Integer 'Source row
Dim sc As Integer ' Source collumn
Dim dr As Integer 'move through the rows
Dim dc As Integer 'move through the collumns
Dim d As Date 'adds the date
Dim riviter As String 'sheetname / machine data
Dim wklp As Integer ' moves through the sheet 13 at a time


sr = 10
sc = 7
dr = 4
dc = 3
wklp = 13


For i = 1 To 4 'one loop for each sheet

If i = 1 Then riviter = "Baltec 1"
If i = 2 Then riveter = "Baltec 2"
If i = 3 Then riviter = "Guillemin 1"
If i = 4 Then riviter = "Guillemin 2"

Worksheets("OEE Hidden sheet").Activate

For r = dr To 11 ' 1463 ' Total Number of rows needed

sr = 10

For c = dc To 123 ' Total number of columns needed

If sr = 14 Then sr = 18
If sr = 29 Then sr = 30
If sr = 34 Then sr = 36
If sr = 42 Then sr = 47
If sr = 143 Then sr = 146

Cells(r, c).Formula = "='Baltec 1'!" & "g" & sr

sr = sr + 1


Next c
sc = sc + 1

Next r

Next i

End Sub

 

[Joe4]

Here is some code that shows you how to populate a whole range of cells with the appropriate formulas with a single line of code, no loops necessary.
The key is to pick some starting cell (G10) and lock that reference down, and then use the OFFSET formula to move rows and columns in reference to that.

Just apply it to any range you want. I used "C4:F7" from your last example.

Sub MyPopulateCells()
'   Populate cells C4:F7 on "PBIOEE" sheet will necessary formulas
    Sheets("PBIOEE").Activate
    Range("C4:F7").FormulaR1C1 = "=OFFSET(Baltec!R10C7,COLUMN()-3,ROW()-4)"
End Sub

 

[Thomas12209]

Here is some code that shows you how to populate a whole range of cells with the appropriate formulas with a single line of code, no loops necessary.
The key is to pick some starting cell (G10) and lock that reference down, and then use the OFFSET formula to move rows and columns in reference to that.

Just apply it to any range you want. I used "C4:F7" from your last example.

Sub MyPopulateCells()
'   Populate cells C4:F7 on "PBIOEE" sheet will necessary formulas
    Sheets("PBIOEE").Activate
    Range("C4:F7").FormulaR1C1 = "=OFFSET(Baltec!R10C7,COLUMN()-3,ROW()-4)"
End Sub

Thanks you for your help with this. Because of the nature of having to jump 7 columns on the source page every 7 loops, and the fact that the row data has to skip rows as well I stuck with the loop. My final solution was -

Cells(r, c).Formula = "='Baltec 1'!R[" & sr & "]C[" & sc & "]"

using forms of sr=sr+1 or -1 to keep all the formulas pointing to the correct places. I know that it is going to run slow with all the loops, but I will only have to run this program once a year when I reset the sheets for the next year. I can even start it before walking out the door one evening and it will be done when I get back in the next day. Thanks again for taking time to help me out.