Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA instead of lookupformula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|