Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
I have a cell that looks like this:
=IF(B23=B22,C22,"") But what I really want is a Match function that finds the last match between B23 and any row above it, and then copies the value in column C to C23. e.g. B C 19 red 3 20 green 5 21 red 6 22 blue 17 How do I get C23 to enter a 6 when I enter "red" in B23? Note: This spreadsheet will grow, I add a new row or two every week. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
Here is a user-defined function. You may want to omit the 3rd argument
and use MatchLast = LookupArray(iRow, 2). Function MatchLast(LookupValue, LookupArray As Range, Column As Integer) Dim c As Range Dim iRow As Integer For iRow = LookupArray.Rows.Count To 1 Step -1 If LookupArray(iRow, 1) = LookupValue Then MatchLast = LookupArray(iRow, Column) Exit Function End If Next iRow End Function Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
On 10 May 2007 08:42:05 -0700, merjet wrote:
Here is a user-defined function. You may want to omit the 3rd argument and use MatchLast = LookupArray(iRow, 2). I actually will be populating quite a few cells with values on more than one tabbed page. I'm not sure whether that's an argument for a 3rd argument. I saved the macro (it wanted to make it a sub), and haven't quite got the cell working. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
On Thu, 10 May 2007 10:00:28 -0600, Howard Brazee
wrote: I saved the macro (it wanted to make it a sub), and haven't quite got the cell working. I get a compile error, expected type name. I suspect I'm not passing something correctly. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
I saved the macro (it wanted to make it a sub), and haven't
quite got the cell working. Post your code. Some cell data would help, too. Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
On 10 May 2007 09:12:56 -0700, merjet wrote:
I saved the macro (it wanted to make it a sub), and haven't quite got the cell working. Post your code. Some cell data would help, too. Merjet I'm not familiar with user created functions, so I didn't want to get fancy. My cell's value has: =MatchLast($B24, $B$2:$B23, 3) for now, just to see what I would get. I guessed that "column as integer" meant, translate C to 3. When I copied in your code, I selected Tools/Macro/Macros, entered in MatchLast, erased the stub code for a subroutine, and pasted in your function. That may have been the wrong thing to do, but I looked all over for how to enter a function. (I found a place for recording a macro - maybe I should have done that and then edited it down). I told it that it is a global macro. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
It sounds like you put the code in the right place (in a standard
module in the VB Editor). On a worksheet a user-defined function is used like an Excel function. =MatchLast($B24, $B$2:$B23, 3) is fine syntax-wise. However, the LookupArray you used is only one column -- it should be 2 or more -- and 3 means the 3rd column (of the LookupArray), which would only be valid if the LookupArray had 3 or more columns. Hth, Merjet |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
On 10 May 2007 10:44:56 -0700, merjet wrote:
It sounds like you put the code in the right place (in a standard module in the VB Editor). That's good. On a worksheet a user-defined function is used like an Excel function. =MatchLast($B24, $B$2:$B23, 3) is fine syntax-wise. However, the LookupArray you used is only one column -- it should be 2 or more -- and 3 means the 3rd column (of the LookupArray), which would only be valid if the LookupArray had 3 or more columns. I don't understand what that means, and will have to do some research. I opened my spreadsheet, and spent some time trying to remember how to tell it that this macro is safe. I've done that in the past, and remember it taking some work, but don't remember what I did. That can wait though. Since the number 3 is wrong, I tried replacing it with 2 and with 1, without getting around the Compile error: Expected: type name which pops up over my macro editor. The first logical line below is red: Function MatchLast(LookupValue, LookupArray As Range, Column As Integer) Dim c As Range Dim iRow As Integer For iRow = LookupArray.Rows.Count To 1 Step -1 If LookupArray(iRow, 1) = LookupValue Then MatchLast = LookupArray(iRow, Column) Exit Function End If Next iRow End Function |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
Compile error:
Expected: type name which pops up over my macro editor. The first logical line below is red: Function MatchLast(LookupValue, LookupArray As Range, Column As Integer) In your VB Editor "Integer)" s/b on the same line as the one before it. When posted here, long lines can overflow to the next line. Merjet |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
On 10 May 2007 13:02:05 -0700, merjet wrote:
In your VB Editor "Integer)" s/b on the same line as the one before it. When posted here, long lines can overflow to the next line. That corrected the problem. It didn't occur to me that VB as line sensitive. =MatchLast($B24, $B$2:$B23, 2) appears to work for me. Now I need to find the macro and tell Excel to trust it. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel matching question
=MatchLast($B24, $B$2:$B23, 2)
That won't work. But $B$2:$C23 should. Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another question regarding matching names and adding spaces | Excel Worksheet Functions | |||
Matching a Date question | New Users to Excel | |||
Matching Question | Excel Worksheet Functions | |||
Matching Question | Excel Worksheet Functions | |||
vba question.. matching in columns | Excel Programming |