?
Solved

copy dynamic range to a row on the next tab

Posted on 2010-01-11
12
Medium Priority
?
287 Views
Last Modified: 2012-06-27
I have a range of cells on tab "Sample Size Calc".  This is a dynamically generated list of the workingdays for the month.  I would like to copy them into a formula on another sheet so that the cell yields the id number and the date.

Start:
1/1/2010 (list starts in cell I9)
1/4/2010
1/5/2010
1/6/2010
1/7/2010
1/8/2010
1/11/2010
1/12/2010
1/13/2010
1/14/2010
1/15/2010
1/18/2010
1/19/2010
1/20/2010
1/21/2010
1/22/2010
1/25/2010
1/26/2010
1/27/2010
1/28/2010
1/29/2010

Finish: J1 = Cell J1 = id number (9) plus date
J1                    J2
9 1/1/2010      25 1/2/2010  

etc.....to dynamically generated end of month date, so the number of rows above and the number of columns here are dynamic depending on the last workday of the month.

I have been playing with the following code, but it need more information to do what I described.

For Each cel In Range(Cells(1, 10), Cells(1, WDays))

    cel.Formula = "='Sample Size Calc'!" & Range("I9").Offset(1+1, 0).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    Next
   
Next

Any assistance would be greatly appreciated.  Thanks.


0
Comment
Question by:ealbrec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 13

Expert Comment

by:game-master
ID: 26289922


good morning!

can u please attach sample excel file for us to further analyze it..



game-master
0
 

Author Comment

by:ealbrec
ID: 26293859
Here is a file containing the code I am trying to modify.  When you run the macro, it demonstrates what I am trying to do, but the date does not update correctly.  I know this is probably a pretty simple question, so hopefully I can learn something in the process.  Thanks for the help!
Code-Snipet2.xls
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26294663
Hi,

I used the following formula...

=IF('Sample Size Calc'!I9<>"", 'Cap Man'!$A$1& " " &TEXT('Sample Size Calc'!I9, "mm/dd/yyyy")," ")

and i got the following result as shown in the attached file.

Can you confirm if this is what u need?

- Ardhendu
Code-Snipet2.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ealbrec
ID: 26294759
That would be one way to do it, but I was hoping to use the macro because Sample Size Calc.Rang(I9:I29) is dynamic.  The length of the range depends on the value in the Month (I2) and Year (I3) cells.  Otherwise, will have this formula hanging out there at the far right of the spreadsheet.  Thanks.
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26294784
what about the Id? would that be picked up from Cell A1?

and how many rows you have to apply this to?
0
 

Author Comment

by:ealbrec
ID: 26294912
The Id needs to be picked up from Cell A1.  It really only needs to be done for the first frow and then that can be copied to fill down the spreadsheet for the rest of the rows.  There is a mcro started in the workbook, but I am a novice and could not figure out the code.  Ardhendu's solution will get me where I need to be, but I would like to learn where I went wrong.  
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26296310
Hi Ealbrec,

I looked at your code and it seemed to me there was a lot of extra lines of code in it. Here's the cleaned up code which worked for me.

Can you try this once?

Thanks,
Ardhendu
Sub CreateInitialCalendar()

Dim rgref As Range
Dim i As Long
Dim r As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Worksheets("Sample Size Calc")
Set ws2 = ActiveWorkbook.Worksheets("Cap Man")

Application.DisplayAlerts = False
ws1.Activate
WDays = ws1.Range("I7")
ws1.Range("I10:I39").ClearContents
ws1.Range("I10:I39").NumberFormat = "mm/dd/yyyy"
ws1.Range("I10") = "=IF(WEEKDAY(R[-1]C)=7,R[-1]C+2,IF(WEEKDAY(R[-1]C)=6,R[-1]C+3,R[-1]C+1))"
ws1.Range("I10").AutoFill Destination:=Range("I10 : I" & WDays + 8)

ws2.Activate
ws2.Range("J1:IV1").ClearContents
r = 10
For i = 8 To WDays + 7
    ws2.Cells(1, r).Value = ws2.Range("A1") & " " & ws1.Cells(i + 1, 9)
    'MsgBox (cel.Value)
    r = r + 1
Next i
Application.DisplayAlerts = True
End Sub

Open in new window

Code-Snipet2.xls
0
 

Author Comment

by:ealbrec
ID: 26297208
This works very good!  One final thing though, the line of code below.....Is there a way to write the formula so that A1 is only a reference to the cell A1.  That way if the next thing I do is autofill cells J1:IV down the page, the formula will update to A2, A3, A4 etc?

ws2.Cells(1, r).Value = ws2.Range("A1") & " " & ws1.Cells(i + 1, 9)

Looks great though!  Thanks.
0
 
LVL 20

Accepted Solution

by:
Ardhendu Sarangi earned 2000 total points
ID: 26297375
Hi,
That is quite easy. Here you go!

- Ardhendu
Option Explicit
Sub CreateInitialCalendar()
Dim i As Long, r As Integer, n As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim WDays As Integer


Set ws1 = ActiveWorkbook.Worksheets("Sample Size Calc")
Set ws2 = ActiveWorkbook.Worksheets("Cap Man")

Application.DisplayAlerts = False
ws1.Activate
WDays = ws1.Range("I7")
ws1.Range("I10:I39").ClearContents
ws1.Range("I10:I39").NumberFormat = "mm/dd/yyyy"
ws1.Range("I10") = "=IF(WEEKDAY(R[-1]C)=7,R[-1]C+2,IF(WEEKDAY(R[-1]C)=6,R[-1]C+3,R[-1]C+1))"
ws1.Range("I10").AutoFill Destination:=Range("I10 : I" & WDays + 8)

ws2.Activate
ws2.Range("J1:IV10").ClearContents

For n = 1 To ws2.Cells(65536, "A").End(xlUp).Row
r = 10
    For i = 8 To WDays + 7
        ws2.Cells(n, r).Value = ws2.Range("A" & n) & " " & ws1.Cells(i + 1, 9)
        'MsgBox (cel.Value)
        r = r + 1
    Next i
Next n
Application.DisplayAlerts = True
End Sub

Open in new window

Code-Snipet2.xls
0
 

Author Closing Comment

by:ealbrec
ID: 31675628
The solution addressed the question exactly as it was asked.  Thanks for the assistance!
0
 

Author Comment

by:ealbrec
ID: 26297472
Ardhendu Thanks, this works perfect!
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26299484
Author Comments:
The solution addressed the question exactly as it was asked. Thanks for the assistance!


Hi,

Thanks a lot for the grade!

Glad I could help out...

- Ardhendu
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question