• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

Insert a variable in Vlookup and automate Vlookup with a macro

Hello all

In the attached file I want to do the following;

1. create an input box
2. have the input box pass a variable ( the sheet name) into a Vlookup formula
    In this case the sheet name is Feb Data, but that will change month to month
    The Vlookup formula starts is cell F9 of sheet 82239 - OEM
3. have Vlookup run starting in cell F9 and filldown.
4. remove the Vlookup formula and leave its output.

Note: don't care about the totals in the file

Any help would be appreciated. Test.xls
0
pyramid
Asked:
pyramid
  • 3
  • 2
1 Solution
 
nutschCommented:
Here is a code that will do it, after you do a copy / paste values on your existing vlookups.

Thomas


Sub Macro5()
    Dim strSheet As String, sht As Worksheet, rg As Range
    
    strSheet = InputBox("Enter new sheet name")
    
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name = strSheet Then GoTo foundSheet
    Next sht
    
    MsgBox "sheet doesn't exist"
    Exit Sub
    
foundSheet:
    
    Set sht = Sheets(strSheet)

On Error Resume Next

    Set rg = Range("F9:F" & Rows.Count).SpecialCells(xlCellTypeConstants, xlNumbers)
       
    If Err <> 0 Then
        MsgBox "no cells to replace"
        Exit Sub
    End If
    
    rg.FormulaR1C1 = _
        "=VLOOKUP(RC[-3],'" & strSheet & "'!R[-5]C[10]:R65536C17,2,FALSE)"
    
    rg.Value = rg.Value
    
End Sub

Open in new window

0
 
pyramidAuthor Commented:
Hi Thomas, thanks for this

I am getting the no cells to replace error from your code so I am assuming I am doing something wrong probably related to the cut/paste values you taking about. Can you provide more detail? Do I need to have something in column F?

thanks

P.
0
 
pyramidAuthor Commented:
Hi Again

So I put values in each cell = to 0 Dollars. I created a button and assigned your code to a macro. I am getting alot of NA errors in cells that I know I should have data. Any suggestions? I attached another file so you can see what I mean.

thanks again
P.
Test2.xls
0
 
nutschCommented:
Yes, the initial vlookup formula didn't have absolute addressing on the lookup range.

Here is a correction that should address that.


Option Explicit

Sub VlookupMaster()
    Dim strSheet As String, sht As Worksheet, rg As Range
    
    strSheet = InputBox("Enter new sheet name")
    
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name = strSheet Then GoTo foundSheet
    Next sht
    
    MsgBox "sheet doesn't exist"
    Exit Sub
    
foundSheet:
    
    Set sht = Sheets(strSheet)

On Error Resume Next

    Set rg = Range("F9:F" & Rows.Count).SpecialCells(xlCellTypeConstants)
       
    If Err <> 0 Then
        MsgBox "no cells to replace"
        Exit Sub
    End If
    
    rg.FormulaR1C1 = _
        "=VLOOKUP(RC[-3],'" & strSheet & "'!R4C16:R65536C17,2,FALSE)"
    
    rg.Value = rg.Value
    
End Sub

Open in new window

0
 
pyramidAuthor Commented:
Thomas
It works thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now