PDA

View Full Version : Filldown a vlookup with no fixed start point



Laura
10-06-2007, 05:32 PM
Hi,

I was wondering if you could help me.

I'm new to VBA - was sent on a course by work and today I've been trying to write something for the first time.

I managed to write a lot of it, but I've come stuck on one bit and have been for hours now. I have three vlookups I need to do (by vlooking up another sheet that changes each time). That bit was fine. But I want to fill the columns down with the result of the vlookup. But the problem that I can't seem to get round is that the vlookups won't always start in the same column and the number of rows in the vlookup changes, so I can't simply write something like this:

Range("A:A").Select
Selection.CurrentRegion.Select
rowCount = Selection.Rows.Count
Range("B1:C" & rowCount & "").Select
Selection.FillDown
If I wasn't using a macro I'd do the left, ctrl+down, right, shift+right, ctrl+shift+up thingie.

I'm sure there's an easy trick to achieve what I want...

Here's the section of the code I've got so far for the vlookup:

Sub weekly_stats_12()
Dim rowCount As Integer

ActiveCell.Offset(0, 2).End(xlUp).Select

statsName = InputBox("Enter the name of the weekly stats you would like to use.")
Workbooks.Open Filename:="D:\Desktop\" & statsName & ""


Range("A:A").Select
Selection.CurrentRegion.Select
rowCount = Selection.Rows.Count

Workbooks("Example Static Price Report.xls").Activate

ActiveCell = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A$1:$A$" & rowCount & ",1,FALSE)"
ActiveCell.Offset(0, 1) = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A$1:$A$" & rowCount & ",1,FALSE)"
ActiveCell.Offset(0, 2) = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A$1:$A$" & rowCount & ",1,FALSE)"



'ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1).Select
'And this is where I get stuck!

End Sub
Thanks in advance.

Bob Phillips
10-07-2007, 01:08 AM
Laura,

Do you need to count the rows? VLOOKUP will use complete columns, and is very efficient in the way that it handles it.



ActiveCell = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A:$A,1,FALSE)"

Laura
10-07-2007, 05:13 AM
Hi xld,

You are right, I don't need to use the rowCount in that situation. Thanks!

I also managed to sort out my initial problem - I needed to do this:

ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:C1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
If I post all of what I've written, would it be possible for one of you good people to have a look through it to see if there's a better way/more efficient way of writing the code? I just want to get into good coding habits, you see. (I'm not sure about the way I've formated it either... sort of made it up as I went along, although I'm sure there are standards for that too.) I only started yesterday and I'm really surprised that I have managed to come up with something that not only works, but will save me several hours per week.

(You will see that I've vlooked up to the same document and only used column A, but that's because the files are stored work and are confidential so I can't send them home - and they are rather huge!)

Sub unMatched()
Dim i As Integer
Dim rowCount As Integer
Dim reportName As String

Worksheets(1).Name = "UnMatched"
Worksheets(2).Name = "Matched"
Worksheets(3).Name = "PRICHK"

Worksheets("UnMatched").Range("A1").Select

'Delete the first two rows
ActiveCell.EntireRow.delete
ActiveCell.EntireRow.delete

Columns("A:A").EntireColumn.AutoFit

'Sort column A and align left
ActiveCell.CurrentRegion.Select
Selection.sort Key1:=Range("A1"), Order1:=xlAscending
Selection.HorizontalAlignment = xlLeft

' Loop until end of list
Do Until ActiveCell = ""

' Loop through the list
For i = 1 To rowCount

' Don't compare against yourself (not sure if I need this)
If ActiveCell.Row <> Cells(i, 1).Row Then
' Do comparison of next cusip.
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
' If matched then delete row.
ActiveCell.delete
' Increment counter to account for the deleted row.
i = i + 1
End If
End If

Next i
'Go to the next cusip
ActiveCell.Offset(1, 0).Select

Loop

reportName = InputBox("Enter the file name of the last report you want to vlookup")
Workbooks.Open Filename:="D:\Desktop\" & reportName & ".xls"
Workbooks("Example Static Price Report.xls").Activate
Range("B1") = "=VLOOKUP(A1,'[" & reportName & ".xls]UnMatched'!$A:$A,1,FALSE)"
Range("C1") = "=VLOOKUP(A1,'[" & reportName & ".xls]UnMatched'!$A:$A,1,FALSE)"
Workbooks("" & reportName & ".xls").Close (xlDoNotSaveChanges)

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Columns.AutoFit


End Sub

Sub weekly_stats()
Dim statsName As String

ActiveCell.Offset(0, 2).Select

'Open the weekly stats file to vlookup - want mid, final source and final static/OK
statsName = InputBox("Enter the name of the weekly stats you would like to use.")
Workbooks.Open Filename:="D:\Desktop\" & statsName & ""
Workbooks("Example Static Price Report.xls").Activate
ActiveCell = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A:$A,1,FALSE)"
ActiveCell.Offset(0, 1) = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A:$A,1,FALSE)"
ActiveCell.Offset(0, 2) = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A:$A,1,FALSE)"
Workbooks("" & statsName & ".xls").Close (xlDoNotSaveChanges)

