Find a Specific Column

[ToniS]

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

 

[Bob Phillips]

cell.offset(0,-3).resize(1,4)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ferris]

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)

 

[Tom Ogilvy]

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

 

[ToniS]

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