FillDown from unknown column
Thanks Mike. That was a great help. Here's the code I wound up with.
For some reason it didn't work if the sheet wasn't active.
Sub AddCAPPivotFields()
' Add Cap Pivot Fields
' Assumes first criteria is cost account
Dim oDatasheet As Worksheet
Dim LastRow As Long
Dim LastCol As Long
On Error Resume Next
Set oDatasheet = ActiveWorkbook.Worksheets("Data")
If oDatasheet Is Nothing Then
MsgBox "Active workbook doesn't appear to be a Cobra spreadsheet
report.", vbCritical
Exit Sub
End If
With oDatasheet
.Activate 'don't understand why this necessary
LastCol = .Cells.SpecialCells(xlLastCell).Column
.Cells(1, LastCol + 1).Formula = "=LEFT(A1,6)"
.Cells(1, LastCol + 2).Formula = "=LEFT(A1,FIND("" "",A1)-1)"
.Cells(1, LastCol + 3).Formula = "=MID(A1,FIND("" "",A1)+1,FIND(""
"",A1,FIND("" "",A1,FIND("" "",A1,FIND("" "",A1)+1)+1)+1)-FIND("" "",A1)-
1)"
.Cells(1, LastCol + 4).Formula = "=RIGHT(A1,LEN(A1)-FIND(""
"",A1,FIND("" "",A1,FIND("" "",A1,FIND("" "",A1)+1)+1)+1))"
rng1 = .Cells(1, LastCol + 1).Address
.Range(rng1, Range("A1").End(xlDown)).Offset(0, LastCol).FillDown
End With
End Sub
"Mike Fogleman" wrote in
:
Assuming the unknown columns are contiguous,
Sub UnknownFillDown()
Dim ColCount
Dim rng1
Sheet1.Range("A1").Activate
Selection.CurrentRegion.Select
ColCount = Selection.Columns.Count
ColCount = ColCount + 1
Sheet1.Cells(1, ColCount).Activate
Sheet1.Cells(1, ColCount).Formula = "=TRIM(A1)"
rng1 = ActiveCell.Address
Range(rng1, Range("A1").End(xlDown)).Offset(0, ColCount - 1).FillDown
End Sub
Adjust sheet name, ranges, and formula as needed,
Mike
"Woody" wrote in message
.119...
I'm adding a formula to a column that I don't know in advance, and I
need to fill the formula down the same number of rows as there are in
column A.
If I knew the column, I would do something like:
LastRow = Cells.SpecialCells(xlLastCell).Row
Range("F1:F" Trim(Str(LastRow))).FillDown
How can I do this?
Thanks,
Woody
|