You are here:

Excel/Excel 2007 Windows Formula

Advertisement


Question
im about a 5 on the scale.

I have a workbook that has two worksheets.  What I am looking to do is this.

Part 1

Worksheet 1 in G2 I want it to look at the # in A2 go to worksheet 2 column L and search the entire column for an exact match.  If there is a match I want it to take the date in Column N of the same row and return that date to G2 on worksheet 1.  If no match then do nothing.  I then want to copy that formula down to all of column G

Part 2

In K2 of worksheet 1 look at the # in A2.
Go to column L of worksheet 2 and search the entire column for a match.  If there is a match then look at column H of the same row and if the letters ALS appear in the text then return the letter M to K2.  If there is no match then return the letter R to K2.

Thanks so much for any help you can give.
In K2 of

Answer
Hi Rick,

I apologize for taking so long to answer your question. I had to work on it pieces at a time complete it.  Here is code that I believe does what you want.  You didn't mention autofilling the formula in K2 down (Part 2), but I assumed you wanted the code to do that as well.  However I annotated where this is in the code so you can comment it out or delete it if you don't want it.

Sub Matching()

  'Part 1

'  Worksheet 1 in G2 I want it to look at the # in A2 go to worksheet 2
'  column L and search the entire column for an exact match.  If there
'  is a match I want it to take the date in Column N of the same row
'  and return that date to G2 on worksheet 1.  If no match then do
'  nothing.  Then copy that formula down to all of column G

  Worksheets(1).Activate
  Range("G2").Formula = "=IF(ISNA(VLOOKUP(A2,Sheet2!L$1:N$30,3,FALSE)),"""",VLOOKUP(A2,Sheet2!L$1:N$30,3,FALSE))"
  'now copy formula down
  Dim LastRow       As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("G2").AutoFill Destination:=Range("G2", Cells(LastRow, "G"))
  
  'Part 2

'  In K2 of worksheet 1 look at the # in A2.
'  Go to column L of worksheet 2 and search the entire column for a match.
'  If there is a match then look at column H of the same row and if the
'  letters ALS appear in the text then return the letter M to K2.
'  If there is no match then return the letter R to K2.

  Range("K2").Formula = "=findals(A2,Sheet2!L1:L30)"
'  Comment the following line out if you don't want the formula in K2
'  to autofill down to the end of data
  Range("K2").AutoFill Destination:=Range("K2", Cells(LastRow, "K"))

End Sub

Function FindALS(FindVal As Single, TableRng As Range) As String
  Dim iRow       As Long
  Dim ColHval    As String
  
  For iRow = TableRng.Row To TableRng.Rows.Count
     If TableRng(iRow, 1) = FindVal Then
        ColHval = Sheet2.Cells(TableRng(iRow, 1).Row, "H")
        If ColHval Like "*ALS*" Then
         FindALS = "M"
        Else
         FindALS = "R"
        End If
        Exit For
     End If
  Next iRow
        
End Function
___________________________________________________

Note that I wrote a user-defined function (UDF) FindALS to implement Part 2 because I found that doing it with the built-in worksheet functions was inefficient and unwieldy.  Just run sub Matching to do both Part 1 and Part 2.

Let me know if you have any problems with this.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.