PDA

View Full Version : Solved: use formula in VBA for vlookup



mperrah
07-28-2007, 01:01 PM
I'm trying to remove code from worksheet to elimnate user altering/deleting. I can lock sheets, but want more control over code in sheets.
I have a chart that uses vlookup and sumproduct, rather than leave the vlookup on the sheet I'm trying to put it in vba

This is one of the data source cells for the chart
techchart (a named cell) is the technumber of the formula I'm trying to relocate.
=SUMPRODUCT(--(item27=scan_item),--(QCDate>=WkStart),--(QCDate<=RangeEnd),--(Techs=techChart))

This is the cell I pull the pass and fail results.
="Pass" & ": "&TechPass &" Fail: "&TechFail

I have a table of data of pass and fail items,
using sumproduct within a date range, the chart shows results based on a drop down validation list from range AJ7 to AO67 (the AO67 may change)
part of controling the code is I want to use a count on the rows down incase I add or remove the total techs so AO67 might go to 68 or 66

Function FindTechNumber()

Dim technum As short
Dim TechPassValue As short
Dim TechFailValue As short

With Worksheets("Chart")

Set technum = "VLookup(AD35, AJ7:AO67, 3)"
Set TechPassValue = "VLookup(AD35, AJ7:AO67, 5)"
Set TechFailValue = "VLookup(AD35, AJ7:AO67, 6)"

' on change of validation list in AD35 re-calculate these values?

End Function
The file is working with code on the worksheet, but fails if I add a tech or delete one.
I supply other offices with this workbook and they have different numbers of techs, so the AO67 value may change, being able to allow vba code to accomidate that will help deployment.

If I can get the coding in VBA is the best so others can't (are less likely) break it...
Thanks in advance.

Bob Phillips
07-28-2007, 04:00 PM
I am not sure what the question is, and I have no idea what Short is, but here goes



Function FindTechNumber()
Dim technum
Dim TechPassValue
Dim TechFailValue

With Worksheets("Chart")

technum = Application.VLookup(Range("AD35"), Range("AJ:AO6"), 3)
TechPassValue = Application.VLookup(Range("AD35"), Range("AJ:AO6"), 5)
TechFailValue = Application.VLookup(Range("AD35"), Range("AJ:AO6"), 6)
End With

End Function

mperrah
07-28-2007, 04:38 PM
I modified to search for each of the 3 values I need.
in the cell I type:
=FindTechNumber(n) or maybe just
=FindTechNumber() ?

Cell AD35 is a data validation list that refers to the range in the function below
Function FindTechNumber()
Dim n

With Worksheets("Chart")
n = Application.VLookup(Range("AD35"), Range("AJ:AO6"), 3)
End With

End Function
This just evaluates to #Value!
How do I properly call this function?

I use another one like this:
Function FileSavedIn()
Select Case ActiveWorkbook.FileFormat
Case 56: FileSavedIn = ".xls" ' Excel 2003
Case 55: FileSavedIn = ".xlam" ' Excel 2007 Open XML Add-in
Case 54: FileSavedIn = ".xlts" ' Excel 2007 Open XML Template
Case 53: FileSavedIn = ".xltm" ' Excel 2007 Open XML Template Macro Enabled
Case 52: FileSavedIn = ".xlsm" ' Excel 2007 Open XML Macros Enabled
Case 51: FileSavedIn = ".xlsx" ' Excel 2007 Open XML
Case -4143: FileSavedIn = ".xls" ' Excel 2000 et plus (workbook normal)
Case 43: FileSavedIn = ".xls" ' Excel 97/2000 (correct?)
Case 39: FileSavedIn = ".xls" ' Excel 5 (correct?)
Case 35: FileSavedIn = ".xls" ' Workbook Excel 4 (correct?)
Case 33: FileSavedIn = ".xls" ' Sheet Excel 4 (correct?)
Case 29: FileSavedIn = ".xls" ' Excel 3 (Correct?)
Case 18: FileSavedIn = ".xla" ' Excel 97-2003 Add-in
Case 17: FileSavedIn = ".xlt" ' Excel Template
Case 16: FileSavedIn = ".xls" ' Excel 2.1 (Correct?)
Case Else: FileSavedIn = ".xls" ' Unknown
End Select
End Function

in a cell I have :
=FileSavedIn()
this evaluates to .xls

stanleydgrom
07-28-2007, 05:03 PM
mperrah,

Please try this FIRST on a TEST copy of workbook.

I changed your Function to a Macro.



