VBA help sheet code

[ottasight]

hi, why doesn't this work........thanks


Private Sub testit()
Dim a As Integer
Dim x As Integer
a = 25
For x = 1 To 5
Range("ax").FormulaR1C1 = a
Next x
End Sub

 

[Bieli]

Use
Range("A" & x).FormulaR1C1 = a
or
Cells(x, 1).FormulaR1C1 = a

 

[ottasight]

thanks ...will give it a try

 

[hiker95]

ottasight,

hi, why doesn't this work........thanks

Please explain in detail what the code should be doing, in what cells.

 

[ottasight]

Hi Bieli
for some reason code didn't work

Hiker95
code is place in vb sheet code rather than module. it should print 25 in each cell a1:a5.........thanks for any help

 

[ottasight]

sorry didn't fully explain, in sheet code when sheet is auto calculated 25 should appear in range a1:a5

 

[hiker95]

ottasight,


Sample worksheet before either of the two Worksheet_Calculate Events:

Excel Workbook
	A
1
2
3
4
5
6
Sheet1

After your macro code (slightly changed), and (after a macro with one line of code):

Excel Workbook
	A
1	25
2	25
3	25
4	25
5	25
6
Sheet1

You can only have one Worksheet_Calculate Event per worksheet.



Your looping code slightly changed:

Option Explicit
Private Sub Worksheet_Calculate()
' hiker95, 09/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=578637
Dim a As Long
Dim x As Long
a = 25
For x = 1 To 5
  Range("A" & x) = a
Next x
End Sub

And, a macro with one line of code:

Option Explicit
Private Sub Worksheet_Calculate()
' hiker95, 09/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=578637
Range("A1:A5") = 25
End Sub