Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set the range?
I have the data("a1:k38221"). This data keeps on fluctuating. The column L
has the Vlookup formula. Since the number of rows in data keeps on changing evrytime I import , I need to maually set the column range in Column L to copy the Vlookup function. Below is the program which I use to copy the Vlookup function into Column L range("l2").Activate range("l2").FormulaR1C1 = "=VLOOKUP(RC[-1],EDI_PCAT!R1C3:R318C4,2,FALSE)" range("l2").Select Selection.Copy ActiveWindow.ScrollRow = 33784 ActiveCell.range("L1:L38221").Select ActiveSheet.Paste So is there a way where I could set the range automatically in column L based on data i.e instead of typing in activecell.range("L1:L38....").select everytime in column L, can I automate th selection based on the data range |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set the range?
hi
if the data is variable then the problem is to find the last row...this time. and then again next time. Dim lr As Long lr = Cells(Rows.Count, "A").End(xlUp).Row 'MsgBox lr ' test purposes only then assing the lr to the range Range("L1:L" & lr).Select accually you need to start avoiding the select function whenever you can. slows things down, causes extra typing and restricts you to activesheet. instead of .... range("l2").Select Selection.Copy use... range("l2").Copy instead of.... ActiveCell.range("L1:L38221").Select ActiveSheet.Paste use... range("L1:L" & lr).PasteSpecial xlpasteall regards FSt1 "Abhinandan" wrote: I have the data("a1:k38221"). This data keeps on fluctuating. The column L has the Vlookup formula. Since the number of rows in data keeps on changing evrytime I import , I need to maually set the column range in Column L to copy the Vlookup function. Below is the program which I use to copy the Vlookup function into Column L range("l2").Activate range("l2").FormulaR1C1 = "=VLOOKUP(RC[-1],EDI_PCAT!R1C3:R318C4,2,FALSE)" range("l2").Select Selection.Copy ActiveWindow.ScrollRow = 33784 ActiveCell.range("L1:L38221").Select ActiveSheet.Paste So is there a way where I could set the range automatically in column L based on data i.e instead of typing in activecell.range("L1:L38....").select everytime in column L, can I automate th selection based on the data range |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set the range?
Hi
Your suggestion works like magic. thanks very much. "FSt1" wrote: hi if the data is variable then the problem is to find the last row...this time. and then again next time. Dim lr As Long lr = Cells(Rows.Count, "A").End(xlUp).Row 'MsgBox lr ' test purposes only then assing the lr to the range Range("L1:L" & lr).Select accually you need to start avoiding the select function whenever you can. slows things down, causes extra typing and restricts you to activesheet. instead of .... range("l2").Select Selection.Copy use... range("l2").Copy instead of.... ActiveCell.range("L1:L38221").Select ActiveSheet.Paste use... range("L1:L" & lr).PasteSpecial xlpasteall regards FSt1 "Abhinandan" wrote: I have the data("a1:k38221"). This data keeps on fluctuating. The column L has the Vlookup formula. Since the number of rows in data keeps on changing evrytime I import , I need to maually set the column range in Column L to copy the Vlookup function. Below is the program which I use to copy the Vlookup function into Column L range("l2").Activate range("l2").FormulaR1C1 = "=VLOOKUP(RC[-1],EDI_PCAT!R1C3:R318C4,2,FALSE)" range("l2").Select Selection.Copy ActiveWindow.ScrollRow = 33784 ActiveCell.range("L1:L38221").Select ActiveSheet.Paste So is there a way where I could set the range automatically in column L based on data i.e instead of typing in activecell.range("L1:L38....").select everytime in column L, can I automate th selection based on the data range |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |