PDA

View Full Version : Solved: Mapping search



Gil
02-13-2010, 11:27 AM
Please see the notes in the attached sheet that hopefully explains what I am looking to achieve. I just haven't a clue where to start on this one so if anyone can help thank you in advance.

mdmackillop
02-14-2010, 05:56 AM
I've probably misunderstood this, as I can't make sense of your examples. Please show clearly where each result factor comes from.

Option Explicit
Sub Locate()
Dim v
Dim c As Range, Rng As Range
Dim i As Long, rws As Long
Set c = ActiveCell
v = Split(c)(1)
rws = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To rws Step 8
Set Rng = Cells(i, 2).Resize(8)
If v >= Rng(1) & v <= Rng(2) Then
c.Offset(, 1) = "'" & Rng(7) & "-" & Rng(5) & "-" & v - Rng(2) + 1
Exit For
End If
Next
End Sub

Gil
02-14-2010, 08:17 AM
Hello mdmackillop
Thank you for the interest in this.
I have added some detail to sheet 1 and also added an example of the full detail on sheet 2. Examples are highlighted in red on sheet 2.

mdmackillop
02-14-2010, 01:24 PM
Are you looking to return the data from sheet 1 or sheet 2?

Gil
02-14-2010, 09:12 PM
mdmackillop
Sheet 2 is the long winded version of sheet one.A complete version of this would be far too large to open quickly. The compressed version is sheet one and that is where I want to return the data from.
If it is still not clear I will close this thread down and try a different route.
Thank you for your support so far.
Gil

mdmackillop
02-15-2010, 05:23 PM
I'm sorry, but I can't see where the 3 results in Column G come from the sheet 1 data. If you can clarify this, I'm sure we can find a solution. Can you see where my earlier post is going wrong?

Gil
02-15-2010, 09:08 PM
mdmackillop
I have had another go with your code and it almost works. My inexperience hindered my first efforts.It does give the result I am after but needs a bit of tweeking.
The block size in each case is 96 so the 3rd number should be in the range 1-96. If you enter LIC 33024 the correct result is 2-7-1.Enter LIC33119 the correct result is 2-7-96. Enter LIC 33120 the result is 2-7-97 but should be 2-8-1 as it steps up to the next level, 8 in this case. When it gets to Level 10 it should then step to the next vert which is 3 level 1.
A minor thing is the result appears in F4 & not G4.
Hope that makes some sense
Gil

mdmackillop
02-16-2010, 09:49 AM
It's much simpler to get the data directly from Sheet 2


Sub Locate2()
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Set Sh = Sheets("Sheet2")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(1)), lookat:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 2) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
End Sub

Gil
02-16-2010, 11:44 AM
mdmackillop
Thanks for the reply and suggestion. The only problem is the full version I estimate to be about 50mb having approx 60 sheets. That would be too big for one file wouldnt it.

Gil
02-22-2010, 09:48 AM
Hello mdmackillop

I have looked at ways of reducing my data file which I assume I could call a Database to a level where I think it could be more manageable using the format in the attached workbook. I dont think this needs to be opened to reference to it so I need some code in a new workbook to do the work referencing to my database.
Therefore if an entry is made in a cell then a result would be returned in another cell

example
new workbook entry cell = d4 , result cell = g4

cell d4 entry Lic33407 would give a result in g4 2-10-96 the 2 is vert 2 the 10 level 10 and the 96 block pair 96

cell d4 entry Lic35263 would give a result in g4 37-10-96 the 37 is vert 37 the 10 level 10 and the 96 block pair 96

cell d4 entry Lic34185 would give a result in g4 3-9-10 the 3 is vert 3 the 9 level 9 and the 10 block pair 10

cell d4 entry Lic36015 would give a result in g4 38-8-80 the 38 is vert 38 the 8 level 8 and the 80 block pair 80

I hope that I have explained the examples clearly and you have something positive to work on.

Many thanks in anticipation

Gil

Gil
02-23-2010, 02:34 PM
Hello
In my previous posting I forgot to add the code I have copied from mdmackillop's earlier sugestion. The problem I have is my database has changed and the info for the last 2 searches are now only contained in columns B & A. Therefore the code Fnd.Offset no longer works as the search data is variable ie columns d,h,k.o.q etc. Also I would like to do my seach from another workbook while my database is closed. Is that possible.

Sub Locate2()
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Set Sh = Sheets("Sheet2")
Set c = ActiveCell
Set Fnd = Sh.Cells.find(Trim(Split(c)(1)), LookAt:=xlWhole)
c.Offset(, 4) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
End Sub

Many thanks in anticipation

Gil
03-02-2010, 09:56 AM
Hello
Many thanks for the help and contributions. Although my task was not fully resolved I put it down to the way I asked the question. I shall have to think carefully when I want to make a request in future.
Never the less I have gained a bit more knowledge from this experience.
Gil