You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Excel 2007 Windows Formula

Advertisement

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

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

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 8 | Politeness = 10 |

Comment | Responded promptly and provided the formula. Had to tweak it a bit but it was way more than what I would have been able to do on my own. |

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

Answers by Expert:

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.

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.