D
Dan
hi, learning but am not good at macros, want to save time updating formula's
in a column, steps:
1 i will manual copy cell at top,
2 want to select any column so dynamic (i will manually select 1st cell/ row
in desired paste col for a desired range),
3 paste formula's shortcut eg alt-T, skip rows where col A has a period "."
(thanks, note: new email works..)
Sub test() 'alt-T (test)
'want to: copy new formula from 1 cell at top,
'to all cells in 1 col skip rows with period "." in col A,
'1st 4 lines below work, but does not skip lines with period "." in col A
'and does what want: pick any col & hit alt-T shorcut
'Dim C4 As String
'C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select
'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Dim cell As Range
Dim ws As Worksheet
Dim C4 As Long 'this is a number since using it as an offset
Set ws = Worksheets("sym") ' Set ws = Worksheets("Sheet1")
C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'ERROR
For Each cell In ws.Range(C4) '(err: METHOD RANGE OF OBJECT WORKSHEET
FAILED) 'column manual selected / rows C4
If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
Else
'PROBLEM:
With ws.Range(ActiveCell, ws.Range(ActiveCell, ActiveCell &
cell.Row).Offset(C4, 0)) 'MODIFY
'With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0)) 'WRONG
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select 'ORIGINAL
WORKS (as below) PASTE ALL ROWS: NOT WANTED
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With
End If
Next
End Sub
'xxxxxxxxxx SAMPLE info: (not want copy col to col, not sure otherwise
works)
'Sub test() 'alt-T (test)
' Dim cell As Range
' Dim ws As Worksheet
' Dim C4 As Long 'this is a number since you're using it
as an offset
' Set ws = Worksheets("Sheet1")
' C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
' For Each cell In ws.Range(C4)
' If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
' Else
'no ws.Range("A" & cell.Row).Copy ' have no idea what you want to paste
' With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0))
' .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
' 'the above code pastes over values in column A.
' End With
' End If
' Next
'End Sub
in a column, steps:
1 i will manual copy cell at top,
2 want to select any column so dynamic (i will manually select 1st cell/ row
in desired paste col for a desired range),
3 paste formula's shortcut eg alt-T, skip rows where col A has a period "."
(thanks, note: new email works..)
Sub test() 'alt-T (test)
'want to: copy new formula from 1 cell at top,
'to all cells in 1 col skip rows with period "." in col A,
'1st 4 lines below work, but does not skip lines with period "." in col A
'and does what want: pick any col & hit alt-T shorcut
'Dim C4 As String
'C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select
'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Dim cell As Range
Dim ws As Worksheet
Dim C4 As Long 'this is a number since using it as an offset
Set ws = Worksheets("sym") ' Set ws = Worksheets("Sheet1")
C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'ERROR
For Each cell In ws.Range(C4) '(err: METHOD RANGE OF OBJECT WORKSHEET
FAILED) 'column manual selected / rows C4
If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
Else
'PROBLEM:
With ws.Range(ActiveCell, ws.Range(ActiveCell, ActiveCell &
cell.Row).Offset(C4, 0)) 'MODIFY
'With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0)) 'WRONG
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select 'ORIGINAL
WORKS (as below) PASTE ALL ROWS: NOT WANTED
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With
End If
Next
End Sub
'xxxxxxxxxx SAMPLE info: (not want copy col to col, not sure otherwise
works)
'Sub test() 'alt-T (test)
' Dim cell As Range
' Dim ws As Worksheet
' Dim C4 As Long 'this is a number since you're using it
as an offset
' Set ws = Worksheets("Sheet1")
' C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
' For Each cell In ws.Range(C4)
' If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
' Else
'no ws.Range("A" & cell.Row).Copy ' have no idea what you want to paste
' With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0))
' .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
' 'the above code pastes over values in column A.
' End With
' End If
' Next
'End Sub