Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
hi, i am trying to paste formulas down a column without having to paste 1
section at a time. although I have the offset working to do that, I need to skip rows where column A has a period ".' in that column. the related item i have is not working for that, as below. thanks. Sub test() 'alt-T (test) Dim C4 As String C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1 If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'this line incorrect for this purpose 'need to skip all rows that have a period "." in col A Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
i see issues, one because i don't know exactly what you're trying to do and also
because the code seems to overwrite anything you may have in column A. but. maybe this will give you some ideas. 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("A1:A1000") If ws.Range("A" & cell.Row).Value = "." Then 'do nothing Else 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 -- Gary Keramidas Excel 2003 "Dan" wrote in message ... hi, i am trying to paste formulas down a column without having to paste 1 section at a time. although I have the offset working to do that, I need to skip rows where column A has a period ".' in that column. the related item i have is not working for that, as below. thanks. Sub test() 'alt-T (test) Dim C4 As String C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1 If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'this line incorrect for this purpose 'need to skip all rows that have a period "." in col A Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
hi, thanks much for the reply, not sure yet if your example does what want
yet, takes me much time to make them work. sounds like might not: as my formula sat it works for all below, except skip rows, goal: 1. edit formula in work "row" at top (row 9), then hit cntrl-c or copy (1 cell) 2. place cursor in top row cell (where work starts/ maybe line 200) same column copy formula to all rows below starting where i place my cursor. (since top row picked is permanent, i put cursor 1 row below that & minus 1 from rows formula). 3. hit alt-"T" (as my shorcut is set), to paste to all rows below that. (my macro does that, but want to skip rows where column A has a period ".") suspect your IF condition might do that, or if what don't get yet.. all of your example might take me a few days to work that out, unless you see what I mean before hand, thanks. my macro works as is, in any column, as I want for dynamic use for a quick paste tool. copy is done by hand. "Gary Keramidas" wrote: i see issues, one because i don't know exactly what you're trying to do and also because the code seems to overwrite anything you may have in column A. but. maybe this will give you some ideas. 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("A1:A1000") If ws.Range("A" & cell.Row).Value = "." Then 'do nothing Else 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 -- Gary Keramidas Excel 2003 "Dan" wrote in message ... hi, i am trying to paste formulas down a column without having to paste 1 section at a time. although I have the offset working to do that, I need to skip rows where column A has a period ".' in that column. the related item i have is not working for that, as below. thanks. Sub test() 'alt-T (test) Dim C4 As String C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1 If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'this line incorrect for this purpose 'need to skip all rows that have a period "." in col A Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub . |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
temporary correction of my macro would be to skip the line that does not work,
(but that means it will paste to all lines, undesired), skip: ' If Me.Cells(.Row, "A").Value = "." Then Exit Sub for what macro is now, is exactly what want / doing. sorry didn't explain better. "Gary Keramidas" wrote: i see issues, one because i don't know exactly what you're trying to do and also because the code seems to overwrite anything you may have in column A. but. maybe this will give you some ideas. 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("A1:A1000") If ws.Range("A" & cell.Row).Value = "." Then 'do nothing Else 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 -- Gary Keramidas Excel 2003 "Dan" wrote in message ... hi, i am trying to paste formulas down a column without having to paste 1 section at a time. although I have the offset working to do that, I need to skip rows where column A has a period ".' in that column. the related item i have is not working for that, as below. thanks. Sub test() 'alt-T (test) Dim C4 As String C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1 If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'this line incorrect for this purpose 'need to skip all rows that have a period "." in col A Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Set Printable area to skip columns | Excel Discussion (Misc queries) | |||
Why does paging down skip ten rows? | Excel Worksheet Functions | |||
skip rows | Excel Discussion (Misc queries) | |||
skip rows | Excel Worksheet Functions | |||
Quick Macro question - How to delete two rows then skip one - and repeat | Excel Discussion (Misc queries) |