Dynamically pasting function from Visual Basic to Excel cell
 

Thanks Tom

You suggestion was very helpfull, however, I found the error was just my
typo as usual. Working function is provided below for anyone interested.
Now I just have to converted the code to Access using Excel object.

Sub testjohn()
Dim intColumns As Integer
Dim intRows As Integer
Dim i As Integer

intColumns = Worksheets("Variables").Cells(2, 2) 'Column containing
last piece of data, in this case 46
intRows = Worksheets("Variables").Cells(2, 1) "Row containing last
question, in this case 41
For i = 2 To intRows 'Start question to finish question, in this
case start from row 2 and finish at row 41
'Enter function in cell (selected row, selected column -
formular range is from column 2 (46 + 1) back to column 47 (48 - 1))
Application.ActiveSheet.Cells(i, intColumns + 2).FormulaR1C1 =
"=COUNTIF(RC[-" & intColumns + 1 & "]:RC[-2],1)"
Next i 'Go to next row
End Sub

USING RANGE IS EVEN QUICKER METHOD :
Sub testjohnRange()
Dim intColumns As Integer
Dim intRows As Integer
Dim i As Integer

intColumns = Worksheets("Variables").Cells(2, 2) 'Column containing
last piece of data
intRows = Worksheets("Variables").Cells(2, 1)
With Worksheets("Responses")
.Range(.Cells(2, intColumns + 2), _
.Cells(intRows, intColumns + 2)).FormulaR1C1 =
"=COUNTIF(RC[-" & intColumns + 1 & "]:RC[-2],1)" 'Enter function in cell
range (row 2, column 49 to row 41, column 49)
End With

End Sub

John


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!