Help Vlookup in excel (VBA)

[Walshie1987]

If wanting to put a function into vba

Basically its a vlookup, how would I write it?

I thought maybe something like:

vlookup(cells(vrow,"A").value,data,3,false)

but this isn't working

data is the name of the lookup area

any ideas

Cheers

 

[Walshie1987]

This is the function I want to write in vba:

=IF(W2="D",IF(ABS(IF(AND(VLOOKUP(J2,data,3,FALSE)>X2,X2/0.95<=VLOOKUP(J2,
data,3,FALSE),VLOOKUP(J2,data,3,FALSE)<(X2/0.8)),(X2/0.95)+(((VLOOKUP(J2,
data,3,FALSE)-(X2/0.92))*0.4))-Z2))<=0.02,"Correct","Check"),"")

 

[xlbo]

Your range definition etc needs to be in VBA syntax not worksheet syntax so

myResult = application.vlookup(Range("A1").value, Range("D2:F100"),2,FALSE)


where the lookup value is in A1 and the range to look up in is D2:F100

Why would you want to write that in VBA? it would be more efficient to use it in the worksheet as that is optimised for calculations whereas VBA is not...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Walshie1987]

Thanks Geoff.

Basically I have a spreadsheet with 1000's of lines in, and the function in each of the lines is...

=IF(W4="A",IF(ABS(IF(AND(X4<VLOOKUP(J4,data,3,FALSE),(X4/0.84)<=VLOOKUP(J4,
data,3,FALSE),(X4/0.8)>=VLOOKUP(J4,data,3,FALSE)),X4/0.9,IF(AND(X4<VLOOKUP
(J4,data,3,FALSE),(X4/0.84)<=VLOOKUP(J4,data,3,FALSE),(X4/0.8)<=VLOOKUP(J4,
data,3,FALSE)),(VLOOKUP(J4,data,3,FALSE)-X4)/2+X4,"Test"))-Z4)<=0.02,
"Correct","Check"),IF(W4="C",IF(ABS(IF(AND(X4<=VLOOKUP(J4,data,3,FALSE),
ROUNDUP((X4/0.95),2)>=VLOOKUP(J4,data,3,FALSE)),IF(ROUNDUP((X4/0.95),2)-X4>
=200,X4+200,ROUNDUP((X4/0.95),2)))-Z4)<=0.02,"Correct","Check"),IF(W4="D",
IF(ABS(IF(AND(VLOOKUP(J4,data,3,FALSE)>X4,X4/0.95<=VLOOKUP(J4,data,3,FALSE)
,VLOOKUP(J4,data,3,FALSE)<(X4/0.84)),(X4/0.95)+(((VLOOKUP(J4,data,3,FALSE)
-(X4/0.95))*0.4))-Z4))<=0.02,"Correct","Check"),"")))

and to do anything in the spreadsheet takes along time. So I'm going to put the above into vba so it only works it out when I click a button

Cheers
Chris

 

[xlbo]

don't do the calc in VBA. Hold the formula string somewhere and apply it to the cells using VBA, then copy / paste values - it will work faster that way as otherwise you will need to loop through every row, calculating the value in code 1 at a time...ssssslllllllloooooooooooo

http://wwwwwww

dim strForm as string

strForm = "=IF(W4="A",IF(ABS etc..."

with Range("D2:D10000")
   .formula = strForm
   .copy
   .pastespecial xlvalues
  application.cutcopymode = false
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Walshie1987]

Thats just made things alot easier!!

Cheers Geoff!

 

[electricpete]

and to do anything in the spreadsheet takes along time. So I'm going to put the above into vba so it only works it out when I click a button

Another way to skin that cat: you can set recalculation to manual (tools / options / calculation) and then the worksheet only calculates when you ask it to (by pressing F9).

 

[Gavona]

I use a modification to Geoff's approach as a more generic solution. I have a general purpose bit of code and don't need to hold the formula in VBA. I include range(xxx).calculate to calculate the formulae (not the entire workbook) before converting to values.

I store the formula in the workbook two rows above the heading for the column.
I then use the column heading to name the range where I want to copy the formula (Insert,Name,Create does that).

I select the column headings concerned and then run this macro.

Sub FormulaCopy()
'MyName is the name of the range to which data is to be copied _
 it does not include the heading.
'The formulae are 2 rows above the heading i.e.
'   Formula
'   [blank]
'   Heading
'   first cell of range named ....

Dim Myprompt As String, Response As String, Style As Integer

