Hi Steve
My first comment about your code is that Selects in macros should be avoided because they slow down execution and make the screen flicker.
c.Offset(0, -1).SelectSelection.Copy
could become :-
c.Offset(0, -1).Copy
When you record a macro all of the defaults appear in the code, so :-
c.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
may be simplified to :-
c.PasteSpecial Paste:=xlPasteFormulas
Here is how to find the last used cell in the range "B2:M2" :-
Set rLastUsedCellInRow = Range("B2:M2").SpecialCells(xlCellTypeBlanks).Offset(, -1).Cells(1, 1)
So :-
Sub PropagateFormulaToRightEmptyColumn()
With Sheets("Rej by Month").Range("B2:M2").SpecialCells(xlCellTypeBlanks).Offset(, -1).Cells(1, 1)
.Copy Destination:=Sheets("Rej by Month").Range(.Offset(, 1), .End(xlDown).Offset(, 1))
End With
End Sub
Regards
Derek Turner
+++
>________________________________
> From: sspatriots <sspatriots@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 26 April 2012, 15:57
>Subject: [ExcelVBA] Re: Copy column range of formulas to next column
>
>
>
>
>
>Got it! I must be getting better at this stuff...at least finding similar solutions on-line and making them work for me. Anyhow, below is what I came up with. If anyone sees that I have something that shouldn't be, please let me know, but it seems to work ok.
>
>Option Explicit
>Sub FindLastNonEmptyCellInRowRange()
>'
>' Find Last Non-Empty Cell In Row Range Copy Formulas Over From Col To Left Macro
>'
>
>'
>Application.DisplayAlerts = False
>Application.ScreenUpdating = False
>
>Sheets("Rej by Month").Select
>
>Dim c
>For Each c In Range("B2:M2").Cells
>If c = "" Then
>
>c.Offset(0, -1).Select
>Selection.Copy
>
>c.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
>SkipBlanks:=False, Transpose:=False
>
>Selection.Copy
>Selection.End(xlDown).Select
>ActiveCell.Offset(-1, 0).Range("A1").Select
>Range(Selection, Selection.End(xlUp)).Select
>ActiveSheet.Paste
>Selection.End(xlDown).Select
>
>Exit For
>End If
>Next
>
>Application.CutCopyMode = False
>
>Range("A1").Select
>
>Application.DisplayAlerts = True
>Application.ScreenUpdating = True
>
>End Sub
>
>--- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@...> wrote:
>>
>> Well I've managed to come up with the code below that gets me to the last cell with a formula in it in the second row. I just need to figure out how to tell it to copy it and the 38 cells with formulas directly below it over to the next column.
>>
>> Thanks,
>>
>> Steve
>>
>>
>>
>> Option Explicit
>> Sub FindLastNonEmptyCellInRowRange()
>> Dim c
>> For Each c In Range("B2:M2").Cells
>> If c = "" Then
>>
>> c.Offset(0, -1).Select
>>
>> Exit For
>> End If
>> Next
>> End Sub
>>
>> --- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@> wrote:
>> >
>> > Hi,
>> >
>> > I have a worksheet that has column headings by the month in the range 'B1:M1'. The first one is 'AUG' (B1), because that is the start of our fiscal year. Anyhow, right now I have formulas in the range 'B2:I40' that are populating data from another worksheet into that range. Columns 'B' through 'I' are the months from last 'AUG' through 'MAR', which is all I have data for at the moment. I don't want to put the formulas into 'J2:M40' until I get the data for the months of 'APR' through 'JUL' and I only want them added one month at a time as I obtain each months data.
>> >
>> > My reasoning for this is that this range of data gets fed into a bar chart with an exponential trendline. If those cells have anything in them (even a formula that makes them appear blank), the trendline option isn't available.
>> >
>> > So if I were to run the code today, I would want it to find the last range of formulas for the previous month's data (currently in 'I2:I40') and paste them into 'J2:J40' just before I bring my new data into the workbook for 'APR'. I hope this makes sense and that I've explained it clearly.
>> >
>> > Thanks in advance for any suggestions/ideas, because I've been scouring the web and not able to come up with anything close.
>> >
>> >
>> > Regards,
>> >
>> > Steve
>> >
>>
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar