VBA Formula in Loop Where Main Reference is Variable

[chimneytop]

Hey everyone,

I'm working on making a macro that will organize data for a mail merge. The main table is contains 62 lines of data for each person, with the first cell containing multiple rows. I'm using substitute to get around this.

If we aren't in VBA, the formula would be 'Output'!A3 = Substitute('Import'!A63,Char(10),":"), 'Output'!A4 = Substitute('Import'A126,Char(10),":"), ...

There is more code than what I've written below, but the yellow code is where my debugger gets unhappy. How do I make it pull the correct cell?

' Declaring Variables I
Dim i As Integer
Dim i_import_count_request As Integer
i_import_count = InputBox("How many records did you import?")
Dim s_plan_admin As String
s_plan_admin = InputBox("Who is the plan administrator / ER?")
Dim i_start_record As Integer

' Declaring Variables II
Dim ws_imported As Worksheet
Set ws_imported = Sheets("Imported")
Dim ws_output As Worksheet
Set ws_output = Sheets("Output")


' Nth record
ws_output.Select
Range("A1").Select
For i = 1 To i_import_count
i_start_record = 63 * (i - 1)
Range("A1").Offset(i, 0).Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(Imported!R[i_start_record]C,CHAR(10),"":"")"


Next i

 

[chimneytop]

Solved my own problem

ActiveCell.FormulaR1C1 = "=SUBSTITUTE(Imported!R[" & i_start_record & "]C,CHAR(10),"":"")"

 

[Fluff]

How about

ActiveCell.FormulaR1C1 = "=SUBSTITUTE(Imported!R" & i_start_record & "C,CHAR(10),"":"")"