(Msg. 1) Posted: Thu Apr 16, 2009 8:38 pm
Post subject: Copying formulas in range and pasting in a selection Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I cannot seem to figure out what is wrong with the following formula. I want
to select a range of cells, copy only the formulas in that range and then
paste only the formulas in the range("W8:BT60").
When I run this, if there are values in cells not containing formulas in the
destination range, the values disappear. Any help is appreciated. Thank you.
(Msg. 2) Posted: Fri Apr 17, 2009 6:32 am
Post subject: RE: Copying formulas in range and pasting in a selection [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
The paste special will replace everything in the destination range.
To not overwrite values in the destination range you need to check each cell
for a value.
Something like this
Sub test()
counter = 0
For Each cell In Range("Source")
counter = counter + 1
If Range("A1").Offset(0, counter).HasFormula = True Or
Range("A1").Offset(0, counter) = Empty Then
Range("A1").Offset(0, counter) = cell.Formula
End If
Next
End Sub
I am assuming that your source and destination ranges are the same size.
This will not overwrite values in the destination, only cells with formulas
or empty cells.
Change the "Source" to your source name and use V8 for your offset start.
--
If this helps, please remember to click yes.
"Billy B" wrote:
> I cannot seem to figure out what is wrong with the following formula. I want
> to select a range of cells, copy only the formulas in that range and then
> paste only the formulas in the range("W8:BT60").
>
> When I run this, if there are values in cells not containing formulas in the
> destination range, the values disappear. Any help is appreciated. Thank you.
>
> Range("GradeFormulas_ITC105").Select
> Range("GradeFormulas_ITC105").Copy
> Range("W8:BT8").Select
> Range("W8:BT8").PasteSpecial xlPasteFormulas, xlNone,
> SkipBlanks:=False, Transpose:=False
> Selection.AutoFill Destination:=Range("W8:BT60"), Type:=xlFillCopy
> Range("A8").Select
>
(Msg. 3) Posted: Fri Apr 17, 2009 6:41 am
Post subject: RE: Copying formulas in range and pasting in a selection [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
One note. The first version copies the exact formula and would not preserve
relative references. To keep the relative references intact you would need
to change the code like this. This appears to be more like your original
intent
Sub test()
counter = 0
For Each cell In Range("Source")
counter = counter + 1
cell.Copy
If Range("A1").Offset(0, counter).HasFormula = True Or
Range("A1").Offset(0, counter) = Empty Then
Range("A1").Offset(0, counter).PasteSpecial xlPasteFormulas
End If
Next
End Sub
--
If this helps, please remember to click yes.
"Paul C" wrote:
> The paste special will replace everything in the destination range.
>
> To not overwrite values in the destination range you need to check each cell
> for a value.
>
> Something like this
>
> Sub test()
>
> counter = 0
> For Each cell In Range("Source")
> counter = counter + 1
> If Range("A1").Offset(0, counter).HasFormula = True Or
> Range("A1").Offset(0, counter) = Empty Then
> Range("A1").Offset(0, counter) = cell.Formula
> End If
> Next
>
> End Sub
>
> I am assuming that your source and destination ranges are the same size.
> This will not overwrite values in the destination, only cells with formulas
> or empty cells.
>
> Change the "Source" to your source name and use V8 for your offset start.
> --
> If this helps, please remember to click yes.
>
>
> "Billy B" wrote:
>
> > I cannot seem to figure out what is wrong with the following formula. I want
> > to select a range of cells, copy only the formulas in that range and then
> > paste only the formulas in the range("W8:BT60").
> >
> > When I run this, if there are values in cells not containing formulas in the
> > destination range, the values disappear. Any help is appreciated. Thank you.
> >
> > Range("GradeFormulas_ITC105").Select
> > Range("GradeFormulas_ITC105").Copy
> > Range("W8:BT8").Select
> > Range("W8:BT8").PasteSpecial xlPasteFormulas, xlNone,
> > SkipBlanks:=False, Transpose:=False
> > Selection.AutoFill Destination:=Range("W8:BT60"), Type:=xlFillCopy
> > Range("A8").Select
> >
(Msg. 4) Posted: Fri Apr 17, 2009 10:52 am
Post subject: RE: Copying formulas in range and pasting in a selection [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
The code that allows relative references works fine if I only wanted to copy
formulas to one row because in the loop it looks like the reference always
goes back to A1.
What I need to do is copy the formulas down 24 rows starting on at V8 on the
worksheet.
Thanks
"Paul C" wrote:
> One note. The first version copies the exact formula and would not preserve
> relative references. To keep the relative references intact you would need
> to change the code like this. This appears to be more like your original
> intent
>
> Sub test()
>
> counter = 0
> For Each cell In Range("Source")
> counter = counter + 1
> cell.Copy
> If Range("A1").Offset(0, counter).HasFormula = True Or
> Range("A1").Offset(0, counter) = Empty Then
> Range("A1").Offset(0, counter).PasteSpecial xlPasteFormulas
> End If
> Next
>
> End Sub
> --
> If this helps, please remember to click yes.
>
>
> "Paul C" wrote:
>
> > The paste special will replace everything in the destination range.
> >
> > To not overwrite values in the destination range you need to check each cell
> > for a value.
> >
> > Something like this
> >
> > Sub test()
> >
> > counter = 0
> > For Each cell In Range("Source")
> > counter = counter + 1
> > If Range("A1").Offset(0, counter).HasFormula = True Or
> > Range("A1").Offset(0, counter) = Empty Then
> > Range("A1").Offset(0, counter) = cell.Formula
> > End If
> > Next
> >
> > End Sub
> >
> > I am assuming that your source and destination ranges are the same size.
> > This will not overwrite values in the destination, only cells with formulas
> > or empty cells.
> >
> > Change the "Source" to your source name and use V8 for your offset start.
> > --
> > If this helps, please remember to click yes.
> >
> >
> > "Billy B" wrote:
> >
> > > I cannot seem to figure out what is wrong with the following formula. I want
> > > to select a range of cells, copy only the formulas in that range and then
> > > paste only the formulas in the range("W8:BT60").
> > >
> > > When I run this, if there are values in cells not containing formulas in the
> > > destination range, the values disappear. Any help is appreciated. Thank you.
> > >
> > > Range("GradeFormulas_ITC105").Select
> > > Range("GradeFormulas_ITC105").Copy
> > > Range("W8:BT8").Select
> > > Range("W8:BT8").PasteSpecial xlPasteFormulas, xlNone,
> > > SkipBlanks:=False, Transpose:=False
> > > Selection.AutoFill Destination:=Range("W8:BT60"), Type:=xlFillCopy
> > > Range("A8").Select
> > >
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office