| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB instead of function for INDEX
HI - I am using code to paste in this formula "=IF(ISNA(INDEX(Data,B$3,$IV8)),
"",(INDEX(Data,B$3,$IV8)))" and then copy it around the spreadsheet, but it is clumsy and has a long processing time because it is a large spreadsheet. The spreadsheet is a calendar for a list of employees performing an action so the value in B3 is a Julian Date and the value in IV is an employee number. How do you write code to behave as an INDEX function and have each cell look for the value in the range of Data? Any small example will help tremendously. Thanks in Advance Carrie I think if I can a handle on this I can also apply it to VLookups that are monsterous and solve many of my other issues as well. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200802/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Hi Carrie
Show us the code you are using now, maybe it can be speeded up a bit. Regards, Per On 3 Feb., 03:03, "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote: HI - I am using code to paste in this formula "=IF(ISNA(INDEX(Data,B$3,$IV8)), "",(INDEX(Data,B$3,$IV8)))" and then copy it around the spreadsheet, but it is clumsy and has a long processing time because it is a large spreadsheet. The spreadsheet is a calendar for a list of employees performing an action so the value in B3 is a Julian Date and the value in IV is an employee number. How do you write code to behave as an INDEX function and have each cell look for the value in the range of Data? Any small example will help tremendously. Thanks in Advance Carrie I think if I can a handle on this I can also apply it to VLookups that are monsterous and solve many of my other issues as well. -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Hi Per-
Here is the code, most of it is just setting up the calendar according to a dialog box option. It is the code near the bottom where I am pasting the formula that I don't like. I was hoping to get someone to help me understand how to write that portion in VB rather than just "pasting" a formula. Any help or more than understanding I guess would be greatly appreciated. I am guessing it would be performed with an If / and some kind of loop but I just don't know how to get there. Thx for any help you can offer Private Sub OptionButton1_Click() Sheets("Calendar").Select Range("Clear_Calendar").Select Selection.Clear Range("B5:IT7").Select Selection.Delete Shift:=xlUp Range("A5").Select Sheets("Dates").Select Application.Goto Reference:="January_2008" Selection.Copy Sheets("Calendar").Select Range("B4").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="February_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="March_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="April_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="May_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="June_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="July_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="August_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("B8").Select ActiveCell.FormulaR1C1 = _ "=IF(ISNA(INDEX(Data,R3C,RC256)),"""",(INDEX(Data, R3C,RC256)))" Selection.Copy Range("B8:IS50").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Calculate Sheets("Dates").Select Range("A1").Select Sheets("Calendar").Select Range("A3").Select UserForm1.Hide Range("B3:IT4").Select Selection.Font.ColorIndex = 2 Range("b8").Select Call MergeCells End Sub Per Jessen wrote: Hi Carrie Show us the code you are using now, maybe it can be speeded up a bit. Regards, Per HI - I am using code to paste in this formula "=IF(ISNA(INDEX(Data,B$3,$IV8)), "",(INDEX(Data,B$3,$IV8)))" and then copy it around the spreadsheet, but it [quoted text clipped - 12 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200802/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Hi Carrie There is two things that wíll speed up your code. Set screenUpdating =False at the start of your code ( make sure to set it true at the end of the macro). You don't need to select a range before manipulating it. I.e range("A1:A10").delete is much faster than Range("A1:A10").select Selection.Delete You can also use this aproach to paste the formula. I have changed the code according to the above in the first part of the code and the part pasting the formula. The rest is up to you:-) Private Sub OptionButton1_Click() Application.ScreenUpdating = False Sheets("Calendar").Select Range("Clear_Calendar").Clear Range("B5:IT7").Delete Shift:=xlUp Range("A5").Select Sheets("Dates").Select Application.Goto "January_2008" Selection.Copy Sheets("Calendar").Range("B4").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="February_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).End(xlToRight).Select ActiveCell.Offset(-2, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="March_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="April_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="May_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="June_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="July_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="August_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("B8:IS50").FormulaR1C1 = _ "=IF(ISNA(INDEX(Data,R3C,RC256)),"""", (INDEX(Data,R3C,RC256)))" Calculate Sheets("Dates").Select Range("A1").Select Sheets("Calendar").Select 'Range("A3").Select UserForm1.Hide Range("B3:IT4").Font.ColorIndex = 2 Range("b8").Select Call MergeCells Application.ScreenUpdating = True End Sub Regards, Per Hi Per- Here is the code, most of it is just setting up the calendar according to a dialog box option. It is the code near the bottom where I am pasting the formula that I don't like. I was hoping to get someone to help me understand how to write that portion in VB rather than just "pasting" a formula. Any help or more than understanding I guess would be greatly appreciated. I am guessing it would be performed with an If / and some kind of loop but I just don't know how to get there. Thx for any help you can offer Per Jessen wrote: Hi Carrie Show us the code you are using now, maybe it can be speeded up a bit. Regards, Per HI - I am using code to paste in this formula "=IF(ISNA(INDEX(Data,B$3,$IV8)), "",(INDEX(Data,B$3,$IV8)))" and then copy it around the spreadsheet, but it [quoted text clipped - 12 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Per -
Thank you for the tips they will certainly help. But I am still left with the question of is there a way to write code rather than copy & paste the INDEX function? Or maybe this, do you know where the code is stored behind the INDEX function and can I get into it to look at it? Carrie Per Jessen wrote: Hi Carrie There is two things that wÃ*ll speed up your code. Set screenUpdating =False at the start of your code ( make sure to set it true at the end of the macro). You don't need to select a range before manipulating it. I.e range("A1:A10").delete is much faster than Range("A1:A10").select Selection.Delete You can also use this aproach to paste the formula. I have changed the code according to the above in the first part of the code and the part pasting the formula. The rest is up to you:-) Private Sub OptionButton1_Click() Application.ScreenUpdating = False Sheets("Calendar").Select Range("Clear_Calendar").Clear Range("B5:IT7").Delete Shift:=xlUp Range("A5").Select Sheets("Dates").Select Application.Goto "January_2008" Selection.Copy Sheets("Calendar").Range("B4").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="February_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).End(xlToRight).Select ActiveCell.Offset(-2, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="March_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="April_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="May_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="June_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="July_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="August_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("B8:IS50").FormulaR1C1 = _ "=IF(ISNA(INDEX(Data,R3C,RC256)),"""", (INDEX(Data,R3C,RC256)))" Calculate Sheets("Dates").Select Range("A1").Select Sheets("Calendar").Select 'Range("A3").Select UserForm1.Hide Range("B3:IT4").Font.ColorIndex = 2 Range("b8").Select Call MergeCells Application.ScreenUpdating = True End Sub Regards, Per Hi Per- [quoted text clipped - 22 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200802/1 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Another trick would be to change Calculation to manual while the code is
running, then change it back at the end. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Per Jessen" wrote in message ... Hi Carrie There is two things that wíll speed up your code. Set screenUpdating =False at the start of your code ( make sure to set it true at the end of the macro). You don't need to select a range before manipulating it. I.e range("A1:A10").delete is much faster than Range("A1:A10").select Selection.Delete You can also use this aproach to paste the formula. I have changed the code according to the above in the first part of the code and the part pasting the formula. The rest is up to you:-) Private Sub OptionButton1_Click() Application.ScreenUpdating = False Sheets("Calendar").Select Range("Clear_Calendar").Clear Range("B5:IT7").Delete Shift:=xlUp Range("A5").Select Sheets("Dates").Select Application.Goto "January_2008" Selection.Copy Sheets("Calendar").Range("B4").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="February_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).End(xlToRight).Select ActiveCell.Offset(-2, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="March_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="April_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="May_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="June_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="July_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="August_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("B8:IS50").FormulaR1C1 = _ "=IF(ISNA(INDEX(Data,R3C,RC256)),"""", (INDEX(Data,R3C,RC256)))" Calculate Sheets("Dates").Select Range("A1").Select Sheets("Calendar").Select 'Range("A3").Select UserForm1.Hide Range("B3:IT4").Font.ColorIndex = 2 Range("b8").Select Call MergeCells Application.ScreenUpdating = True End Sub Regards, Per Hi Per- Here is the code, most of it is just setting up the calendar according to a dialog box option. It is the code near the bottom where I am pasting the formula that I don't like. I was hoping to get someone to help me understand how to write that portion in VB rather than just "pasting" a formula. Any help or more than understanding I guess would be greatly appreciated. I am guessing it would be performed with an If / and some kind of loop but I just don't know how to get there. Thx for any help you can offer Per Jessen wrote: Hi Carrie Show us the code you are using now, maybe it can be speeded up a bit. Regards, Per HI - I am using code to paste in this formula "=IF(ISNA(INDEX(Data,B$3,$IV8)), "",(INDEX(Data,B$3,$IV8)))" and then copy it around the spreadsheet, but it [quoted text clipped - 12 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
There's no way to view the code of a built in Excel function.
Do you want the INDEX to be automatic/dynamic? Or is it a one-time only lookup? If it's the former, VBA will never approach the speed of a native Excel function. One thing that may help (or may not, you have to test it) is to enter the formula for the entire range B8:IS50 in one shot instead of entering it in B8 then copy-pasting the formula to the larger range. The huge change will come from not selecting and activating things in code, and setting Application.ScreenUpdating to false at the top of the procedure and to true at the bottom. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:7f2b196ceb66c@uwe... Per - Thank you for the tips they will certainly help. But I am still left with the question of is there a way to write code rather than copy & paste the INDEX function? Or maybe this, do you know where the code is stored behind the INDEX function and can I get into it to look at it? Carrie Per Jessen wrote: Hi Carrie There is two things that wíll speed up your code. Set screenUpdating =False at the start of your code ( make sure to set it true at the end of the macro). You don't need to select a range before manipulating it. I.e range("A1:A10").delete is much faster than Range("A1:A10").select Selection.Delete You can also use this aproach to paste the formula. I have changed the code according to the above in the first part of the code and the part pasting the formula. The rest is up to you:-) Private Sub OptionButton1_Click() Application.ScreenUpdating = False Sheets("Calendar").Select Range("Clear_Calendar").Clear Range("B5:IT7").Delete Shift:=xlUp Range("A5").Select Sheets("Dates").Select Application.Goto "January_2008" Selection.Copy Sheets("Calendar").Range("B4").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="February_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).End(xlToRight).Select ActiveCell.Offset(-2, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="March_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="April_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="May_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="June_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="July_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Dates").Select Application.Goto Reference:="August_08" Selection.Copy Sheets("Calendar").Select ActiveCell.Offset(2, 0).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select ActiveCell.Offset(-2, 0).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("B8:IS50").FormulaR1C1 = _ "=IF(ISNA(INDEX(Data,R3C,RC256)),"""", (INDEX(Data,R3C,RC256)))" Calculate Sheets("Dates").Select Range("A1").Select Sheets("Calendar").Select 'Range("A3").Select UserForm1.Hide Range("B3:IT4").Font.ColorIndex = 2 Range("b8").Select Call MergeCells Application.ScreenUpdating = True End Sub Regards, Per Hi Per- [quoted text clipped - 22 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200802/1 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Jon - Thank you, I did in fact rewrite the original code as to what you and
Per suggested and I removed all the 'selects' out as well as the 'calculate' and put in the Application.ScreenUpdating top and bottom. Works much better. Also thank you for answering the built in Excel fuction question. I was asking the INDEX question not only for what I had here but also another worksheet where I have a VLookup 'copy & paste' scenario also. The range on this other worksheet is Column E8:AU65534 - Obviously it is too much to handle and takes forever before it times out. Even a small section really bogs down. The above tips and tricks will help out immensly but I need to get away from the function all together here. So off to my books to see if I can write a decent If / Loop instead. I suppose from no one directly answering my question, that VB code to act as these functions, specifically INDEX, means it is either a daunting task way over my head or not a good solution. Hope you can clarify that for me. Jon Peltier wrote: There's no way to view the code of a built in Excel function. Do you want the INDEX to be automatic/dynamic? Or is it a one-time only lookup? If it's the former, VBA will never approach the speed of a native Excel function. One thing that may help (or may not, you have to test it) is to enter the formula for the entire range B8:IS50 in one shot instead of entering it in B8 then copy-pasting the formula to the larger range. The huge change will come from not selecting and activating things in code, and setting Application.ScreenUpdating to false at the top of the procedure and to true at the bottom. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ Per - [quoted text clipped - 152 lines] Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
The standard response is to recommend using a database program if you are
using this much data, or at least use database commands to get the values you need from a database. Is there any way you can break down the large data sheet into smaller sheets, so lookups are searching smaller ranges for the data? Kind of like setting up some relational tables to reduce repeated data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:7f30022febf4e@uwe... Jon - Thank you, I did in fact rewrite the original code as to what you and Per suggested and I removed all the 'selects' out as well as the 'calculate' and put in the Application.ScreenUpdating top and bottom. Works much better. Also thank you for answering the built in Excel fuction question. I was asking the INDEX question not only for what I had here but also another worksheet where I have a VLookup 'copy & paste' scenario also. The range on this other worksheet is Column E8:AU65534 - Obviously it is too much to handle and takes forever before it times out. Even a small section really bogs down. The above tips and tricks will help out immensly but I need to get away from the function all together here. So off to my books to see if I can write a decent If / Loop instead. I suppose from no one directly answering my question, that VB code to act as these functions, specifically INDEX, means it is either a daunting task way over my head or not a good solution. Hope you can clarify that for me. Jon Peltier wrote: There's no way to view the code of a built in Excel function. Do you want the INDEX to be automatic/dynamic? Or is it a one-time only lookup? If it's the former, VBA will never approach the speed of a native Excel function. One thing that may help (or may not, you have to test it) is to enter the formula for the entire range B8:IS50 in one shot instead of entering it in B8 then copy-pasting the formula to the larger range. The huge change will come from not selecting and activating things in code, and setting Application.ScreenUpdating to false at the top of the procedure and to true at the bottom. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ Per - [quoted text clipped - 152 lines] Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via http://www.officekb.com |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Jon -
You are absolutely correct, I would never have chosen Excel for this project but sometimes getting folks to use other tools they are unfamiliar with is simply not supported. Hence, do the best you can in this environment. But utilizing more sheets with the data is a good idea and one I had not considered. Thanks for all your help. Carrie Jon Peltier wrote: The standard response is to recommend using a database program if you are using this much data, or at least use database commands to get the values you need from a database. Is there any way you can break down the large data sheet into smaller sheets, so lookups are searching smaller ranges for the data? Kind of like setting up some relational tables to reduce repeated data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ Jon - Thank you, I did in fact rewrite the original code as to what you and [quoted text clipped - 49 lines] Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200802/1 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VB instead of function for INDEX
Two suggestions I can see for now:
One, don't use functions in the worksheet, but do it all on arrays. So, transfer the relevant ranges to arrays and run the code (which can still be the same worksheet functions) on those arrays. If that is still slow then a good option might be (as suggested already) to use a database and I would suggest SQLite for that, as it is very fast, very simple and there is a good, free VB(A) wrapper. RBS "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:7f309b7e8d9f4@uwe... Jon - You are absolutely correct, I would never have chosen Excel for this project but sometimes getting folks to use other tools they are unfamiliar with is simply not supported. Hence, do the best you can in this environment. But utilizing more sheets with the data is a good idea and one I had not considered. Thanks for all your help. Carrie Jon Peltier wrote: The standard response is to recommend using a database program if you are using this much data, or at least use database commands to get the values you need from a database. Is there any way you can break down the large data sheet into smaller sheets, so lookups are searching smaller ranges for the data? Kind of like setting up some relational tables to reduce repeated data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ Jon - Thank you, I did in fact rewrite the original code as to what you and [quoted text clipped - 49 lines] Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200802/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
| Index Function | Excel Worksheet Functions | |||
| Index Function/Match Function | Excel Discussion (Misc queries) | |||
| Index function | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