'Used relative references to do left, ctrl+down, right, shift+right, ctrl+shift+up
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:C1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Columns.AutoFit
ActiveCell.Offset(0, 1).Select

'Repeat as necessary by running again if client uses more than one valuation point

End Sub

Sub unmatched_column_headings()

Rows("1:1").Select
Selection.Insert
Selection.Font.Bold = True

Range("A1").Value = "Cusip"
Range("B1").Value = "Source"
Range("C1").Value = "Alternative"
Range("A1").Select

End Sub

Bob Phillips
10-07-2007, 05:27 AM
There is rarely if ever a need to select anything in Excel, and it is mightily inefficient. You can be specific with the cells being processed.

Besides that, I like to be explicit, always state the property, such as

cell.FormulA = "the_formula"

not just

cell = "the_formula"

What exactly are you trying to do with

'Used relative references to do left, ctrl+down, right, shift+right, ctrl+shift+up

Laura
10-07-2007, 05:41 AM
What would I use instead of select? I did read about it being inefficient in my book, but the course I went on used it all the time (and the bloke just wanted to write code to change the colour of things and wasn't very real-world). Can I just use Range("A1"), for example?

What I was trying to do with 'Used relative references to do left, ctrl+down, right, shift+right, ctrl+shift+up was to pull the vlookup formula down for all the rows. In my spreadsheet there's about 200 rows with identifiers in. I need to look them up against another spreadsheet and pull selected data from there.

Bob Phillips
10-07-2007, 06:53 AM
Yes you would use actual ranges, for example, not



Rows("1:1").Select
Selection.Insert
Selection.Font.Bold = True


you would use



With Rows("1:1")
.Insert
.Font.Bold = True
End With


To get a range down, just use something like



Set rng = Range(Range("A1"),Range("A1").End(xlDown))


If you want to find the last row and there might be blanks, use



LastRow = Cells(Rows.Count,"A").End(xlUp).Row
Set rng = Range("A1:A" & LastRow)

'or

Set rng = Range("A1").Resize(LastRow)


It's a shame about VBA courses (courses generaly I guess), they always deal with the same sort of tosh, nothing useful. The old 'Hello world' syndrome.

Laura
10-07-2007, 07:06 AM
Oh, thank you so very much, xld. You have been such a great help.

That makes so much sense.

I will go through my code and clean it up. It's best to get into good practices from the start, I suppose, so I will try my best to be as efficient as possible code-wise.

I think the course was expensive too. I'm guessing ?500-1000. I'm glad my employer foot the bill. If I had paid for it - which you can do - I would have been disappointed. During one of the breaks I spoke to the instructor and he said he doesn't like the courses he has to teach - most people taking them just want to pass an exam so they are tailored only for that. He said that he teaches some courses and the only experience he has with the particular software is the couple of days learning it his company allows and reading through the offical tutorial and books his company provides. To me that won't be much help for people in large corporate organisations who use the software daily and want solutions to their problems. Ho hum.

Bob Phillips
10-07-2007, 07:10 AM
It's not only efficiencey, it is readability. All that selecting is very confusing IMO, whereas explicitly defining the range, and how it is determined, makes it easier to maintain.

That training approach is very odd.

I teach Excel classes, but I only do a tailorised course for a company, I don't do multi-company courses as I it lacks a commitment, too much like ticking the box.

Actually, if it was ?500-1000, that is relatively cheap. ?1500 is more the norm for a 2-3 day VBA course.

Bob Phillips
10-07-2007, 07:16 AM
BTW, if you are serious about programming, get hold of Carlos Quintero's MZ_Tools addin. It is free and it is indispensable.

You can get it at http://www.mztools.com/v3/mztools3.aspx

Laura
10-07-2007, 10:52 AM
Thanks for the addin link. I've installed it on my PC and I'll definately read the website to see how to make the best use of it.

If the course was ?1500 then I'm even more shocked!

I've rejigged the code. (I'll stick it at the end of the post incase any who stumbles across the thread wants to see it.)

It didn't like:

With Rows("1:1")
.Insert
.Font.Bold = True
End With
It made the original first row bold, not the new one, so I changed that.

If I may, can I ask another question? I am using:

ActiveCell.Offset(0, 2).Select
'and
ActiveCell.Offset(0, 1).Select
This is because the macro will be used more than once on some reports and the results will be located nect to each other. Is the a better way of stating the starting and ending cells with out using select?



