Writing data to Specific Cells???

[John]

Hi Folks,

I'm trying to write data from cell calculation which updates automatically
as part of a FOR loop into a specific range of cells.

I would like to be able to read in a numeric value from a cell and loop
round that number writing to cells M1 to M100 or so but all I can get is the
data writing accross the row rather than down the col???

Here's what I have so far! Any Help Appreciated

Private Sub CommandButton1_Click()
Dim myRange As Range
Dim myrange2 As Range

Set myRange = Worksheets("Sheet1").Range("A110")
Set myrange2 = Worksheets("Sheet1").Range("M1:M100")

For i = 1 To 100
With Worksheets("Sheet1").Cells(114, i)
If .Value = "" Then .Value = myRange
End With
Application.SendKeys (F9)
Next i
End Sub


I was playing around with using myrange2 but had no luck :/

Cheers

John

 

[John]

wow bob that was fast )))

I'll give that a try

Thx

 

[John]

didn't work bob

John,

Try

Private Sub CommandButton1_Click()
Dim myRange As Range
Dim myrange2 As Range
Dim cell As Range

Set myRange = Worksheets("Sheet1").Range("A110")
Set myrange2 = Worksheets("Sheet1").Range("M1:M100")

For Each cell In myrange2
If .Value = "" Then .Value = myRange
Next cel
End Sub


--

HTH

Bob Phillips

 

[J.E. McGimpsey]

(Followup set to microsoft.public.excel.misc)

That's about as unhelpful a response as you could make, isn't it?
Are we supposed to be able to decipher from that what "didn't work"
means?

While I don't understand what you're trying to do (what does "loop
round that number" mean?), with a bit more explanation, I'm sure
someone could help you tweak it in.

Also, cross-posting to so many newsgroups is *really* unneccessary -
what does .templates have to do with your question? or WORD.vba?????
Most regulars read at least .excel.misc, .excel.programming and
..excel.worksheet.functions, so you only need to post in one.
..excel.programming would have been the most appropriate, but since
you didn't include it the first time, I'm setting followup to .misc.

See

http://www.cpearson.com/excel/

for more tips on how to use these groups effectively.

 

[Dave Peterson]

What didn't work?

maybe you have to calculate again:

Private Sub CommandButton1_Click()
Dim myRange As Range
Dim myrange2 As Range
Dim cell As Range

Set myRange = Worksheets("Sheet1").Range("A110")
Set myrange2 = Worksheets("Sheet1").Range("M1:M100")

For Each cell In myrange2
If .Value = "" Then .Value = myRange
application.calculate
Next cel
End Sub

 

[Dan E]

Give this A try,

Private Sub CommandButton1_Click()
Dim myRange As Range
Dim myrange2 As Range

Set myRange = Worksheets("Sheet1").Range("A110")
Set myrange2 = Worksheets("Sheet1").Range("M1:B100")

For i = 1 To 100
With Worksheets("Sheet1").Range("M" & i)
If .Value = "" Then .Value = myRange.Value
End With
Application.SendKeys (F9)
Next i
End Sub

Dan E

 

[Dan E]

My Bad

Private Sub CommandButton1_Click()
Dim myRange As Range
Dim myrange2 As Range

Set myRange = Worksheets("Sheet1").Range("A110")

For i = 1 To 100
With Worksheets("Sheet1").Range("M" & i)
If .Value = "" Then .Value = myRange.Value
End With
Application.SendKeys (F9)
Next i
End Sub

Dan E

 

[Dana DeLouis]

I don't know what didn't work either. Perhaps just another general idea...

Sub Demo()
Dim x
x = Worksheets("Sheet1").Range("A110")
On Error Resume Next
Worksheets("Sheet1").Range("M1:M100").SpecialCells(xlCellTypeBlanks) = x
End Sub

 

[Tom Ogilvy]

This goes across the columns from 1 to 100 in row 114

With Worksheets("Sheet1").Cells(114, i)

That is why you are writing across the columns

You never use myRange2 (column M). What are you actually trying to do?
Bob assumed you wanted to substitute A114:CV115 with M1:M100

Private Sub CommandButton1_Click()
Dim myRange As Range
Dim myrange2 As Range
Dim cell As Range

Set myRange = Worksheets("Sheet1").Range("A110")
Set myrange2 = Worksheets("Sheet1").Range("M1:M100")

For Each cell In myrange2
If .Value = "" Then .Value = myRange
Application.Calculate ' add this line
Next cel
End Sub

Basically does that.
However, unless A110 has a formula that includes some type of random number
generator or is dependent in some way on the values in Column M, then you
would get the same value each time.

Regards,
Tom Ogilvy