Writing data to Specific Cells???

Tutorials Win: Microsoft Windows Forum

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
Top

Re: Writing data to Specific Cells??? by John

John
Fri Jul 04 13:56:24 CDT 2003

wow bob that was fast :))))

I'll give that a try :)

Thx
"Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
news:#Mrro1lQDHA.3664@tk2msftngp13.phx.gbl...
> 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
>
> "John" <johnnybhoy67@excite.com> wrote in message
> news:#7x#axlQDHA.3088@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> >
>
>


Top

Re: Writing data to Specific Cells??? by John

John
Fri Jul 04 14:28:55 CDT 2003

didn't work bob :(


"Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
news:#Mrro1lQDHA.3664@tk2msftngp13.phx.gbl...
> 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
>
> "John" <johnnybhoy67@excite.com> wrote in message
> news:#7x#axlQDHA.3088@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> >
>
>


Top

Re: Writing data to Specific Cells??? by Dave

Dave
Fri Jul 04 14:40:53 CDT 2003

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

John wrote:
>
> didn't work bob :(
>
> "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> news:#Mrro1lQDHA.3664@tk2msftngp13.phx.gbl...
> > 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
> >
> > "John" <johnnybhoy67@excite.com> wrote in message
> > news:#7x#axlQDHA.3088@TK2MSFTNGP10.phx.gbl...
> > > 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
> > >
> > >
> >
> >

--

Dave Peterson
ec35720@msn.com
Top

Re: Writing data to Specific Cells??? by Dan

Dan
Fri Jul 04 14:41:58 CDT 2003

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

"John" <johnnybhoy67@excite.com> wrote in message
news:ew6WDKmQDHA.1684@TK2MSFTNGP12.phx.gbl...
> didn't work bob :(
>
>
> "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> news:#Mrro1lQDHA.3664@tk2msftngp13.phx.gbl...
> > 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
> >
> > "John" <johnnybhoy67@excite.com> wrote in message
> > news:#7x#axlQDHA.3088@TK2MSFTNGP10.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>


Top

Re: Writing data to Specific Cells??? by Dan

Dan
Fri Jul 04 14:46:00 CDT 2003

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

"Dan E" <nospam.d.elliot@nospam.mailcity.com> wrote in message
news:#5mgWRmQDHA.2224@TK2MSFTNGP12.phx.gbl...
> 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
>
> "John" <johnnybhoy67@excite.com> wrote in message
> news:ew6WDKmQDHA.1684@TK2MSFTNGP12.phx.gbl...
> > didn't work bob :(
> >
> >
> > "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> > news:#Mrro1lQDHA.3664@tk2msftngp13.phx.gbl...
> > > 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
> > >
> > > "John" <johnnybhoy67@excite.com> wrote in message
> > > news:#7x#axlQDHA.3088@TK2MSFTNGP10.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Top

Re: Writing data to Specific Cells??? by J

J
Fri Jul 04 14:40:25 CDT 2003

(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.



In article <ew6WDKmQDHA.1684@TK2MSFTNGP12.phx.gbl>,
"John" <johnnybhoy67@excite.com> wrote:

> didn't work bob :(
Top

Re: Writing data to Specific Cells??? by Dana

Dana
Fri Jul 04 15:07:32 CDT 2003

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

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dave Peterson" <ec35720@msn.com> wrote in message
news:3F05D845.11AED0AC@msn.com...
> 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
>
> John wrote:
> >
> > didn't work bob :(
> >
> > "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> > news:#Mrro1lQDHA.3664@tk2msftngp13.phx.gbl...
> > > 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
> > >
> > > "John" <johnnybhoy67@excite.com> wrote in message
> > > news:#7x#axlQDHA.3088@TK2MSFTNGP10.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
>
> --
>
> Dave Peterson
> ec35720@msn.com


Top

Re: Writing data to Specific Cells??? by Tom

Tom
Fri Jul 04 15:53:38 CDT 2003

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

John <johnnybhoy67@excite.com> wrote in message
news:#7x#axlQDHA.3088@TK2MSFTNGP10.phx.gbl...
> 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
>
>


Top

Re: Writing data to Specific Cells??? by Helmut

Helmut
Sat Jul 05 01:09:23 CDT 2003

Hi John,

Sub WriteToCell(t%, r%, c%, s$)
ActiveDocument.Tables(t).Cell(r, c).Range.Text = s
End Sub
Sub aTest()
WriteToCell 1, 1, 1, "Test2"
End Sub

"t" is a table
"r" is a row
"c" is a column
"s" is a string
I think, the loop isn't a challenge either.
(Word 97).
Greetings from Bavaria, Germany
Helmut Weber

Top