PDA

View Full Version : offset and vba



alexanderd
08-13-2006, 07:47 AM
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:

mdmackillop
08-13-2006, 08:03 AM
ActiveCell.Offset(-1, 3).Range("A1:D1").ClearContents

alexanderd
08-13-2006, 08:25 AM
ActiveCell.Offset(-1, 3).Range("A1:D1").ClearContents
unfortunately it dose not help, i should have included a work book. sample attached.

mdmackillop
08-13-2006, 08:29 AM
Your code needs to be run from a specific cell. Which cell is it?

alexanderd
08-13-2006, 08:40 AM
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 end

mdmackillop
08-13-2006, 09:20 AM
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.

alexanderd
08-13-2006, 09:43 AM
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.

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.

mdmackillop
08-13-2006, 12:39 PM
I'm not clear on the formatting you wish to clear. This will colour the range of cells you appear you wish to clear.

'//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

johnske
08-13-2006, 04:16 PM
I'm also a bit uncertain of what you're trying to clear, but this clears formats

Option Explicit

Sub SelectIt()
If Not ActiveCell.Row = 1 Then Range(ActiveCell.Offset(-1, 3), _
ActiveCell.Offset(-1, 6)).ClearFormats
End Sub

alexanderd
08-15-2006, 12:55 PM
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

johnske
08-15-2006, 03:58 PM
To just clear the formula, use 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

alexanderd
08-17-2006, 12:39 PM
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

mdmackillop
08-17-2006, 12:56 PM
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.

ActiveCell.Offset(, 1).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-3],eanretail!R3C1:R2058C3,3,FALSE))" & _
","""",VLOOKUP(RC[-3],eanretail!R3C1:R2058C3,3,FALSE))"