ExcelBanter - VBA instead of lookupformula

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA instead of lookupformula (https://www.excelbanter.com/excel-programming/302719-vba-instead-lookupformula.html)

VBA instead of lookupformula

Hi,

Is it possible to do lookup in VBA instead of a formula?

I have a workbook with a huge amount of lookupformulas and therefore the
size of the workbook is so big that it gets unstabile.

If I write in code, will the size decrease?
If so, can anyone give me a little help in how to do that?

Any help is appreciated!

//Thomas

VBA instead of lookupformula

Hi Don!

Could you please help me understand by convert this formula into VBA:
=IF(A$21=0;"";IF(B6<A$21;"";LOOKUP(A$21;B6;C6)))

Thanks in advance

//Thomas

"Don Guillett" skrev i meddelandet
...
Should help. Just reference the range in the vba or use find & offset
range("a4")=application.vlookup(range("a1"),range( "b2:x100"),2,false)

--
Don Guillett
SalesAid Software

"Jonsson" wrote in message
...
Hi,

Is it possible to do lookup in VBA instead of a formula?

I have a workbook with a huge amount of lookupformulas and therefore the
size of the workbook is so big that it gets unstabile.

If I write in code, will the size decrease?
If so, can anyone give me a little help in how to do that?

Any help is appreciated!

//Thomas

VBA instead of lookupformula

Hi Don!

Sorry, but I can't make it work!

When I paste the code you gave me, into the sheets VBA, as I assume I should
do, I get a errormessage that says:
"undifined variable" at "mrng". I'm not so good at VBA, so I hope you have
patiens with me.
I use the swedish version, maybe thats the problem?

//Thomas

"Don Guillett" skrev i meddelandet
...
just use the example I gave you, modified to suit
OR modify this idea to suit. Formula is created,copied down,changed to
value

Sub balance()
Set mrng = Range("h8:h" & Range("a65536").End(xlUp).Row)
With mrng
Formula="=IF(A$21=0;"";IF(B6<A$21;"";LOOKUP(A$21; B6;C6)))"
' .Formula = "=h7+d8"
.Formula = .Value
End With
End Sub

--
Don Guillett
SalesAid Software

"Jonsson" wrote in message
...
Hi Don!

Could you please help me understand by convert this formula into VBA:
=IF(A$21=0;"";IF(B6<A$21;"";LOOKUP(A$21;B6;C6)))

Thanks in advance

//Thomas

"Don Guillett" skrev i meddelandet
...
Should help. Just reference the range in the vba or use find & offset
range("a4")=application.vlookup(range("a1"),range( "b2:x100"),2,false)

--
Don Guillett
SalesAid Software

"Jonsson" wrote in message
...
Hi,

Is it possible to do lookup in VBA instead of a formula?

I have a workbook with a huge amount of lookupformulas and therefore
the
size of the workbook is so big that it gets unstabile.

If I write in code, will the size decrease?
If so, can anyone give me a little help in how to do that?

Any help is appreciated!

//Thomas

VBA instead of lookupformula

Thomas, this is NOT a VBA solution.. just an alternative:

If you have a lot of repeating formulas,

size decrease can be achieved by defining the formula as a Name Object,
then using the named formula in your sheet.

Since the formula is likely to have RELATIVE cell references, you need to
be carefull: before calling the Define Name dialog, you need to activate a
cell in the column from which the formula will be called....

hope you know how to work with defined names (and defined formulas)
else: spend some time studying it in help.. it will be worth it.

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

"Jonsson" wrote:

Hi,

Is it possible to do lookup in VBA instead of a formula?

I have a workbook with a huge amount of lookupformulas and therefore the
size of the workbook is so big that it gets unstabile.

If I write in code, will the size decrease?
If so, can anyone give me a little help in how to do that?

Any help is appreciated!

//Thomas

VBA instead of lookupformula

FWIW, following is an abandoned attempt I once made to improve vlookup.
This may not be useful to OP but perhaps anyone may tweak it to make a
killer replacement - faster and more versatile. I have sheets with
hundreds of rows by hundreds of columns of vlookups and I bet that I'm
not alone in the crawling speed pain.
----------------------------------
Option Explicit
Dim gResult

Function MyVlookupAlpha(sStr As String, rangeWhere As Range, iColOffset
As Integer)
Dim rangeWhere2 As Range, c As Range
'NOTE! Only supply 1 column to the function - e.g. ("X",A1:A9,3), NOT
("X",A1:E9,3)
'Remember that the last arg (iColOffset) is 1-based, like idiotic
vlookup
Stop 'this line really helps debugging functions, vs. subs
'Set rangeWhere2 = rangeWhere.Columns(1)
For Each c In rangeWhere 'rangewhere2 doesn't work! JEEZ!
If c.Value = sStr Then
'Debug.Print c.Offset(0, iColOffset - 1).Value
MyVlookupAlpha = c.Offset(0, iColOffset - 1).Value
Exit Function
End If
Next c
MyVlookupAlpha = 0 'consider "" in another variation, etc.
End Function
Sub MySubVlookupAlpha(sStr As String, rangeWhere As Range, iColOffset As
Integer)
'sample data follows for MyVLOOKUPAlpha("Y1995M1",b2:d6,3)
'Dim sStr As String
'Dim rangeWhere As Range
'Dim iColOffset As Integer
'sStr = "Y1995M1"
'Set rangeWhere = ActiveSheet.Range("b2:d6")
'iColOffset = 3

Dim rangeWhere2 As Range, c As Range
Stop 'this line really helps debugging functions, vs. subs
Set rangeWhere2 = rangeWhere.Columns(1)
Set c = rangeWhere2.Find(sStr, LookIn:=xlValues, lookat:=xlWhole)
Stop
'For Each c In rangeWhe Debug.Print c.Value: Next
If c Is Nothing Then
gResult = 0 'consider "" in another variation, etc.
Else
Debug.Print c.Offset(0, iColOffset).Value
gResult = c.Offset(0, iColOffset).Value
End If
Set c = rangeWhere2.Find(sStr, LookIn:=xlFormulas, lookat:=xlPart) '
to "reset"
End Sub