Sub unMatched()
Dim i As Integer
Dim rowCount As Integer
Dim reportName As String
Dim rng As Range
Dim lastRow As Integer

Application.ScreenUpdating = False

Worksheets(1).Name = "UnMatched"
Worksheets(2).Name = "Matched"
Worksheets(3).Name = "PRICHK"
Worksheets("UnMatched").Activate

Rows("1:2").delete

With Columns("A:A")
.EntireColumn.AutoFit
.sort Key1:=Range("A1"), Order1:=xlAscending
.HorizontalAlignment = xlLeft
End With

' Loop until end of list
Do Until ActiveCell = ""

' Loop through the list
For i = 1 To rowCount

' Don't compare against yourself (not sure if I need this)
If ActiveCell.Row <> Cells(i, 1).Row Then
' Do comparison of next cusip.
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
' If matched then delete row.
ActiveCell.delete
' Increment counter to account for the deleted row.
i = i + 1
End If
End If

Next i
'Go to the next cusip
ActiveCell.Offset(1, 0).Select

Loop

reportName = InputBox("Enter the file name of the last report you want to vlookup")
Workbooks.Open Filename:="D:\Desktop\" & reportName & ".xls"
Workbooks("Example Static Price Report.xls").Activate
Range("B1") = "=VLOOKUP(A1,'[" & reportName & ".xls]UnMatched'!$A:$A,1,FALSE)"
Range("C1") = "=VLOOKUP(A1,'[" & reportName & ".xls]UnMatched'!$A:$A,1,FALSE)"
Workbooks("" & reportName & ".xls").Close (xlDoNotSaveChanges)

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("B1:C" & lastRow)
With rng
.FillDown
.Copy
.PasteSpecial Paste:=xlPasteValues
.Columns.AutoFit
End With

Application.ScreenUpdating = True

End Sub

Sub weekly_stats()
Dim statsName As String
Dim lastRow As Integer

Application.ScreenUpdating = False

ActiveCell.Offset(0, 2).Select

'Open the weekly stats file to vlookup - want mid, final source and final static/OK
statsName = InputBox("Enter the name of the weekly stats you would like to use.")
Workbooks.Open Filename:="D:\Desktop\" & statsName & ""
Workbooks("Example Static Price Report.xls").Activate
ActiveCell = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A:$A,1,FALSE)"
ActiveCell.Offset(0, 1) = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A:$A,1,FALSE)"
ActiveCell.Offset(0, 2) = "=VLOOKUP(A1,'[" & statsName & ".xls]UnMatched'!$A:$A,1,FALSE)"
Workbooks("" & statsName & ".xls").Close (xlDoNotSaveChanges)


lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ActiveCell.Range("A1:C" & lastRow)
With rng
.FillDown
.Copy
.PasteSpecial Paste:=xlPasteValues
.Columns.AutoFit
End With
ActiveCell.Offset(0, 1).Select

Application.ScreenUpdating = True

'Repeat as necessary by running again if client uses more than one valuation point

End Sub

Sub unmatched_column_headings()

Application.ScreenUpdating = False

Rows("1:1").Insert
Rows("1:1").Font.Bold = True

Range("A1").Value = "Cusip"
Range("B1").Value = "Source"
Range("C1").Value = "Alternative"

Application.ScreenUpdating = True

End Sub

Bob Phillips
10-07-2007, 11:27 AM
It didn't like:

With Rows("1:1")
.Insert
.Font.Bold = True
End With
It made the original first row bold, not the new one, so I changed that.
Sorry, my error, I didn't look closely enough.


If I may, can I ask another question? I am using:

ActiveCell.Offset(0, 2).Select
'and
ActiveCell.Offset(0, 1).Select
This is because the macro will be used more than once on some reports and the results will be located nect to each other. Is the a better way of stating the starting and ending cells with out using select?
You should be setting a range object to the start cell. Activecell is SO bad because unless you select a cell, you have no idea where it will be. If you do select a cell, there is no need, set a range to that cell and then work from the range object, not activecell. By working from the range it is easier to see where you are going. For instance



Activecell.Offset(0,2).Select

'lots more code so that you lose sight of last select

Activecell.Offset(1,3).Select

'lots more code so that you lose sight of last select

Activecell.Offset(2,1).Select

'etc.

where exactly are you. But with



Set rng = Range("A1")

With rng.Offset(0,2)
'lots more code
End With

With rng.Offset(1,5)
'lots more code
End With

With rng.Offset(3,6)
'lots more code
End With

it makes you more rigorous, ensuring you control its position.

mdmackillop
10-07-2007, 05:04 PM
and to extend a range, you'll find Resize useful
Set rng = Range("A1")

With rng.Offset(0,2).resize(3,5)
.interior.colorindex = 6
'lots more code
End With