Option Explicit
Sub FindTechNumber()
Dim lngLastRow As Long
With Worksheets("Chart")
lngLastRow = Sheets("Chart").Range("AJ" & Rows.Count).End(xlUp).Row
Range("AJ7:AO" & lngLastRow).Sort Key1:=Range("AJ7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'technum address
Range("AD37").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 3)"
'TechPassValue address
Range("AD38").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 5)"
'TechFailValue address
Range("AD39").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 6)"
'Adjust this range for the range for the above 'tech' references
With Range("AD37:AD39")
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
Range("AE35").Select
' on change of validation list in AD35 re-calculate these values?
End With
End Sub




Have a great day,
Stan

mperrah
07-28-2007, 05:17 PM
A few changes.
i had some values in the cels you called out.
Should this make a vlue apear in the range AD42:AD44?
Or do I need to call the formula first?

Sub FindTechNumber()
Dim lngLastRow As Long
With Worksheets("Chart")
lngLastRow = Sheets("Chart").Range("AJ" & Rows.Count).End(xlUp).Row
Range("AJ7:AO" & lngLastRow).Sort Key1:=Range("AJ7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'technum address
Range("AD42").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 3)"
'TechPassValue address
Range("AD43").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 5)"
'TechFailValue address
Range("AD44").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 6)"
'Adjust this range for the range for the above 'tech' references
With Range("AD42:AD44")
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
Range("AE35").Select
' on change of validation list in AD35 re-calculate these values?
End With
End Sub

mperrah
07-28-2007, 05:23 PM
I didn't realize you made it a sub instead of a function.
i put in a command button and it fires perfect.
One thing, can I make it fire on the change of the validation list instead?
Mark

mperrah
07-28-2007, 09:05 PM
If I double click anywhere this fires,
but if I just switch it to the Change event, it starts a un-ending loop.
any ideas?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim lngLastRow As Long
With Worksheets("Chart")
lngLastRow = Sheets("Chart").Range("AJ" & Rows.Count).End(xlUp).Row
Range("AJ7:AO" & lngLastRow).Sort Key1:=Range("AJ7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'technum address
Range("AD42").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 3)"
'TechPassValue address
Range("AD43").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 5)"
'TechFailValue address
Range("AD44").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 6)"
'Adjust this range for the range for the above 'tech' references
With Range("AD42:AD44")
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
Range("AE35").Select
' on change of validation list in AD35 re-calculate these values?
End With
End Sub

mperrah
07-28-2007, 11:07 PM
Got it to work.
Now I realized I don't need the values on the sheet at all.
I can store them as a variable.
How would I use the VLookup in VBA and not paste it into the sheet?
AJ7:AO67 is the data range I am looking up from
the first column is what matches AD35
The 3rd column is the tech number
5th is the pass total and 6th is the fail total
These values are actualy a link to the WQC Sheet that total everything
using sumproduct.
I could bypass the link and pull directly from the WQC sheet and keep it all in VBA, setting up and using the variables is where I'm not sure where to start.

I could have the validation list pull from the range on WQC,
then use the vlookup to pull the values directly
Range is A7:E(count to end down)
there the tech number is column 2, pass is 4 and fail is 5
Here is what I have so far..
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tList As Range
Dim lngLastRow As Long
Set tList = Range("AD35")
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, tList) Is Nothing Then
With Target
With Worksheets("Chart")
lngLastRow = Sheets("Chart").Range("AJ" & Rows.Count).End(xlUp).Row
Range("AJ7:AO" & lngLastRow).Sort Key1:=Range("AJ7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'technum address
Range("AD38").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 3)"
'TechPassValue address
Range("AD39").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 5)"
'TechFailValue address
Range("AD40").Formula = "=VLookup(AD35, $AJ$7:$AO$" & lngLastRow & ", 6)"
'Adjust this range for the range for the above 'tech' references
With Range("AD38:AD40")
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
Range("AE35").Select
' on change of validation list in AD35 re-calculate these values?
End With
End With
End If
End Sub

Bob Phillips
07-29-2007, 02:11 AM
As I showed you in post #2.

mperrah
07-29-2007, 10:33 AM
Bob,
So these are variables I can call from the sheet?

="Pass" & ": "&TechPassValue &" Fail: "&TechFailValue

Bob Phillips
07-29-2007, 10:42 AM
OH no, you would have to store the values somewhere when they are generated, then pick it from there (such as some off-screen worksheet cells).

mperrah
07-29-2007, 11:35 AM
I have a sheet named "Prompt" or "menuSheet" that are hidden,
I could name a cell on one of these.
then how do I store the value there?
in VBA or as a formula

in cell "=TechNumberValue()" ? using the sub
or as we dim in #2
in cell "=technum" ? pulling the variable

mperrah
07-29-2007, 06:24 PM
Would a combobox work better than a data validation list?
how do I call a sub on a combobox change event?

mperrah
07-30-2007, 04:04 PM
How would I save a variable in the vba to avoid needing a helper cell on the sheet.
for example. If I can use the vlookup within vba and store the result as a named variable (or range) that I can use in a formula in the sheet.

vlookup shows the tech number based on the filllistsource and offset,
that result is used in a formula in a cell, how do I name the variable in vba
and how do I use that variable in the worksheet?

mperrah
08-10-2007, 06:37 PM
How can I replace the vlookup formula by using just vba.
The code in previous posts made the cell contents equal the formula rather than typing it in, but how do eliminate the formula all together?
I need a code to update dynamicaly a validation list source
and change the data a chart uses, now it is vlookup and formula.

I have a drop down validation list in A35 that is sourced from a range on sheet("WQC") I pasted links on the Chart sheet.
The WQC sheet totals: the number of jobs per tech, the pass, and fail.
the source range has the tech name in column a, (not used here)
number in b, total jobs in c, pass in d, and fail in e.
then I have a range on sheet("chart") that uses sumproduct to check the results per tech based on the jobs within a date range, then the chart updates based on the tech I choose from the validation list. This range is the chart data source.

My problem is the techs numbers are different in each office and my validation list needs to be updated if the techs are added or removed.
Also I am trying to avoid the cells with the vlookup being deleted.
Locking the cells won't allow the data to change with the different tech being chosen from the drop down validation list..

I appreciate your help.
Mark

Bob Phillips
08-11-2007, 02:17 AM
You would need to to a find on the lookup value in the first column of the lookup table, and if and when found, get the column offset (3, 5, and 6 in your example) from that cell to get the value.

lior03
08-11-2007, 12:14 PM
hello
i am trying to take advantage of this thread for another.
i have a data base,all the macros in a workbook.i want to use th vlookup function to return a macro name by entering a number.
Function FindTechNumber(num)
On Error Resume Next
With Worksheets("projects")
FindTechNumber = Application.VLookup(num, Range("A1:D1750"), 3)
End With
End Function
what is wrong with my function?.
thanks

Bob Phillips
08-11-2007, 12:20 PM
Maybe



Function FindTechNumber(num)
On Error Resume Next
With Worksheets("projects")
FindTechNumber = Application.VLookup(num, Range("A1:D1750"), 3,False)
End With
End Function


Is there any possibility it won't be found?

lior03
08-11-2007, 12:38 PM
and why is that.i only emulate what a vlookup should do.

Bob Phillips
08-11-2007, 12:43 PM
Why is what?

lior03
08-11-2007, 12:47 PM
my function do not work.i try the built in vlookup function ,it did not work either.

Bob Phillips
08-11-2007, 01:03 PM
Doesn't work? Returns nothing? Returns a wrong value? The function crashes and burns? The computer blows up? a little detail perhaps.

lior03
08-11-2007, 01:08 PM
it return #N/A

Bob Phillips
08-11-2007, 03:28 PM
That means it didn't find the value being looked up. I did ask is there any possibility it won't be found. What do you want it to do in these circumstances?

mperrah
08-13-2007, 10:40 PM
1) is there something like sumproduct that resides in vba?
(instead of excel - avoiding code on the sheet)

2) looking at jwalks power programming 2007 (thanks to Kens read list)
using a userform combobox I found code to generate a list name like Vlookup with 2 columns in the combo drop down, the picked item gets copied to a cell - yeah, how can I get the second column copied instead of the first?

3) once this cell gets filled in, how can I fire a code to use the cell contents as a varible for another procedure? - like vlookup?

Bob Phillips
08-14-2007, 12:39 AM
1) is there something like sumproduct that resides in vba?
(instead of excel - avoiding code on the sheet)

No, but you can use worksheet functions, either directly, Application.Vlookup(..., or by evaluating, Activesheet.Evaluate("SUMPRODUCT("--...


2) looking at jwalks power programming 2007 (thanks to Kens read list)
using a userform combobox I found code to generate a list name like Vlookup with 2 columns in the combo drop down, the picked item gets copied to a cell - yeah, how can I get the second column copied instead of the first?



With ListBox1
MsgBox .List(.ListIndex, 1)
End With



3) once this cell gets filled in, how can I fire a code to use the cell contents as a varible for another procedure? - like vlookup?

var = Range("A1").Value perhaps????

mperrah
08-14-2007, 01:08 PM
I get the value in C1 and the msg box works to show index 1
but having trouble setting up c1 = index, 1

