Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-15-2016, 08:43 AM
c991257 c991257 is offline Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2016
Advanced Beginner
Vlookup in a range (or something)
 
Join Date: Mar 2014
Location: Zambia
Posts: 61
c991257 is on a distinguished road
Default 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
Attached Files
File Type: xlsx ARZ, TMH1 A Series Test Summary.xlsx (36.6 KB, 20 views)
Reply With Quote
  #2  
Old 07-20-2016, 02:07 AM
p45cal's Avatar
p45cal p45cal is online now Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #3  
Old 07-25-2016, 05:50 AM
c991257 c991257 is offline Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2016
Advanced Beginner
Vlookup in a range (or something)
 
Join Date: Mar 2014
Location: Zambia
Posts: 61
c991257 is on a distinguished road
Default

Compile Error - Variable not defined?
Reply With Quote
  #4  
Old 07-25-2016, 05:57 AM
p45cal's Avatar
p45cal p45cal is online now Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by c991257 View Post
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
Reply With Quote
  #5  
Old 07-25-2016, 06:15 AM
c991257 c991257 is offline Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2016
Advanced Beginner
Vlookup in a range (or something)
 
Join Date: Mar 2014
Location: Zambia
Posts: 61
c991257 is on a distinguished road
Default

wow.

Thanks.
Reply With Quote
  #6  
Old 07-26-2016, 01:18 AM
c991257 c991257 is offline Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2016
Advanced Beginner
Vlookup in a range (or something)
 
Join Date: Mar 2014
Location: Zambia
Posts: 61
c991257 is on a distinguished road
Default

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
Attached Files
File Type: xlsm 300, ARZ, Test Summary, Sample Data.xlsm (101.2 KB, 10 views)
Reply With Quote
  #7  
Old 07-26-2016, 06:00 AM
p45cal's Avatar
p45cal p45cal is online now Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #8  
Old 07-26-2016, 06:55 AM
c991257 c991257 is offline Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2016
Advanced Beginner
Vlookup in a range (or something)
 
Join Date: Mar 2014
Location: Zambia
Posts: 61
c991257 is on a distinguished road
Default

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?
Reply With Quote
  #9  
Old 07-26-2016, 07:00 AM
c991257 c991257 is offline Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2016
Advanced Beginner
Vlookup in a range (or something)
 
Join Date: Mar 2014
Location: Zambia
Posts: 61
c991257 is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 07-26-2016, 08:25 AM
p45cal's Avatar
p45cal p45cal is online now Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by c991257 View Post
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 View Post
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.
Reply With Quote
  #11  
Old 08-31-2016, 05:05 PM
p45cal's Avatar
p45cal p45cal is online now Vlookup in a range (or something) Windows 10 Vlookup in a range (or something) Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by p45cal View Post
I strongly suspect that if you were to remove:
, searchformat:=False
including that leading comma, it would work on a mac.
Did it work?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup in a range (or something) Find if Date range falls within another range Triadragon Excel 3 05-02-2016 11:48 AM
Vlookup in a range (or something) 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
Vlookup in a range (or something) 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft