can any one suggest how i a can select a cell which is offset (-1,3) and select that cell and the 3 cells to the right and clear any formula from them.
what i have (and it will not work is)
if offset(-1,3). select then
.clear
endif:banghead:
Printable View
can any one suggest how i a can select a cell which is offset (-1,3) and select that cell and the 3 cells to the right and clear any formula from them.
what i have (and it will not work is)
if offset(-1,3). select then
.clear
endif:banghead:
[VBA]ActiveCell.Offset(-1, 3).Range("A1:D1").ClearContents[/VBA]
unfortunately it dose not help, i should have included a work book. sample attached.Quote:
Originally Posted by mdmackillop
Your code needs to be run from a specific cell. Which cell is it?
each time a new row is appended the cell in colum "A" is selected, that is why i am looking at "offset" for a means to an endQuote:
Originally Posted by mdmackillop
Cell changes are changing "Target" cells. Can you state what should happen when you insert a new value in Cell A20 as per your example.
in "A20" place any whole number "B20" input an value from worksheet "eanretail" "E20" will then fill with a value atomatically and the next line will appear with all the formula's from the previous row.Quote:
Originally Posted by mdmackillop
what i want to do is select cell "E20" an input a value not in "eanretail", having selected cells "F20:H20,E21:F21" and cleared any formatting.
I'm not clear on the formatting you wish to clear. This will colour the range of cells you appear you wish to clear.
[VBA]
'//select column B in the new row for next entry
Range("A" & Target.Row + 1).Select 'B
Changed = False
'//Formatting code
Dim r As Range
Set r = Union(Target.Offset(, 1).Range("A1:C1"), Target.Offset(1).Range("A1:B1"))
r.Interior.ColorIndex = 6
End If
[/VBA]
I'm also a bit uncertain of what you're trying to clear, but this clears formats
[vba]
Option Explicit
Sub SelectIt()
If Not ActiveCell.Row = 1 Then Range(ActiveCell.Offset(-1, 3), _
ActiveCell.Offset(-1, 6)).ClearFormats
End Sub
[/vba]
please see the attached i might explain what i would like to achieve better.
To all who have contributed i would like to thank them for their help and hope i have not missled thenm to much
To just clear the formula, use [VBA]Sub SelectIt()
If Not ActiveCell.Row = 1 Then
With Range(ActiveCell.Offset(-1, 3), ActiveCell.Offset(-1, 6))
.Value = .Value
End With
End If
End Sub[/VBA]
thanks to all who tried to help me in my hour of need, unfortunately nothing seemed to work, even with your help.
i am now looking at a different way to accomplish what i wanted to do (it is comming along). I will post the results once i have finnished it.
thanks to all once again
You mix up clear formulas with clear formatting, which is very confusing.
You can simplify things by writing the formula into colum E when you add the description, then there is no need to copy down, or to clear when the column E value is changed.
[VBA]
ActiveCell.Offset(, 1).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-3],eanretail!R3C1:R2058C3,3,FALSE))" & _
","""",VLOOKUP(RC[-3],eanretail!R3C1:R2058C3,3,FALSE))"
[/VBA]