this works as stated
Private Sub ComboBox1_Click()
Range("C1") = ComboBox1.Value
With ComboBox1
MsgBox .List(.ListIndex, 1)
End With
Unload Me
End Sub

attempt 1
Private Sub ComboBox1_Click()
dim cbtechID as long
set cbtechID = combobox1.list(.listIndex, 1)
Range("C1") = cbtechID
Unload Me
End Sub
invalid or unqualified reference (focus on .ListIndex)

Private Sub ComboBox1_Click()
Range("C1") = ComboBox1.Value.List(.ListIndex, 1)

Unload Me
End Sub same error as above with same focus (listindex)

Bob Phillips
08-14-2007, 01:18 PM
You must read what we post carefully



Private Sub ComboBox1_Click()
Dim cbtechID As Long
With Combobox1
.list(.listIndex, 1)
End With
Range("C1").Value = cbtechID
Unload Me
End Sub

mperrah
08-14-2007, 01:43 PM
I'm sorry Bob, this is all very exciting and new and the more I learn the more I realize I don't know...

I pasted your code above into the code of the userform
in the VBE makes the .list line red and errors out
.list(.listIndex, 1)

Does tis code belong in the userform combobox code or
do i need to put the code in a regular module?

Bob Phillips
08-14-2007, 02:07 PM
That's because I removed the MsgBox and stupidly didn't add in the variable



Private Sub ComboBox1_Click()
Dim cbtechID As Long
With Combobox1
cbTechID = .list(.listIndex, 1)
End With
Range("C1").Value = cbtechID
Unload Me
End Sub


or just



Private Sub ComboBox1_Click()
Dim cbtechID As Long
With Combobox1
Range("C1").Value = .list(.listIndex, 1)
End With
Unload Me
End Sub

mperrah
08-14-2007, 09:32 PM
Awesome,
They both work beautifully.

I placed the procedure in the code for the combobox of the userform.

Thank you so much,
I really appreciate your help

Mark

mperrah
08-14-2007, 10:58 PM
don't hate me for cross posting but I found a way to do vlookup in vba and wanted to share it here.
I use this forum every day and tell my friends and call it home,
that is why I brought this here.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("F1").Address Then

Dim c As Range
For Each c In Range("TechNumbers")

'Exit on blank
If c.Value = vbNullString Then Exit For

'Copy match
If c.Value = Range("F1").Value Then

'Copy value found two columns to the right of TechNumbers
'A.K.A. The 3rd column, to AD36.
Range("F2").Value = c.Offset(0, 1).Value

End If

Next c

End If

End Sub

I use this code after the userform combobox puts the value in f1,
made in the earlier post here.

Hope this helps anyone trying to free the sheets of vulnerable code.
Thanks again to all,
Mark

Bob Phillips
08-15-2007, 12:09 AM
It may work Mark, but wouldn't Find be more efficient (as we suggested early in the thread), rather than looping through all the cells?

mperrah
08-15-2007, 10:04 PM
Ok, I'm most be glutten cause I love this stuff.
I looked up find method in Excel help and made an attempt here

It errors saying object required in this line:
Set fndMatch = .Find(fTech, LookIn:=xlValues)
And the top line has the break.
This is my first try at find in VBA, But I'm very interested to learn more.
It's ok to get out the red pencil.
My mom's a teacher, so I grew up with it.
Anyone remeber the movie A River Runs Through It

Mark

Edit...
Now it works once then errors on object needed. I think the find criteria is getting saved and reused because I'm not explicatin each time (not sure how, or if this is even why...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dataValtgt As Range
Dim fTech As Object ' fTech is tech number from combo box result
Dim srcRng As Range ' srcRng is range to look in like a vlookup
Dim techTrgt As Double ' techTrgt is named cell the src is found in
Dim RngTrgt As Range ' is the vlookup range to find offset value in
Dim fndMatch As Object ' fndMatch is the found tech number on the wqc page location

Set srcRng = Worksheets("WQC").Range("checkRange")
Set dataValtgt = Range("AD35")
Set fTech = Worksheets("Chart").Range("AD35").Value
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, dataValtgt) Is Nothing Then
With Target
With srcRng
Do
Set fndMatch = .Find(fTech, LookIn:=xlValues)
If Not fndMatch Is Nothing Then
firstAddress = fndMatch.Address
With Worksheets("Chart")
.Range("AD39").Value = fndMatch.Address.Offset(, 1)
.Range("AD40").Value = fndMatch.Address.Offset(, 2)
.Range("AD41").Value = fndMatch.Address.Offset(, 3)
End With
End If
Loop While Not fndMatch Is Nothing And fndMatch.Address <> firstAddress
'End With
End With
End With
End If
End Sub