Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

copy dynamic range to a row on the next tab

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
ealbrec
Asked:
ealbrec
  • 6
  • 5
1 Solution
 
game-masterCommented:


good morning!

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



game-master
0
 
ealbrecAuthor Commented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
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!

 
ealbrecAuthor Commented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
what about the Id? would that be picked up from Cell A1?

and how many rows you have to apply this to?
0
 
ealbrecAuthor Commented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
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
 
ealbrecAuthor Commented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
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
 
ealbrecAuthor Commented:
The solution addressed the question exactly as it was asked.  Thanks for the assistance!
0
 
ealbrecAuthor Commented:
Ardhendu Thanks, this works perfect!
0
 
Ardhendu SarangiSr. Project ManagerCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now