+ Reply to Thread
Results 1 to 2 of 2

Looping through cells setting a formula

  1. #1
    Registered User
    Join Date
    03-24-2006
    Location
    Stafford, England
    MS-Off Ver
    Office 2003
    Posts
    70

    Looping through cells setting a formula

    Hi,

    I am trying to set a formula by looping through cells but am a bit stuck. I am using the following;

    For i = 5 To 10
    For j = "B" To "Z"
    Sheets("Infections").Range(j & i).Formula = "my formula"
    Next j
    Next i

    I get a type mismatch error on 'j'. Can someone point me in the right direction of how to solve this?

    Thanks in advance

    Jonathan

  2. #2
    Registered User
    Join Date
    03-24-2006
    Location
    Stafford, England
    MS-Off Ver
    Office 2003
    Posts
    70

    Re: Looping through cells setting a formula

    I have sorted the reference to the cell I want the formula in. I now have a problem of how to refer to a cell in a different excel workbook (using UNC path).

    I am trying to use

    For i = 5 To 10
    For j = 2 To 26
    Sheets("Infections").Cells(i, j).Formula = "='\\svr-filer01\group$\Weekly Management Reporting\Ward Governance Reporting\09-10\[Data Input.xls]Infections'!" & j & (inf + i - 5)
    Next j
    Next i

    I know the highlighted J is incorrect. How do I refer to a cell in a formula using R1C1?

    Jonathan
    Last edited by JonPugh; 01-08-2010 at 12:20 PM. Reason: Updated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1