maybe i'll get lucky again
 

Could anyone point out where my formula copying is going bad on me? or is
there a much simpler way to accomplish this?

Thanks very much

)

================================================

Private Sub copyFormulas(aws As Worksheet, aRow As Integer)
'move UP one row, move across range, if cell is a formula copy to aRow

Dim rCurrent As Range

On Error GoTo errhdl

Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Do Until rCurrent.Value = ""
If rCurrent.HasFormula Then
'neither of these work...
'=========================
rCurrent.Copy (rCurrent.Offset(-1, 0))
'rCurrent.Offset(-1, 0).Formula = rCurrent.Formula
'==========================<<<<
End If
'next column
Set rCurrent = rCurrent.Offset(0, 1)
Debug.Print rCurrent.Cells(1).Column
Loop
Exit Sub

errhdl:
Stop

End Sub

--
kent eilers

maybe i'll get lucky again
 

This
rCurrent.Copy (rCurrent.Offset(-1, 0))

should not have parentheses.

rCurrent.Copy rCurrent.Offset(-1, 0)

The second should work.

Regards,
Tom Ogilvy



Kent Eilers wrote in message
...
Could anyone point out where my formula copying is going bad on me? or is
there a much simpler way to accomplish this?

Thanks very much

)

================================================

Private Sub copyFormulas(aws As Worksheet, aRow As Integer)
'move UP one row, move across range, if cell is a formula copy to aRow

Dim rCurrent As Range

On Error GoTo errhdl

Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Do Until rCurrent.Value = ""
If rCurrent.HasFormula Then
'neither of these work...
'=========================
rCurrent.Copy (rCurrent.Offset(-1, 0))
'rCurrent.Offset(-1, 0).Formula = rCurrent.Formula
'==========================<<<<
End If
'next column
Set rCurrent = rCurrent.Offset(0, 1)
Debug.Print rCurrent.Cells(1).Column
Loop
Exit Sub

errhdl:
Stop

End Sub

--
kent eilers

maybe i'll get lucky again
 

Kent,
Does this return a valid range object ?
Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Which line errors ?

NickHK


"Kent Eilers" wrote in message
...
Could anyone point out where my formula copying is going bad on me? or is
there a much simpler way to accomplish this?

Thanks very much

)

================================================

Private Sub copyFormulas(aws As Worksheet, aRow As Integer)
'move UP one row, move across range, if cell is a formula copy to aRow

Dim rCurrent As Range

On Error GoTo errhdl

Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Do Until rCurrent.Value = ""
If rCurrent.HasFormula Then
'neither of these work...
'=========================
rCurrent.Copy (rCurrent.Offset(-1, 0))
'rCurrent.Offset(-1, 0).Formula = rCurrent.Formula
'==========================<<<<
End If
'next column
Set rCurrent = rCurrent.Offset(0, 1)
Debug.Print rCurrent.Cells(1).Column
Loop
Exit Sub

errhdl:
Stop

End Sub

--
kent eilers

maybe i'll get lucky again
 

I believe so...


"Nick Cranham" wrote in message
...
Kent,
Does this return a valid range object ?
Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Which line errors ?

NickHK


"Kent Eilers" wrote in message
...
Could anyone point out where my formula copying is going bad on me? or
is
there a much simpler way to accomplish this?

Thanks very much

)

================================================

Private Sub copyFormulas(aws As Worksheet, aRow As Integer)
'move UP one row, move across range, if cell is a formula copy to
aRow

Dim rCurrent As Range

On Error GoTo errhdl

Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Do Until rCurrent.Value = ""
If rCurrent.HasFormula Then
'neither of these work...
'=========================
rCurrent.Copy (rCurrent.Offset(-1, 0))
'rCurrent.Offset(-1, 0).Formula = rCurrent.Formula
'==========================<<<<
End If
'next column
Set rCurrent = rCurrent.Offset(0, 1)
Debug.Print rCurrent.Cells(1).Column
Loop
Exit Sub

errhdl:
Stop

End Sub

--
kent eilers

maybe i'll get lucky again
 

yep - just ran through it again.

)

"Nick Cranham" wrote in message
...
Kent,
Does this return a valid range object ?
Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Which line errors ?

NickHK


"Kent Eilers" wrote in message
...
Could anyone point out where my formula copying is going bad on me? or
is
there a much simpler way to accomplish this?

Thanks very much

)

================================================

Private Sub copyFormulas(aws As Worksheet, aRow As Integer)
'move UP one row, move across range, if cell is a formula copy to
aRow

Dim rCurrent As Range

On Error GoTo errhdl

Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Do Until rCurrent.Value = ""
If rCurrent.HasFormula Then
'neither of these work...
'=========================
rCurrent.Copy (rCurrent.Offset(-1, 0))
'rCurrent.Offset(-1, 0).Formula = rCurrent.Formula
'==========================<<<<
End If
'next column
Set rCurrent = rCurrent.Offset(0, 1)
Debug.Print rCurrent.Cells(1).Column
Loop
Exit Sub

errhdl:
Stop

End Sub

--
kent eilers

maybe i'll get lucky again
 

thanks - it worked ))

"Tom Ogilvy" wrote in message
...
This
rCurrent.Copy (rCurrent.Offset(-1, 0))

should not have parentheses.

rCurrent.Copy rCurrent.Offset(-1, 0)

The second should work.

Regards,
Tom Ogilvy



Kent Eilers wrote in message
...
Could anyone point out where my formula copying is going bad on me? or
is
there a much simpler way to accomplish this?

Thanks very much

)

================================================

Private Sub copyFormulas(aws As Worksheet, aRow As Integer)
'move UP one row, move across range, if cell is a formula copy to
aRow

Dim rCurrent As Range

On Error GoTo errhdl

Set rCurrent = aws.Range(gcstrNameColumn & (aRow - 1))

Do Until rCurrent.Value = ""
If rCurrent.HasFormula Then
'neither of these work...
'=========================
rCurrent.Copy (rCurrent.Offset(-1, 0))
'rCurrent.Offset(-1, 0).Formula = rCurrent.Formula
'==========================<<<<
End If
'next column
Set rCurrent = rCurrent.Offset(0, 1)
Debug.Print rCurrent.Cells(1).Column
Loop
Exit Sub

errhdl:
Stop

End Sub

--
kent eilers