A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Pasting a value into multiple cell addresses



 
 
Thread Tools Display Modes
  #1  
Old June 29th, 2004, 02:12 PM
hmws
external usenet poster
 
Posts: n/a
Default Pasting a value into multiple cell addresses

I have a range of cells (a3,c3,e3,a12,c12 etc) containing
variable cell references.

I would like to be able to go to/lookup a Target
Worksheet and enter the date in that cell.

The formula below returns now() in my target sheet.

sub enterdate()

Application.Goto Worksheets("Target") _
.Range(Worksheets("Source").Range("a3").Value), True
ActiveCell = Now()

End sub

Is there a way to speed this up by doing the other cells
in the source worksheet (ie c3,e3,a12,c12 etc) at the
same time?

Thank you
  #4  
Old June 29th, 2004, 03:49 PM
hmws
external usenet poster
 
Posts: n/a
Default Pasting a value into multiple cell addresses

Thanks Don for your incredibly quick response but I think
I may have phrased the question poorly.

The contents of cell "a3" return a variable cell address
depending on selection. I want to lookup/goto the result
in a3 in this case it is returning $m$19 and enter the
date in cell M19 in the target worksheet (cell A3 is in
the source worksheet).

The code I have written does this ok but one cell only.

Your code overwrites the original formulas with the date
in cells a3,c3,e3.

I'm sure it is possible to do but I am struggling.

However once again thanks for your response.
-----Original Message-----
try this without even going to the worksheet

Worksheets("Source").Range("a3,c3,e3") = Now()

--
Don Guillett
SalesAid Software

"hmws" wrote in

message
...
I have a range of cells (a3,c3,e3,a12,c12 etc)

containing
variable cell references.

I would like to be able to go to/lookup a Target
Worksheet and enter the date in that cell.

The formula below returns now() in my target sheet.

sub enterdate()

Application.Goto Worksheets("Target") _
.Range(Worksheets("Source").Range("a3").Value),

True
ActiveCell = Now()

End sub

Is there a way to speed this up by doing the other

cells
in the source worksheet (ie c3,e3,a12,c12 etc) at the
same time?

Thank you



.

  #5  
Old June 29th, 2004, 05:57 PM
hmws
external usenet poster
 
Posts: n/a
Default Pasting a value into multiple cell addresses

Once again many thanks, but I can see the code isn't
going to do what I want.

The user selects a name from a drop down list in the
Source Worksheet. On selecting that name the formula in
cell A3 returns a reference to a cell address, in this
case M7. (If they select a different name it could return
anything from M1:M500)

I want to then go to cell M7 in worksheet (Target) and
enter now(). The code I entered in my original question
works perfectly for the contents of cell A3.

I can repeat it 21 times and know that would do exactly
what I want but I just think there must be an easier way.

Thanks very much for your time.


-----Original Message-----
OR,
Sub fillinranges()
With Worksheets("Sheet23").Range("a3,c3,e3")
..Value = Now()
..NumberFormat = "mm/dd/yyyy"
End With
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this without even going to the worksheet

Worksheets("Source").Range("a3,c3,e3") = Now()

--
Don Guillett
SalesAid Software

"hmws" wrote in

message
...
I have a range of cells (a3,c3,e3,a12,c12 etc)

containing
variable cell references.

I would like to be able to go to/lookup a Target
Worksheet and enter the date in that cell.

The formula below returns now() in my target sheet.

sub enterdate()

Application.Goto Worksheets("Target") _
.Range(Worksheets("Source").Range("a3").Value),

True
ActiveCell = Now()

End sub

Is there a way to speed this up by doing the other

cells
in the source worksheet (ie c3,e3,a12,c12 etc) at the
same time?

Thank you





.

  #6  
Old June 29th, 2004, 06:06 PM
Bernie Deitrick
external usenet poster
 
Posts: n/a
Default Pasting a value into multiple cell addresses

hmws,

Sub EnterDate2()
Dim myRange As Range
Dim myCell As Range

Set myRange = Worksheets("Source").Range("a3,c3,e3,a12,c12")

For Each myCell In myRange
Worksheets("Target").Range(myCell.Value).Value = Now()
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

"hmws" wrote in message
...
I have a range of cells (a3,c3,e3,a12,c12 etc) containing
variable cell references.

