+ Reply to Thread
Results 1 to 9 of 9

Quicker way to enter formula

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dhruva101
    Yes. In fact I am looking for a Macro only to dit more nicely.
    the code would be something like
       Option Explicit
    
    Sub SpreadVLookup()
    Dim ws As Worksheet
    Dim iLastRow As Long
    For Each ws In ActiveWorkbook.Worksheets
        iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
        If iLastRow < 2 Then iLastRow = 2       ' testing
        ws.Range("B2:B" & iLastRow).Formula = "=IF(ISNA(VLOOKUP(C2,LOTHER,2,0)),""SR#NA"",VLOOKUP(C2,LOTHER,2,0))"
     ' more of these?  ws.Range("C2:C" & iLastRow).Formula = "=IF(ISNA(VLOOKUP(C2,LOTHER,2,0)),""SR#NA"",VLOOKUP(C2,LOTHER,2,0))"
        Range("B1:I" & iLastRow).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    Next ws
    End Sub
    but you will need to specify

    the column to determine the last row used
    what in the names range LOTHER

    which columns need to have a formula, and what formula for each column

    ---
    Si fractum non sit, noli id reficere.

  2. #2
    Registered User
    Join Date
    08-03-2006
    Posts
    60
    Thank you Bryan. I will try to change this macro to suit my needs.

  3. #3
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dhruva101
    Thank you Bryan. I will try to change this macro to suit my needs.
    ok - if you have problems with that just post here again, and thanks for the response.

    ---

+ 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