Find last column and copy formulas

[primagic]

All my content will be stored in the following range:

Range("S5:AD49")

I have the following code which takes the formulas from the first column and paste them into the next column, then copies the values in the first column and pastes over themselves.

Range("S5:S49").Select
    Selection.Copy
    Range("T5:T49").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("S5:S49").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

How can I modify this code to make it look for the next column to the right that doesn't have data (within the specified range S5:AD49 and perform the copy and paste as above?

 

[SkipVought]

Hi,

'
    range("S5:S49").Copy
    Range("T5:Ad49").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

 

[SkipVought]

Oh, I didn see the last part to copy/PasteSpecial values...

'
    range("S5:S49").Copy
    Range("T5:Ad49").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    With range("S5:Ad49")
        .copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False 
    End with

 

[ck1999]

If I understand correctly try this

Sub tests()
  
    For counter = 19 To 30

        If Application.CountA(Range(Cells(5, counter), Cells(49, counter))) > 0 Then
            Cells(5, counter) = 1
        Else
            emptycol = counter
            counter = 30
        End If
        
    Next counter
       
    Range("s5:s49").Copy
    Range(Cells(5, emptycol), Cells(49, emptycol)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    
    Range("S5:S49").Copy
    Range("S5:S49").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub

if you need the column to copy as the last column with data then change the 3 locations of Range("s5:s49") to Range(Cells(5, emptycol-1), Cells(49, emptycol-1))

Skip may have a more elegant way to determine empty columns.

Chris

 

[zelgar]

Unless your formula are using absolute references or only numbers (no cell references), you don't want to do just a copy & paste to move the formula, but a CUT and paste instead. For example, if the formula in cells S5 is "= A5*6", copying it to cell T5 would result in a formula of "=B5*6". If S5 is "=$A5*6" then the copy/paste will work without any trouble. The following code should compare everything from Col T to AD with the Column before it (i.e., S to AC).

For iCol = 20 To 30
    For iRow = 5 To 49
        If Cells(iRow, iCol) = "" Then
            Cells(iRow, iCol - 1).Cut _
                 Destination:=Cells(iRow, iCol)
            Cells(iRow, iCol - 1) = Cells(iRow, iCol)
        End If
    Next iRow
Next iCol

Since it appears the purpose of this macro is to fill holes in your data, I would think that it would be better not to move the formula, but just to copy the data from the previous column and highlight the cell in some way.

For iCol = 20 To 30
    For iRow = 5 To 49
        If Cells(iRow, iCol) = "" Then
            Cells(iRow, iCol ) = Cells(iRow, iCol - 1)
            Cells(iRow, iCol).Interior.Color = RGB(255, 192, 203)
        End If
    Next iRow
Next iCol