Myprompt = "For each cell in current selection the macro copies formulae in the second row above the selected cell to all cells below the selected cell. (Actually there must be a named range equal to the text in the selected cell and it is this range that is copied to.  The routine calculates the pasted cells (only) and then pastes them to values"
Style = vbOKCancel + vbCritical + vbDefaultButton2    ' Define buttons.
Response = MsgBox(Myprompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each c In Selection
Application.StatusBar = "Applying Formulae to " + c.Value
With Range(Replace(c.Value, " ", "_"))
    .Cells(-2, 1).Copy
    .Cells.PasteSpecial (xlPasteAll)
    .Calculate
    .Cells.Copy
    .Cells.PasteSpecial (xlPasteValues)
End With
Next c

Myprompt = "Macro Finished - calculation will be set to automatic"
Style = vbOK + vbCritical + vbDefaultButton2   ' Define buttons.
Response = MsgBox(Myprompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub

Application.StatusBar = "Setting calculation to automatic"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub

Gavin

 

[Walshie1987]

Thanks for that Gavona, very useful. Just going back to Xlbo's comment, everytime i enter:

dim strForm as string

strForm = "=IF(W4="A",IF(ABS etc..."

with Range("D210000")
.formula = strForm
.copy
.pastespecial xlvalues
application.cutcopymode = false
end with

I get a Syntax error, any ideas? possibly something to do with the "A" in the formula?

Cheers
Chris

 

[xlbo]

text strings in a formula need to be double quoted as otherwise VBA believes the " to be the end of a text string

""A"" will let VBA know that you want a text string within a text string

strForm = "=IF(W4=""A"",IF(ABS etc..."

apologies for not getting that right in my example

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Walshie1987]

Cheers Geoff, never knew that

Chris

 

[Walshie1987]

Last question!

Could you correct this anyone?

Private Sub CommandButton1_Click()

For r = 4 To 10

Dim FormulaA As String
Dim FormulaB As String

FormulaA = XXXXX
FormulaB = XXXXX

If Cells(r, "W") = "A" Then

With Range("AF4:AF10")
.Formula = FormulaA
.Copy
.PasteSpecial xlValues
Application.CutCopyMode = False
End With

ElseIf Cells(r, "W") = "B" Then

With Range("AF4:AF10")
.Formula = FormulaB
.Copy
.PasteSpecial xlValues
Application.CutCopyMode = False
End With
End If

Next r
End Sub

Hope you get the general idea of what im aiming for, and hopefully someone can help?

Cheers

 

[xlbo]

can't read your mind - what error are you getting ? what are you trying to do?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

If calculation is set to manual then your formulae will not get calculated before getting converted to values.
Add .calculate before .copy

Your post reminded me that one of the things I hated about putting the formula into the code was having to double up the quotes.

As Geoff said, what problem are you getting? Have you tried stepping through the code with the watch window in use to tell you exactly what is going on? You could watch
Cells(r, "W").value for example.

Regards.


Gavin

 

[Walshie1987]

Sorry I didn't explain very well, basically I want the code to do the following

if cell x = a then put formulaA in cell y
if cell x = b then put formulaB in cell y

the above code is FormulaA in all the cells, then overwriting the same cells with FormulaB. I need all cells that = a to have formulaA next to them, and all cells thata = b to have formulaB next to them.

Hope I've made things a bit clearer

Chris

 

[xlbo]

that is because you are testing a single cell and then applying the formula to the whole range:

If Cells(r, "W") = "A" Then

With [b]Range("AF4:AF10")[/b]

This should do what you need:

If Cells(r, "W") = "A" Then

With [b]Cells(r, "AF") [/b]

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Walshie1987]

Thanks Geoff, should have spotted that

Chris

 

[Walshie1987]

Sorry to keep asking all these questions, but this method is proving very very useful!

How would I put a variant in the above?

At the minute the code is putting the same formula into each cell e.g

in cell AF1 --> =if(D1="test","1","2")
in cell AF2 --> =if(D1="test","1","2")
in cell AF3 --> =if(D1="test","1","2")

instead of:
in cell AF1 --> =if(D1="test","1","2")
in cell AF2 --> =if(D2="test","1","2")
in cell AF3 --> =if(D3="test","1","2")

Obviously I'd get the same in result in every cell in column A if the top formula was pasted in, anyone have any ideas how I can make the references in the formulas change?

Cheers

 

[RivetHed]

Use FormulaR1C1 style notation instead.

 

[xlbo]

If you apply a formula to a range of more than 1 row, the row reference will automatically increment (as you had before)

To do it a row at a time, you just need to manipulate the formula string using your row incrementer:

"=if(D" & r & "=""test"",1,2)"


...or use R1C1 style notation (as per RivetHed) which is relative by default...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question