#1
|
|||
|
|||
Vlookup in a range (or something)
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 |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Compile Error - Variable not defined?
|
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
wow.
Thanks. |
#6
|
|||
|
|||
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 |
#7
|
||||
|
||||
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. |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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. |
#10
|
||||
|
||||
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:
, searchformat:=False including that leading comma, it would work on a mac. |
#11
|
||||
|
||||
Did it work?
|
Thread Tools | |
Display Modes | |
|
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 |