I am trying to make a formula in Excel 2007 that uses a cell which is part of a

Question

I am trying to make a formula in Excel 2007 that uses a cell which is part of a macro and it is showing an error message.

Hi,

I am using Excel 2007 to create a list of values with a macro (which currently works and populates a column in a workbook with a list of values which change when the macro is applied). What I would like to do is create a function in the next column over that performs a VLOOKUP function for the value in said macro-determined cell and searches in another tab of that workbook for a value in the same row as the one that has the value from the macro in its first column. The problem is that when I run the macro, the formula shows an error message (#REF!) in the place where the lookup value (the cell within the macro-driven column) should be. I would greatly appreciate any help in figuring out if it is at all possible to create a formula using a cell that is generated by a macro and, if so, how this problem can be avoided. Here is the code for the macro in question, in case this helps. Thank you very much!

Sub NotDoingButton()
'
' Macro10 Macro
'
    Application.ScreenUpdating = False
    Sheets("Business Assessment").Select
    Range("M2:M2000").Select
    Selection.Copy
    Sheets("Indicators").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("A:A").Select
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    'Alphabetize
     Range("A2:A200").Select
    ActiveWorkbook.Worksheets("Indicators").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Indicators").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Indicators").Sort
        .SetRange Range("A2:A200")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  
End Sub

Explanation / Answer

Make a copy of your workbook to test with and replace the current NotDoingButton() code with the code below. I've shown in it where I added the code to rebuild your VLOOKUP() formulas. Note that I only have it rebuild them in columns G:J for as far down the sheet as there are entries in column A.

You will see that the formulas now appear with an absolute reference to column A on the Indicators sheet; what used to appear as

=VLOOKUP(A2,'Service Providers'!$A$3:$E$163,2,FALSE)

will now appear as

=VLOOKUP($A2,'Service Providers'!$A$3:$E$163,2,FALSE)

It was just quicker to build up the code using the absolute reference - no difference in actual functionality as far as you're concerned.

Sub NotDoingButton()
'
    Application.ScreenUpdating = False
    Sheets("Business Assessment").Select
    Range("M2:M2000").Select
    Selection.Copy
    Sheets("Indicators").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("A:A").Select
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    'Alphabetize
     Range("A2:A200").Select
    ActiveWorkbook.Worksheets("Indicators").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Indicators").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Indicators").Sort
        .SetRange Range("A2:A200")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'added to rebuild the VLOOKUP() formulas in columns G:L
Dim lastRow As Long
' the Indicators sheet should be the active sheet, but just to make sure...
With ActiveWorkbook.Worksheets("Indicators")
    'find out how far down column A values are actually provided
    lastRow = .Range("A" & Rows.Count).End(xlUp).Row

'limit this to row 200 since that is a stated limit earlier

    If lastRow > 200 Then

       lastRow = 200

    End If

    'rebuild column G formulas
    .Range("G2:G" & lastRow).FormulaR1C1 = _
     "=VLOOKUP(RC1,'Service Providers'!R3C1:R163C5,2,FALSE)"
    'rebuild column H formulas
    .Range("H2:H" & lastRow).FormulaR1C1 = _
     "=VLOOKUP(RC1,'Service Providers'!R3C1:R163C5,3,FALSE)"
    'rebuild column I formulas
    .Range("I2:I" & lastRow).FormulaR1C1 = _
     "=VLOOKUP(RC1,'Service Providers'!R3C1:R163C5,4,FALSE)"
    'rebuild column J formulas
    .Range("J2:J" & lastRow).FormulaR1C1 = _
     "=VLOOKUP(RC1,'Service Providers'!R3C1:R163C5,5,FALSE)"
End With

End Sub

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.