MACRO COLUMNS, SKIP ROWS

[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

 

[Luke M]

I don't completely understand what's going on with C4 (is it a range? A
value? a string?)

Here's the basic format of the desired macro. Note that it still needs some
way of determining last cell in the row to copy to. I'm thinking you already
have some way of doing this, or a search for "find last used cell in column"
within these forums/google will provide the short code needed.

'============
Sub CopyCell()
r = ActiveCell.Row
c = ActiveCell.Column

'Assumes cell C4 determines last row in column
'Modify this to something else if needed
LastRow = Range("C4").Value

For Each c In Range(Cells(r, c), Cells(LastRow, c))
If Cells(c.Row, "A").Value <> "." Then
c.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Next c
End Sub
'================

 

[Dan]

hi thanks for the reply, sorry for that, C4 has number of rows down from
where would place cursor, not sure if my immediate method best way on that,
but does a bottom row minus top row. IF not to crazy, i would place my
cursor at top cell of column, (top / first row where work starts)

C4:
=ROW($A$2058)-ROW($A$228)-1

 

[Dan]

ok, did not see your example right away, may take me awhile to test, but the
first example, that i wrote works but does not skip rows where col A has a
period ".",
if you got that, it is:
(thanks)

Dim C4 As String
C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1
Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select
'skip rows col A has ".'

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Dan]

that works pretty neat, will give a check for that, but ultimately looking
for example that would paste to, basically whole column (in range) at the
same time.

can I get a modification for that (thanks much..........)

 

[Dan]

hello, I had recently received a macro from you that I was needing some help
with.
it seems to work on columns where there are formula's only. when i tried to
modify it to work with FORMATS, it did work on rows with formula's, but would
not paste FORMATS down on columns, without formula's.

Is there a way to modify it so that it will? if ok as a different macro,
since using separate macro's anyways... not sure what the problem is.. thanks

what using to try to paste Formats down a column (that does not have
formula's):

Sub PastecellF() 'alt-F (paste cell Format to col)
r = ActiveCell.Row
c = ActiveCell.Column
LastRow = Range("C4").Value

For Each c In Range(Cells(r, c), Cells(LastRow, c))
If Cells(c.Row, "A").Value <> "." Then
c.Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Selection.Cells.PasteSpecial xlPasteFormats
'Selection.PasteSpecial xlPasteFormats

'.Cells.PasteSpecial xlPasteValues
'PasteSpecial xlPasteValues

End If
Next c
End Sub

 

[Dan]

hello, I had recently received a macro from you that I was needing some help
with.
it seems to work on columns where there are formula's only. when i tried to
modify it to work with FORMATS, it did work on rows with formula's, but would
not paste FORMATS down on columns, without formula's.

Is there a way to modify it so that it will? if ok as a different macro,
since using separate macro's anyways... not sure what the problem is.. thanks

what using to try to paste Formats down a column (that does not have
formula's):

Sub PastecellF() 'alt-F (paste cell Format to col)
r = ActiveCell.Row
c = ActiveCell.Column
LastRow = Range("C4").Value

For Each c In Range(Cells(r, c), Cells(LastRow, c))
If Cells(c.Row, "A").Value <> "." Then
c.Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Selection.Cells.PasteSpecial xlPasteFormats
'Selection.PasteSpecial xlPasteFormats

'.Cells.PasteSpecial xlPasteValues
'PasteSpecial xlPasteValues

End If
Next c
End Sub