Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Code (VBA)

    I have a many files that have codes (defined range - CodeList) in the first row. I have another file (Codes2.xls) with the codes in the column A (defined range - CodeList) and the titles for those codes in column D (defined range - titlelist). I have code that opens a file copies and formats some data but I want to replace the codes with the titles in the Codes2.xls file. I guess I need some type of lookup that loops thru the CodesList range.

    Please Help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Code (VBA)

    You could use formulas, and record your actions in a macro, then edit it to make it more general.

    Or something like this (I haven't actually tested it, it may need to be adapted)

    Dim rngCodeTitles As Range
    Dim rngCodes As Range
    Dim oCell As Range
    Dim varVal As Variant

    Set rngCodeTitles = Workbooks("Codes2.xls").Worksheets("Sheet1").Range ("CodeList")
    Set rngCodes = ActiveWorkbook.Worksheets("Sheet1").Range("CodeLis t")

    For Each oCell In rngCodes
    varVal = oCell.Value
    oCell.Value = Application.WorksheetFunction.VLookup(varVal, rngCodeTitles, 4, False)
    Next oCell

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Code (VBA)

    This works, thank you

    But now I want to do a lookup in the Codes2.xls (range - Unitslist) and insert into the cell below the from the Codes2.xls. Then perform the replace code

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Code (VBA)

    Can you post a small demo workbook to illustrate what you want? I don't understand what you mean by "insert into the cell below the from the Codes2.xls. Then perform the replace code"

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Code (VBA)

    Row 1:1 is already defined as CodeList. I copy and paste twice so I can do three looksup. I can't figure out how to define the range name because the number of codes is different from sheet to sheet.

    Thanks for help

    Rows("1:1").Select
    Selection.Copy
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Names.Add Name:="Units", RefersToR1C1:="=Sheet1!R2C2:R2C11" *****Needs work here because different number of codes each time

    Rows("1:1").Select
    Selection.Copy
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Range("B3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Names.Add Name:="Method", RefersToR1C1:="=Sheet1!R3C2:R3C11" *****Needs work here

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Test"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Units"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Method"

    Set rngCodes = ActiveWorkbook.Worksheets("Sheet1").Range("CodeLis t")
    For Each oCell In rngCodes
    varVal = oCell.Value
    oCell.Value = Application.WorksheetFunction.VLookup(varVal, rngCodeTitles, 4, False)
    Next oCell

    Set rngCodes = ActiveWorkbook.Worksheets("Sheet1").Range("Units")
    For Each oCell In rngCodes
    varVal = oCell.Value
    oCell.Value = Application.WorksheetFunction.VLookup(varVal, rngCodeTitles, 5, False)
    Next oCell

    Set rngCodes = ActiveWorkbook.Worksheets("Sheet1").Range("Method" )
    For Each oCell In rngCodes
    varVal = oCell.Value
    oCell.Value = Application.WorksheetFunction.VLookup(varVal, rngCodeTitles, 6, False)
    Next oCell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Code (VBA)

    There is a lot to be said for not selecting ranges in macros if it can be avoided, but I don't have time now to work out a version of your macro without doing that.

    Here is how you can define the first name: instead of the code beginning with Range("B2").Select, use

    (Declarations:
    Dim oCell As Range
    Dim rngCodes As Range)

    Set oCell = Range("B2")
    Set rngCodes = Range(oCell, oCell.End(xlToRight))
    ' In fact, you have rngCodes now, so perhaps you don't need the name. But if you do:
    ActiveWorbook.Names.Add Name:="Units", RefersToR1C1:="=" & rngCodes.Address(ReferenceStyle:=xlR1C1)

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Code (VBA)

    Thanks for all the help, the result

    Rows("1:1").Select
    Selection.Copy
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Rows("1:1").Select
    Selection.Copy
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False

    Set oCell = Range("B1")
    Set rngCodes = Range(oCell, oCell.End(xlToRight))
    For Each oCell In rngCodes
    varVal = oCell.Value
    oCell.Value = Application.WorksheetFunction.VLookup(varVal, rngCodeTitles, 4, False)
    Next oCell

    Set oCell = Range("B2")
    Set rngCodes = Range(oCell, oCell.End(xlToRight))
    For Each oCell In rngCodes
    varVal = oCell.Value
    oCell.Value = Application.WorksheetFunction.VLookup(varVal, rngCodeTitles, 5, False)
    Next oCell

    Set oCell = Range("B3")
    Set rngCodes = Range(oCell, oCell.End(xlToRight))
    For Each oCell In rngCodes
    varVal = oCell.Value
    oCell.Value = Application.WorksheetFunction.VLookup(varVal, rngCodeTitles, 6, False)
    Next oCell

Posting Permissions

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