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
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
[VBA]ActiveCell.Offset(-1, 3).Range("A11").ClearContents[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
unfortunately it dose not help, i should have included a work book. sample attached.Originally Posted by mdmackillop
[vba]ActiveCell.Offset(-1, 3).Range("A1
Your code needs to be run from a specific cell. Which cell is it?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
