Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Specific Column
I have source code in place to find the lastcolumn, how do I decrement 3 or 4
columns... for example lastcolumn = "$H$4" I want to specify a range of "$E$4:$h$4" I am basically adding 3 columns and later on will like to select the range of columns just added to do additional programming on. Below is how I set my lastcol variable For Each cell In Range(Range("A4"), Range("IV4")) LastCol = cell.Address If cell = Empty Then Exit Sub End If Next cell Any help is greatly appreciated ToniS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Specific Column
cell.offset(0,-3).resize(1,4) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ToniS" wrote in message ... I have source code in place to find the lastcolumn, how do I decrement 3 or 4 columns... for example lastcolumn = "$H$4" I want to specify a range of "$E$4:$h$4" I am basically adding 3 columns and later on will like to select the range of columns just added to do additional programming on. Below is how I set my lastcol variable For Each cell In Range(Range("A4"), Range("IV4")) LastCol = cell.Address If cell = Empty Then Exit Sub End If Next cell Any help is greatly appreciated ToniS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Specific Column
On Aug 6, 3:44 pm, ToniS wrote:
I have source code in place to find the lastcolumn, how do I decrement 3 or 4 columns... for example lastcolumn = "$H$4" I want to specify a range of "$E$4:$h$4" I am basically adding 3 columns and later on will like to select the range of columns just added to do additional programming on. Below is how I set my lastcol variable For Each cell In Range(Range("A4"), Range("IV4")) LastCol = cell.Address If cell = Empty Then Exit Sub End If Next cell Any help is greatly appreciated ToniS You could do something like the following: Dim r1 As Range, r2 As Range Set r1 = Range("d1") 'your last column Set r2 = Range(r1.Offset(0, -3), r1) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Specific Column
Dim rng as Range
set rng = Range("IV4").End(xltoLeft).Offset(0,-3).Resize(1,4) demo'd from the immediate window set rng = Range("IV4").End(xltoLeft).Offset(0,-3).Resize(1,4) ? rng.Address $E$4:$H$4 -- Regards, Tom Ogilvy "ToniS" wrote: I have source code in place to find the lastcolumn, how do I decrement 3 or 4 columns... for example lastcolumn = "$H$4" I want to specify a range of "$E$4:$h$4" I am basically adding 3 columns and later on will like to select the range of columns just added to do additional programming on. Below is how I set my lastcol variable For Each cell In Range(Range("A4"), Range("IV4")) LastCol = cell.Address If cell = Empty Then Exit Sub End If Next cell Any help is greatly appreciated ToniS |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Specific Column
Thanks for everyone's input, I really appreciate it!! I am now getting the
right range, but the replace the formula to actual value is not working for me. Below is what I have. Dim tmpRange As Range Dim ReplaceFormulaRange As Range Dim rng As Range Set ReplaceFormulaRange = Range("IV4").End(xlToLeft).Offset(0, -2).Resize(1, 3) MsgBox ("replaceformrng= " & ReplaceFormulaRange.Address) Set tmpRange = Range("sheet2!a2:d2100") ReplaceFormulaRange.Copy ReplaceFormulaRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False tmpRange.EntireColumn.Delete The cells formula gets set to =VLOOKUP($A$5,Sheet2!#REF!,2,FALSE) If I hardcode the below range I get the correct results, the value from the temp table on sheet2 ' works ' Set ReplaceFormulaRange = Range("sheet1!F5:H09") ' need to change to a variable ' ' Set tmpRange = Range("sheet2!a2:d2100") ' ReplaceFormulaRange.Copy ' ReplaceFormulaRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' ' tmpRange.EntireColumn.Delete "Tom Ogilvy" wrote: Dim rng as Range set rng = Range("IV4").End(xltoLeft).Offset(0,-3).Resize(1,4) demo'd from the immediate window set rng = Range("IV4").End(xltoLeft).Offset(0,-3).Resize(1,4) ? rng.Address $E$4:$H$4 -- Regards, Tom Ogilvy "ToniS" wrote: I have source code in place to find the lastcolumn, how do I decrement 3 or 4 columns... for example lastcolumn = "$H$4" I want to specify a range of "$E$4:$h$4" I am basically adding 3 columns and later on will like to select the range of columns just added to do additional programming on. Below is how I set my lastcol variable For Each cell In Range(Range("A4"), Range("IV4")) LastCol = cell.Address If cell = Empty Then Exit Sub End If Next cell Any help is greatly appreciated ToniS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find the last specific value in a column? | Excel Discussion (Misc queries) | |||
Find specific column and copy row. | Excel Programming | |||
Find specific column titles and copy the column to new workboo | Excel Programming | |||
Find specific column titles and copy the column to new workboo | Excel Programming | |||
find a specific value in a column and write another column | Excel Discussion (Misc queries) |