|
Subject:
|
Using Match function retrieve cell reference not
|
|
Posted By:
|
wapfu
|
Post Date:
|
12/10/2006 8:32:10 PM
|
If I use the "application.match" function to search for a string on two sheets. Is there someway to reference the cell not the XLvalue?.
That is, retrieve and store the cell location for a find function so that I can use that cell reference latter, to copy a value adjacent to it? Range 1 has unique cell values running horizontally. Range 2 runs vertically and can have duplicate values.
'sheet 2 tracking sheet
Sheet2.Visible = True
Set rng2 = Sheet2.Range("A1:A209") 'row and column vertically
'sheet 3 is where rng1 is found
Sheet3.Visible = True 'summary sheet(3)
k = 51 'starting row for copying too, on summary sheet
Set rng1 = Sheet3.Range("A4:HQ4") 'row and column horizontally
'set up the search
For Each cell In rng2
res = "" 'res = some string on sheet(2) in column 1
res = Application.WorksheetFunction.Match(cell.Value, rng1, 0) ' match string on sheet 2 with something on sht 3
If Not IsError(res) Then ' they match
' do something like start copying cell information
z = "" 'basically z = res
z = cell.Value 'set object from Match function
MsgBox cell.Value & " equals " & rng1(res).Value, vbOKOnly 'check it's working
I've found the matching cells, now if I use .find in this instance its OK-
Set rng7 = Sheets(3).Range("A4:HQ4").Find(what:=z, LookIn:=xlValues) 'find the correct cell
'using find will only ever find the first cell where the information is found but this is ok as the row values are unique.
Set rng8 = rng7.Offset(k, 1)
as there are only unique values in on the sheet .finf works OK.
Now if there are a number of cells with the same value on the next sheet .find doesn't work - I'd like this time to use a cell reference so that that cell can be used and not the first cell
say we have a1=101 b1=$7, a2=101 b2=$8, a3=109 b3=$52, a4=101 b4=$10,
The first column is accounting codes the second(B) is the value.
Match finds A1=101 and matches it with 101 on the second sheet.
But if I want to use a4, if I use find it will only stop at the first cell A1 but it is A4 I want - hence the need for a cell reference not xlvalue.
Possible?
Or is there another way to locate cells on different sheets and then use that cell reference to latter copy a value from (an adjacent cell) on the first sheet to the free cell that matches adjacent to the value on the second?
VB is needed. The code would reduce an 11meg sheet to around 383kb.
The original uses vlookup and if statements in each cell to copy data from sheet 3 to sheet 2.
Sheet 3 has infromation pasted to it from another workbook, the accounting codes are constant.
The idea is to use the code by clicking a command button once the new data has been added. Sheet 2 is a summary sheet and basically summs different accounting codes.
Everything in a simple -I hope - description.
Thanks
Bill
|
|
Reply By:
|
Shasur
|
Reply Date:
|
12/11/2006 3:35:18 AM
|
Hi Bill
You want to get the address of the cell that has your find value, right!
use rng.Address
Or
If you are specific in row or column
you can use
rng.row / rng.column
Where rng will be your find
http://www.vbadud.blogspot.com
|
|
Reply By:
|
maccas
|
Reply Date:
|
12/11/2006 4:09:26 AM
|
Bill,
I must admit I don't fully understand the problem and what you're trying to achieve. I can't help but feel that you are best off using a SUMIF function on Sheet 2 to summarise the values by accounting codes on Sheet 3, however I may be misunderstanding your requirements.
In answer to you specific query - you can grab the cell range variable from the output of a Application.WorksheetFunction.Match function by understanding that the match function returns the position in a range list. Therefore, given your rng1 variable is a row of cells, you should be able to determine the found cell variable by using:
Set rngFound = rng1.Cells(1,res)
For info, you are correct that Find will only return the first found instance (as indeed will the MATCH worksheet function) however you can combine it with the FindNext method to find all instances of a particular search value. See http://p2p.wrox.com/topic.asp?TOPIC_ID=53253 for an example of how to implement this.
Do let us know if I can help any more or any of this does not make sense.
Maccas
|
