Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
4th time lucky? | Excel Discussion (Misc queries) | |||
Counting Days between Accidents (Third time lucky?) | Excel Discussion (Misc queries) | |||
Lucky Draw | Excel Discussion (Misc queries) |