| 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
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 |
|
#7
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 |
| 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 | |||
Hybrid Mode