I would like to be able to go to/lookup a Target
Worksheet and enter the date in that cell.

The formula below returns now() in my target sheet.

sub enterdate()

Application.Goto Worksheets("Target") _
.Range(Worksheets("Source").Range("a3").Value), True
ActiveCell = Now()

End sub

Is there a way to speed this up by doing the other cells
in the source worksheet (ie c3,e3,a12,c12 etc) at the
same time?

Thank you



  #7  
Old June 30th, 2004, 12:49 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default Pasting a value into multiple cell addresses

Something like this????

Option Explicit
Sub enterDate()

Dim testRng As Range
Dim myVal As Variant

myVal = Worksheets("source").Range("a3").Value

Set testRng = Nothing
On Error Resume Next
Set testRng = Worksheets("target").Range(myVal)
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
With testRng(1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End Sub



hmws wrote:

Once again many thanks, but I can see the code isn't
going to do what I want.

The user selects a name from a drop down list in the
Source Worksheet. On selecting that name the formula in
cell A3 returns a reference to a cell address, in this
case M7. (If they select a different name it could return
anything from M1:M500)

I want to then go to cell M7 in worksheet (Target) and
enter now(). The code I entered in my original question
works perfectly for the contents of cell A3.

I can repeat it 21 times and know that would do exactly
what I want but I just think there must be an easier way.

Thanks very much for your time.

-----Original Message-----
OR,
Sub fillinranges()
With Worksheets("Sheet23").Range("a3,c3,e3")
..Value = Now()
..NumberFormat = "mm/dd/yyyy"
End With
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this without even going to the worksheet

Worksheets("Source").Range("a3,c3,e3") = Now()

--
Don Guillett
SalesAid Software

"hmws" wrote in

message
...
I have a range of cells (a3,c3,e3,a12,c12 etc)

containing
variable cell references.

I would like to be able to go to/lookup a Target
Worksheet and enter the date in that cell.

The formula below returns now() in my target sheet.

sub enterdate()

Application.Goto Worksheets("Target") _
.Range(Worksheets("Source").Range("a3").Value),

True
ActiveCell = Now()

End sub

Is there a way to speed this up by doing the other

cells
in the source worksheet (ie c3,e3,a12,c12 etc) at the
same time?

Thank you




.


--

Dave Peterson

  #8  
Old June 30th, 2004, 08:40 AM
external usenet poster
 
Posts: n/a
Default Pasting a value into multiple cell addresses

Bernie

Thanks, exactly what I was looking for - it would have
taken me years to come up with that!

Once again thanks a lot
-----Original Message-----
hmws,

Sub EnterDate2()
Dim myRange As Range
Dim myCell As Range

Set myRange = Worksheets("Source").Range

("a3,c3,e3,a12,c12")

For Each myCell In myRange
Worksheets("Target").Range(myCell.Value).Value = Now

()
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

"hmws" wrote in

message
...
I have a range of cells (a3,c3,e3,a12,c12 etc)

containing
variable cell references.

I would like to be able to go to/lookup a Target
Worksheet and enter the date in that cell.

The formula below returns now() in my target sheet.

sub enterdate()

Application.Goto Worksheets("Target") _
.Range(Worksheets("Source").Range("a3").Value),

True
ActiveCell = Now()

End sub

Is there a way to speed this up by doing the other

cells
in the source worksheet (ie c3,e3,a12,c12 etc) at the
same time?

Thank you



.

  #9  
Old June 30th, 2004, 01:55 PM
Bernie Deitrick
external usenet poster
 
Posts: n/a
Default Pasting a value into multiple cell addresses

You're welcome. That's why we're here!

Thanks, exactly what I was looking for - it would have
taken me years to come up with that!



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Joining tables to recognize multiple addresses JOsmon Database Design 2 June 1st, 2004 12:03 AM
How to combine email addresses in one cell? Amit Worksheet Functions 1 March 3rd, 2004 07:47 PM
Multiple SUMIF Statements Gary Thomson Worksheet Functions 4 October 28th, 2003 12:16 PM
charting same cell across multiple tabs Tushar Mehta Charts and Charting 1 October 16th, 2003 11:51 AM


All times are GMT +1. The time now is 09:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.