Modifiy a very small macro.. HELP!!
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Modifiy a very small macro.. HELP!!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi, It would be big help if anyone can help me with this problem

    Suppose in Cell A1:A30 (say) contains some date and with Cell A1: 25-Mar-02
    And in Cell B1 I have fomula:
    ='c:My Document[Valuation 25-Mar-02.xls]Ingenium'!$E$4

    NOTE: The file name has the date within to correspond with Cell A1.
    The problem here is suppose I have to fill the B1 formula to B2,B3....B30 etc in and ensuring the file name within each formula has the date correspond to the date in the same row in Column A

    Such as Cell A2: 26-Mar-02
    So Cell B2:='c:My Document[Valuation 26-Mar-02.xls]Ingenium'!$E$4

    SOMEONE has given me the solution via macro:

    For i = 1 To Range("A1")
    Range("B1").Offset(i-1, 0).Formula = "='c:My Document[Valuation " & Format(Range("A1").Offset(i-1, 0), "dd-mmm-yy") & "]Ingenium'!$E$4"
    Next i

    This solution works, but the loop seems endless,,, is there any way to stop the loop when reaching cell A30???

    Thanks for you reply!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do i = 1 to X

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Put 30 in cell A1
    Regards Tommy

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.Please Try this.

    Sub Test()
    Dim rng As Range
    For Each rng In Columns(1).SpecialCells(xlCellTypeConstants)
    rng.Offset(, 1).Formula = _
    "='C:My Document[Valuation " & rng.Text & ".xls]Ingenium'!R4C5"
    Next
    End Sub

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    such a big help.. thanks very much

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com