Hello everyone,
I am a newbie that started teaching myself VBA YESTERDAY so that I can make my life using Excel considerably easier at my job (I like clicking one button and having my spreadsheet give me exactly what I need to report). Here is my issue:
I am exporting the responses from a Google form into Excel and then rearranging the data to send out as a report. The form works in such a way that it will take you down 1 of 2 pathways of questioning, depending on which medication is selected in one of the questions. I recorded a macro for one spreadsheet manipulation (for one group of medications), copied and pasted, removed the extraneous bits (active window scrolls, etc.) and am now finding that I have to do a little extra to this second sheet (for the other medication) to get it to only show data for ONE of the 2 possible options. I am doing all edits manually in VBA.
I've done some research on forums and found a bit of Autofilter code that I popped into the beginning of my macro (which I will paste below, sorry it's kind of long). I also had to delete some information due to confidentiality (items italicized).
I keep getting an error message that says "Runtime error:'1004': Autofilter method of Range class failed". I am just curious as to what I may be missing. I am completely aware, that even the autofilter method may be wrong, but as a said, I'm a newb.
What I am trying to do is filter column L (drug name) in my spreadsheet, and then delete all rows that do not contain the name of one certain drug in column L. Then, the rest of the macro works on the remaining data.
Any help would be greatly appreciated as I will have to generate these reports on a regular basis as the data continues to accumulate.
I am using MS Excel 2010 (Office Professional) on a Windows 7 OS.
Thank you in advance!
Macro is below:
Sub ScreeningLog_study name()
'
' ScreeningLog_study name Macro
'
'
Dim DelRows As Range
ActiveWorkbook.Worksheets("Form Responses 1").Range("L2:L100").AutoFilter Criteria1:="<>drug name as in table", VisibleDropDown:=False
Set DelRows = Range("_FilterDatabase").SpecialCells(xlCellTypeVisible)
ActiveWorkbook.Worksheets("Form Responses 1").AutoFilterMode = False
DelRows.EntireRow.Delete
Set DelRows = Nothing
ActiveWorkbook.Worksheets("Form Responses 1").Sort.SortFields.Add Key:=Range( _
"C2:C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Form Responses 1").Sort
.SetRange Range("A1:AH7")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A:A,E:E,F:F,G:G,H:H,L:L,M:M,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG"). _
Select
Range("AG1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.RowHeight = 76.5
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").ColumnWidth = 14.86
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 9
Columns("D:D").ColumnWidth = 10.29
Columns("E:E").ColumnWidth = 17.14
Columns("F:F").ColumnWidth = 12.29
Columns("G:G").ColumnWidth = 16.71
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").ColumnWidth = 42.86
Columns("J:J").ColumnWidth = 38.86
Columns("I:J").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:K").EntireColumn.AutoFit
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "SUBJECT SCREENING LOG - study name"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "Sponsor:"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Protocol:"
Range("B2").Select
ActiveCell.FormulaR1C1 = "sponsor name"
Range("B3").Select
ActiveCell.FormulaR1C1 = "number"
Range("A2:A3").Select
Selection.Font.Bold = True
Range("J2").Select
ActiveCell.FormulaR1C1 = "number"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Investigator name: name"
Range("B2:B3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J2:J3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J2").Select
ActiveCell.FormulaR1C1 = "Site ID: number"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("J3").Select
ActiveCell.FormulaR1C1 = "Investigator name: name"
With ActiveCell.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=18, Length:=21).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
I am a newbie that started teaching myself VBA YESTERDAY so that I can make my life using Excel considerably easier at my job (I like clicking one button and having my spreadsheet give me exactly what I need to report). Here is my issue:
I am exporting the responses from a Google form into Excel and then rearranging the data to send out as a report. The form works in such a way that it will take you down 1 of 2 pathways of questioning, depending on which medication is selected in one of the questions. I recorded a macro for one spreadsheet manipulation (for one group of medications), copied and pasted, removed the extraneous bits (active window scrolls, etc.) and am now finding that I have to do a little extra to this second sheet (for the other medication) to get it to only show data for ONE of the 2 possible options. I am doing all edits manually in VBA.
I've done some research on forums and found a bit of Autofilter code that I popped into the beginning of my macro (which I will paste below, sorry it's kind of long). I also had to delete some information due to confidentiality (items italicized).
I keep getting an error message that says "Runtime error:'1004': Autofilter method of Range class failed". I am just curious as to what I may be missing. I am completely aware, that even the autofilter method may be wrong, but as a said, I'm a newb.
What I am trying to do is filter column L (drug name) in my spreadsheet, and then delete all rows that do not contain the name of one certain drug in column L. Then, the rest of the macro works on the remaining data.
Any help would be greatly appreciated as I will have to generate these reports on a regular basis as the data continues to accumulate.
I am using MS Excel 2010 (Office Professional) on a Windows 7 OS.
Thank you in advance!
Macro is below:
Sub ScreeningLog_study name()
'
' ScreeningLog_study name Macro
'
'
Dim DelRows As Range
ActiveWorkbook.Worksheets("Form Responses 1").Range("L2:L100").AutoFilter Criteria1:="<>drug name as in table", VisibleDropDown:=False
Set DelRows = Range("_FilterDatabase").SpecialCells(xlCellTypeVisible)
ActiveWorkbook.Worksheets("Form Responses 1").AutoFilterMode = False
DelRows.EntireRow.Delete
Set DelRows = Nothing
ActiveWorkbook.Worksheets("Form Responses 1").Sort.SortFields.Add Key:=Range( _
"C2:C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Form Responses 1").Sort
.SetRange Range("A1:AH7")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A:A,E:E,F:F,G:G,H:H,L:L,M:M,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG"). _
Select
Range("AG1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.RowHeight = 76.5
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").ColumnWidth = 14.86
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 9
Columns("D:D").ColumnWidth = 10.29
Columns("E:E").ColumnWidth = 17.14
Columns("F:F").ColumnWidth = 12.29
Columns("G:G").ColumnWidth = 16.71
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").ColumnWidth = 42.86
Columns("J:J").ColumnWidth = 38.86
Columns("I:J").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:K").EntireColumn.AutoFit
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "SUBJECT SCREENING LOG - study name"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "Sponsor:"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Protocol:"
Range("B2").Select
ActiveCell.FormulaR1C1 = "sponsor name"
Range("B3").Select
ActiveCell.FormulaR1C1 = "number"
Range("A2:A3").Select
Selection.Font.Bold = True
Range("J2").Select
ActiveCell.FormulaR1C1 = "number"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Investigator name: name"
Range("B2:B3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J2:J3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J2").Select
ActiveCell.FormulaR1C1 = "Site ID: number"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("J3").Select
ActiveCell.FormulaR1C1 = "Investigator name: name"
With ActiveCell.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=18, Length:=21).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub