Dynamically pasting function from Visual Basic to Excel cell

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi everyone

I haven't got very much experience with Excel programming and I am
stuck. I need to generate a spread sheet on the fly and dynamically
insert number of functions at various locations using Visual Basic
(locations depend on how many questions and responses are required by
user). When I tried to use the following method:

************************************************************
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-48]:RC[-1], 1)"
************************************************************
it worked fine

When I manually tried to use the counter as in:

************************************************************
Dim i as integer
i = 48
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-" & i & "]:RC[-1],1)"
************************************************************

it worked fine for each cell as a manually moved from cell to cell and
ran the procedure

However, as soon as tried to write a dynamic procedure as

************************************************************
Dim i as integer
For i = 2 To intNoRows 'Generate Functions
Application.ActiveSheet.Cells(intRows, intNoColumns +
2).FormulaR1C1 = "=COUNTIF(RC[-" & i & "]:RC[-1],1)"
Next i
************************************************************

OR


************************************************************
For i = 2 To intNoRows 'Generate Functions
Application.ActiveSheet.Cells(intRows, intNoColumns + 2) =
"=COUNTIF(RC[-" & i & "]:RC[-1],1)"
Next i 'I got rid of .FormulaR1C1 in this sample
************************************************************

the application generates

"Run time error ‘1004'
Application-defined or object-defined error"

I don't know what the error is or how to correct it.

Any help will be appreciated.
 
Well you have two variables

IntNoRows
and
IntRows

I suspect the second is mispelled and thus has now value, causing the error.

Although your code make no sense since you are putting the formula in the
same cell as "I" goes from 2 to IntNoRows.
 
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
 
Back
Top