Vlookup in a range (or something)

c991257 · #1 07-15-2016, 08:43 AM

I have a number of test results from road works that I need to extract the result from, but I can't puzzle out how to do it.

The test result (a compaction percentage) covers a stretch of road from a point (chainage) to a point e.g. from chainage 0+500 to chainage 0+700 (meaning from 500m to 700m). The test result covers that entire stretch.

I need to transfer the test result into a summary sheet which shows the entire road split up in 20 meter sections.

In the example above the test result would cover the ten 20 meter sections between 0+500 and 0+700.

How do I do that?

In the attached I have entered the data manually and highlighted the data used with red.

/Møller

p45cal · #2 07-20-2016, 02:07 AM

Code:

Sub blah()
With Sheets("Sub Grade Data")
  For Each cll In Range(.Range("A2"), .Range("A2").End(xlDown))
    With Sheets("Summary")
      For i = cll.Offset(, 2).Value To cll.Offset(, 3).Value - 1 Step 20
        Set colm = .Rows(1).Find(what:=i, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
        If Not colm Is Nothing Then
          .Cells(22, colm.Column).Value = cll.Offset(, 9).Value
          .Cells(23, colm.Column).Value = cll.Value
        End If
      Next i
    End With
  Next cll
End With
End Sub

c991257 · #3 07-25-2016, 05:50 AM

Compile Error - Variable not defined?

p45cal · #4 07-25-2016, 05:57 AM

Quote:

Originally Posted by c991257

Compile Error - Variable not defined?

Either delete Option Explicit from the top of the module or add the following line at the beginning of the macro:
Dim cll As Range, i, colm As Range

c991257 · #5 07-25-2016, 06:15 AM

wow.

Thanks.

c991257 · #6 07-26-2016, 01:18 AM

The attached file has the code implemented for the various sheets. However for some odd reason it hangs (eventually finishes) on Fill 1. Why is that ?

/Møller

p45cal · #7 07-26-2016, 06:00 AM

Ah yes, that's because the code's using the equivalent of selecting cell B3 on the source sheet then on the keyboard, pressing End then down arrow, which with that source sheet with just a single line of data on row 3, was going right to the bottom of the sheet hence the long time taken. I've updated the code in several ways, principally to do the same from B2 instead of B3, but also, in case there are no lines of data at all, I've capped the number of rows it'll process to 10,000. I've also reduced the amount of code significantly.
See attached.

c991257 · #8 07-26-2016, 06:55 AM

But it was only for the Fill 1. All the others were quick.

I see what you have done in the new code. That's very nifty.

One of my confused Engineers is running on a mac. There the old code gives him an error 448. Any idea what that is?

c991257 · #9 07-26-2016, 07:00 AM

Just a short explanation about what the excel sheet is for.

When building a road the most important test done is a test for compaction. A road is essentially built by adding layers of material on top of each other of increasingly good quality and higher compaction. Once we build a section (of a layer) we run a number of physical tests and the Data sheets verify if the compaction is acceptable. The summary sheet then shows the layers based on the location (chainage/distance from the beginning of the road).

Traditionally the summary would have to be done manually which is an absolute nightmare.

p45cal · #10 07-26-2016, 08:25 AM

Quote:

Originally Posted by c991257

But it was only for the Fill 1. All the others were quick.

It only happened on that sheet because it happened to be the only sheet with a single line of data. Any other sheet, at some future point, having only one line of data would have been v. slow too. Now it shouldn't happen at all.

Quote:

Originally Posted by c991257

One of my confused Engineers is running on a mac. There the old code gives him an error 448. Any idea what that is?

I strongly suspect that if you were to remove:
, searchformat:=False
including that leading comma, it would work on a mac.

p45cal · #11 08-31-2016, 05:05 PM

Quote:

Originally Posted by p45cal

I strongly suspect that if you were to remove:
, searchformat:=False
including that leading comma, it would work on a mac.

Did it work?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find if Date range falls within another range	Triadragon	Excel	3	05-02-2016 11:48 AM
Name a Range in a Word Document and then copy that range to the end of the doc w button click	DanNatCorning	Word VBA	1	04-29-2016 10:47 PM
How to create vlookup formula range R1C1 using VBA	jyfuller	Excel Programming	0	04-29-2015 12:03 PM
Help using If and Vlookup	heastlund	Excel	3	08-04-2014 01:34 PM
VLOOKUP looking at another cell for its range	Rich18144	Excel	1	06-24-2014 07:56 AM