0

While executing the below code, it is working for one excel work book but when trying to do the same with another work book it is throwing run time error while finding the first blank column

I tried changing the code by applying different values but it is working for only one excel work book But as per my knowledge I haven't wrote anything static related to one work book in my code

unusedcolumn = Rows(1).SpecialCells(xlCellTypeBlanks)(1).Column

'Open a excel file
'Find last cell number of row A from data sheet
'Find the first blank column
'Add rule run date to first blank column
'Find age and convert to years
'sort by small to high by age column

Sub age()
Dim OpenWb As Workbook
With Application.FileDialog(msoFileDialogFilePicker)
    'Makes sure the user can select only one file
    .AllowMultiSelect = False
    'Filter to just the following types of files to narrow down selection options
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    'Show the dialog box
    .Show

    'Store in fullpath variable
    fullpath = .SelectedItems.Item(1)

    'Actions in raw data sheet
    Set OpenWb = Workbooks.Open(fullpath)
    Dim wsData As Worksheet
    Set wsData = OpenWb.Worksheets("Sheet1")

    ' Find last cell number of row A from data sheet
    Dim last As Double
    Dim Cell As Range
    With OpenWb.Worksheets("Sheet1")
        last = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    OpenWb.Worksheets("Sheet1").Range("A1").Select
    OpenWb.Worksheets("Sheet1").ListObjects.Add(xlSrcRange, Selection.CurrentRegion, , xlYes).Name = _
    "MyTable"

    'Find the first blank column
    Dim unusedcolumn As Range
    unusedcolumn = Rows(1).SpecialCells(xlCellTypeBlanks)(1).Column
    Dim irow As String
    irow = Worksheets("Sheet1").Cells(Rows(unusedcolumn).Count, 1).End(xlUp).Row

    'Add rule run date to first blank column
    Cells(irow, unusedcolumn) = "Rule run date"
    OpenWb.Worksheets("Sheet1").Select
    Dim i As Double
    For i = 1 To last - 1
        Cells(irow + i, unusedcolumn).value = "11/15/2019"
    Next i

    'Find age
    OpenWb.Worksheets("Sheet1").Rows("1:1").Select
    Dim unusedcolumn2 As Integer
    unusedcolumn2 = Rows(1).SpecialCells(xlCellTypeBlanks)(1).Column
    Dim irow2 As String
    irow2 = Worksheets("Sheet1").Cells(Rows(unusedcolumn).Count, 1).End(xlUp).Row
    Cells(irow2, unusedcolumn2) = "Age"
    OpenWb.Worksheets("Sheet1").Select
    Dim J As Double
    Cells(irow2 + 1, unusedcolumn2).Formula = "=MyTable[[#All],[Rule run date]]-MyTable[[#All],[BIRTH_YEAR]]"

    'Covert age to years
    Dim unusedcolumn3 As Integer
    unusedcolumn3 = Rows(1).SpecialCells(xlCellTypeBlanks)(1).Column
    Dim irow3 As String
    irow3 = Worksheets("Sheet1").Cells(Rows(unusedcolumn).Count, 1).End(xlUp).Row
    Cells(irow3, unusedcolumn3) = "Age in years"
    OpenWb.Worksheets("Sheet1").Select
    Dim k As Double
    Cells(irow3 + 1, unusedcolumn3).Formula = "=DATEDIF(0, MyTable[[#All],[Age]], ""y"") & ""years"" & DATEDIF(0, MyTable[[#All],[Age]], ""ym"") & ""months"" & DATEDIF(0, MyTable[[#All],[Age]], ""md"") & "" days"""
    Worksheets("Sheet1").UsedRange.value = Worksheets("Sheet1").UsedRange.value
    OpenWb.Worksheets("Sheet1").Rows("1:1").Select
    Dim f1 As String
    f1 = Selection.Find(What:="Age", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:=Cells( _
   f1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
srawan kallakuri
  • 71
  • 1
  • 9
  • 2
    You should qualify the range: `unusedcolumn = wsData.Rows(1).SpecialCells(xlCellTypeBlanks)(1).Column` – riskypenguin Oct 25 '19 at 09:27
  • 2
    A few other notes: 1. You don't need to wrap the whole code inside the `With Application.FileDialog(msoFileDialogFilePicker)` block, you should place the `End With` after `fullpath = .SelectedItems.Item(1)`. 2. You should [Avoid Using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) 3. Instead of always referencing `OpenWb.Worksheets("Sheet1")`, you can simply reference `wsData`. 4. `Set wsData = OpenWb.Worksheets("Sheet1")` will fail if there is no sheet called `Sheet1`, referencing sheets by name should be avoided as they can be changed by users. – riskypenguin Oct 25 '19 at 09:30
  • 2
    5. `irow = Worksheets("Sheet1").Cells(Rows(unusedcolumn).Count, 1).End(xlUp).Row` seems weird, the `(unusedcolumn)` doesn't achieve anything here as far as I know, it might even lead to unwanted results. You probably want `irow = Worksheets("Sheet1").Cells(Rows.Count, unusedcolumn).End(xlUp).Row` 6. `irow` (and `irow2`, etc.) should be `Dim`d as `Long` instead of `String` since they represent row numbers 7. `f1` should be `Long` instead of `String` as well 8. `Worksheets("Sheet1").UsedRange.value = Worksheets("Sheet1").UsedRange.value` doesn't do anything – riskypenguin Oct 25 '19 at 09:33
  • Thanks for the help.. But still seeing the same issue even after replacing the line with wsData.Rows(1).SpecialCells(xlCellTypeBlanks)(1).Column – srawan kallakuri Oct 25 '19 at 09:39
  • 2
    Try storing `wsData.Rows(1).SpecialCells(xlCellTypeBlanks)` in a `Range` Object, and test if it `Is Nothing`? – Chronocidal Oct 25 '19 at 09:56
  • 2
    Also - you `Dim unusedcolumn As Range`, and then try to set the value of it to `Range.Column` - which is [a read-only `Long`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.column), while it is still `Nothing` - did you mean either [`Range.Columns`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.columns) or [`Range.EntireColumn`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.entirecolumn), and `Set usedcolumn = Rows(1).SpecialCells(Etcetera)` instead? – Chronocidal Oct 25 '19 at 10:04

0 Answers0