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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Pasting a value into multiple cell addresses
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 06:47 PM |
Multiple SUMIF Statements | Gary Thomson | Worksheet Functions | 4 | October 28th, 2003 11:16 AM |
charting same cell across multiple tabs | Tushar Mehta | Charts and Charting | 1 | October 16th, 2003 11:51 AM |