Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
With help here, I've written the following macro to take filter
criteria from a selected cell on the source sheet and apply an advanced filter to a datasheet and put the filtered records on a separate sheet called "County Records". I've used the same macro, with sheet names and other things adjusted, on 2 previous source sheets (each source sheet has a corresponding datasheet). Sub ReunificationExtract() 'Password used Dim CtyCode As String Dim WkSht As Object Dim PWORD As String Dim CurRow As Integer Dim SourceSht As String Dim Cnt As Integer Dim CtyCell As Object PWORD = "dave" Application.ScreenUpdating = False HomeSht = ActiveSheet.Name CurRow = ActiveCell.Row CtyCode = ActiveSheet.Cells(CurRow, "B") Set CtyCell = Sheets("Reunification Records").Columns("c").Find(What:=CtyCode, LookIn:=xlValues) If Not CtyCell Is Nothing Then 'If Len(Trim(ActiveSheet.Cells(CurRow, "c").Text)) 0 Then Set WkSht = ActiveWorkbook.Sheets("Reunification Records") WkSht.Unprotect Password:=PWORD Sheets("Reunification Records").Range("aa2") = CtyCode WkSht.Protect Password:=PWORD Sheets("County Records").Select Worksheets("County Records").UsedRange.Clear Range("a1:e1").Merge Range("a1").FormulaR1C1 = _ "WARNING: This data will be erased the next time County Records are extracted. " With Range("a1").Characters(Start:=1, Length:=78).Font .FontStyle = "Bold" .ColorIndex = 3 End With Range("A2:e2").Merge Range("A2").FormulaR1C1 = _ "If you wish to save the data, copy and paste it to another spreadsheet or print it before doing another data extraction." With Range("A2").Characters(Start:=1, Length:=124).Font .ColorIndex = 3 Rows("2:2").RowHeight = 25 End With Range("a2").Select Selection.WrapText = True Sheets("Reunification Records").Range("A1:M192").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=Sheets("Reunification Records").Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False Range("A4:E4").Merge Range("a4") = CtyCode & " County Reunification Records" With Range("a4").Characters(Start:=1, Length:=78).Font .FontStyle = "Bold" .ColorIndex = 10 .Size = 16 End With Columns("A:M").EntireColumn.AutoFit Range("A5:M5").Select With Selection .VerticalAlignment = xlBottom .WrapText = True .Font.Bold = True End With Range("A5").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=False, Font _ :=True, Alignment:=False, Border:=True, Pattern:=True, Width:=False Rows("5:5").RowHeight = 25 Worksheets("Reunification Records").Range("aa5:aa25").Copy Destination:= _ Worksheets("County Records").Range("a5") _ .End(xlDown).Offset(2, 0) Worksheets("County Records").Range("a1").Select Sheets("County Records").Range("aa4").Value = HomeSht Else MsgBox "There are no Reunifications for " & CtyCode & " for SFY 2005 2nd Quarter", vbOKOnly End If Application.ScreenUpdating = True End Sub For some reason, on the third set of sheets, the macro works with any of the first 16 rows of the source sheet selected, but hangs up when any of the rows below 16 are selected (I'm not sure if this is relevant, just giving all my observations). I've done watches and stepped through the macro, both on data where it works, and on data (below row 16) where it doesn't work. For some reason, when it doesn't run, it doesn't find any filterable data in the source sheet, even when there's plenty of it there. I've run advanced filter using criteria copied from the source sheet and it filters just fine. Why does this macro run on some data in the source sheet, but not on other selections? Why does it run in 2 other sheets but not, in some cases, in this one? I'd appreciate any help. I know the code is crude, I'm very new at VBA. I'm sure it can be written much more efficiently, and would be interested in feedback on that as well. But I'm mostly concerned about why it's hanging up as is so I can learn from my mistakes. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
I found the error. It was in the datasheet, not the macro. The method
I'd used to define the filter range assumed no blank cells in the datasheet, which up until now, was true. Am redoing that part of the macro. Now I'm having problems with a line of code which I'm trying to use to define the correct filter range. WkSht.Range(.Range("a1"), .Range("a1").End(xlToRight).End(xlDown)) _ .Name = "FilterRange" It's hangin up at the second ".Range", right before ("a1"). WkSht has been dimmed as an object. The error message is "Compile Error: Invalid or unqualified reference". So if it's not an undeclared variable, what is it. Any ideas? Thanks for the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
Those .Range()'s mean that they refer to the previous With object.
Maybe... with wksht .Range(.Range("a1"), .Range("a1").End(xlToRight).End(xlDown)) _ .Name = "FilterRange" end with But if you have gaps, that .end(xltoright).end(xldown) may cause problems. Can you pick out a column and row that always has data? I'm gonna guess that row 1 is headers and always has data. And I'm gonna guess that column A always has data. Dim LastRow as long dim LastCol as long with wksht lastrow = .cells(.rows.count,"A").end(xlup).row lastcol = .cells(1,.columns.count).end(xltoleft).column .range("a1",.cells(lastrow,lastcol).name = "filterrange" end with davegb wrote: I found the error. It was in the datasheet, not the macro. The method I'd used to define the filter range assumed no blank cells in the datasheet, which up until now, was true. Am redoing that part of the macro. Now I'm having problems with a line of code which I'm trying to use to define the correct filter range. WkSht.Range(.Range("a1"), .Range("a1").End(xlToRight).End(xlDown)) _ .Name = "FilterRange" It's hangin up at the second ".Range", right before ("a1"). WkSht has been dimmed as an object. The error message is "Compile Error: Invalid or unqualified reference". So if it's not an undeclared variable, what is it. Any ideas? Thanks for the help. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
Thanks, Dave!
I've changed the order of the steps so it goes across the top first, which, as you said, has no blanks because of the column headers. And I've made sure that the right column has no blanks. Should take care of that problem. I've discovered in discussion with the person feeding me the data from SPSS that this latest data, and some of the future data, comes from an entirely different database than what she had given before, and has blanks and some serious formatting problems when downloaded into Excel. Blank cells, blank cells that aren't really blank, leading and trailing spaces, etc. Fortunately, I've gleaned some good programs here and elsewhere to help with most of this. As for your code, it won't run. First try, I got a compile error on filterrange. I dimmed it as a range, and now I get a run error on the line: ..range("a1",.cells(lastrow,las*tcol).name = "filterrange" I tried it with and without the quotes. I also turned it around, because it seemed to me that filterrange should be defined as the range from a1 to lastrow, lastcol, not the other way around. Is there a time when you define variables backwards, like here? I also tried changing it to xltoRight, then xlDown, because it seemed to me that from A1 I want to go to the right, then down to define the filterrange, not up and left. Is it just me, or does it seem like everything in this code is backwards? What am I missing? Any other ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
Oopsie. I left out a closing paren:
This: ..range("a1",.cells(lastrow,lastcol).name = "filterrange" should be: ..range("a1",.cells(lastrow,lastcol)).name = "filterrange" Sorry. davegb wrote: Thanks, Dave! I've changed the order of the steps so it goes across the top first, which, as you said, has no blanks because of the column headers. And I've made sure that the right column has no blanks. Should take care of that problem. I've discovered in discussion with the person feeding me the data from SPSS that this latest data, and some of the future data, comes from an entirely different database than what she had given before, and has blanks and some serious formatting problems when downloaded into Excel. Blank cells, blank cells that aren't really blank, leading and trailing spaces, etc. Fortunately, I've gleaned some good programs here and elsewhere to help with most of this. As for your code, it won't run. First try, I got a compile error on filterrange. I dimmed it as a range, and now I get a run error on the line: .range("a1",.cells(lastrow,las*tcol).name = "filterrange" I tried it with and without the quotes. I also turned it around, because it seemed to me that filterrange should be defined as the range from a1 to lastrow, lastcol, not the other way around. Is there a time when you define variables backwards, like here? I also tried changing it to xltoRight, then xlDown, because it seemed to me that from A1 I want to go to the right, then down to define the filterrange, not up and left. Is it just me, or does it seem like everything in this code is backwards? What am I missing? Any other ideas? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
Thanks, Dave!
The macro runs now, but is selecting data outside the table (other info located further right in the spreadsheet. Is there a way to only select the data in the table? Dave Peterson wrote: Oopsie. I left out a closing paren: This: .range("a1",.cells(lastrow,lastcol).name = "filterrange" should be: .range("a1",.cells(lastrow,lastcol)).name = "filterrange" Sorry. davegb wrote: Thanks, Dave! I've changed the order of the steps so it goes across the top first, which, as you said, has no blanks because of the column headers. And I've made sure that the right column has no blanks. Should take care of that problem. I've discovered in discussion with the person feeding me the data from SPSS that this latest data, and some of the future data, comes from an entirely different database than what she had given before, and has blanks and some serious formatting problems when downloaded into Excel. Blank cells, blank cells that aren't really blank, leading and trailing spaces, etc. Fortunately, I've gleaned some good programs here and elsewhere to help with most of this. As for your code, it won't run. First try, I got a compile error on filterrange. I dimmed it as a range, and now I get a run error on the line: .range("a1",.cells(lastrow,las*tcol).name = "filterrange" I tried it with and without the quotes. I also turned it around, because it seemed to me that filterrange should be defined as the range from a1 to lastrow, lastcol, not the other way around. Is there a time when you define variables backwards, like here? I also tried changing it to xltoRight, then xlDown, because it seemed to me that from A1 I want to go to the right, then down to define the filterrange, not up and left. Is it just me, or does it seem like everything in this code is backwards? What am I missing? Any other ideas? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
Maybe it's better to work down and to the right:
Option Explicit Sub test() Dim LastRow As Long Dim LastCol As Long Dim wksht As Worksheet Set wksht = ActiveSheet With wksht LastRow = .Range("a1").End(xlDown).Row LastCol = .Range("a1").End(xlToRight).Column .Range("a1", .Cells(LastRow, LastCol)).Name = "filterrange" End With End Sub If this didn't work, can you share the address of the table? And what's adjacent to it that should be avoided? For what it's worth, I've always tried to keep my tables on separate worksheets. If I mix tables (or even other stuff) on that same worksheet, it becomes just a little more difficult to delete or insert rows or columns. davegb wrote: Thanks, Dave! The macro runs now, but is selecting data outside the table (other info located further right in the spreadsheet. Is there a way to only select the data in the table? Dave Peterson wrote: Oopsie. I left out a closing paren: This: .range("a1",.cells(lastrow,lastcol).name = "filterrange" should be: .range("a1",.cells(lastrow,lastcol)).name = "filterrange" Sorry. davegb wrote: Thanks, Dave! I've changed the order of the steps so it goes across the top first, which, as you said, has no blanks because of the column headers. And I've made sure that the right column has no blanks. Should take care of that problem. I've discovered in discussion with the person feeding me the data from SPSS that this latest data, and some of the future data, comes from an entirely different database than what she had given before, and has blanks and some serious formatting problems when downloaded into Excel. Blank cells, blank cells that aren't really blank, leading and trailing spaces, etc. Fortunately, I've gleaned some good programs here and elsewhere to help with most of this. As for your code, it won't run. First try, I got a compile error on filterrange. I dimmed it as a range, and now I get a run error on the line: .range("a1",.cells(lastrow,las*tcol).name = "filterrange" I tried it with and without the quotes. I also turned it around, because it seemed to me that filterrange should be defined as the range from a1 to lastrow, lastcol, not the other way around. Is there a time when you define variables backwards, like here? I also tried changing it to xltoRight, then xlDown, because it seemed to me that from A1 I want to go to the right, then down to define the filterrange, not up and left. Is it just me, or does it seem like everything in this code is backwards? What am I missing? Any other ideas? -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
It has occurred to me that this might have been easier had I made 3
sheets for each category (Of course, I didn't know when I started it would work out this way. After I gave my end-users the original data, they asked for the back-up records that determined the first round data.) But it would make sense to create a sheet for user data, a datasheet for the records, and a macro sheet which would hold the information that the macros use to create the "Records" sheet based on the selection in the user sheet. After I get this next round of data out to them, I'll have some time, I hope, and restructure the whole thing to make it easier to manage. Dave Peterson wrote: Maybe it's better to work down and to the right: Option Explicit Sub test() Dim LastRow As Long Dim LastCol As Long Dim wksht As Worksheet Set wksht = ActiveSheet With wksht LastRow = .Range("a1").End(xlDown).Row LastCol = .Range("a1").End(xlToRight).Column .Range("a1", .Cells(LastRow, LastCol)).Name = "filterrange" End With End Sub If this didn't work, can you share the address of the table? And what's adjacent to it that should be avoided? For what it's worth, I've always tried to keep my tables on separate worksheets. If I mix tables (or even other stuff) on that same worksheet, it becomes just a little more difficult to delete or insert rows or columns. davegb wrote: Thanks, Dave! The macro runs now, but is selecting data outside the table (other info located further right in the spreadsheet. Is there a way to only select the data in the table? Dave Peterson wrote: Oopsie. I left out a closing paren: This: .range("a1",.cells(lastrow,lastcol).name = "filterrange" should be: .range("a1",.cells(lastrow,lastcol)).name = "filterrange" Sorry. davegb wrote: Thanks, Dave! I've changed the order of the steps so it goes across the top first, which, as you said, has no blanks because of the column headers. And I've made sure that the right column has no blanks. Should take care of that problem. I've discovered in discussion with the person feeding me the data from SPSS that this latest data, and some of the future data, comes from an entirely different database than what she had given before, and has blanks and some serious formatting problems when downloaded into Excel. Blank cells, blank cells that aren't really blank, leading and trailing spaces, etc. Fortunately, I've gleaned some good programs here and elsewhere to help with most of this. As for your code, it won't run. First try, I got a compile error on filterrange. I dimmed it as a range, and now I get a run error on the line: .range("a1",.cells(lastrow,las*tcol).name = "filterrange" I tried it with and without the quotes. I also turned it around, because it seemed to me that filterrange should be defined as the range from a1 to lastrow, lastcol, not the other way around. Is there a time when you define variables backwards, like here? I also tried changing it to xltoRight, then xlDown, because it seemed to me that from A1 I want to go to the right, then down to define the filterrange, not up and left. Is it just me, or does it seem like everything in this code is backwards? What am I missing? Any other ideas? -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
Hindsight is 20/20. Or you live and you learn.
Good luck. davegb wrote: It has occurred to me that this might have been easier had I made 3 sheets for each category (Of course, I didn't know when I started it would work out this way. After I gave my end-users the original data, they asked for the back-up records that determined the first round data.) But it would make sense to create a sheet for user data, a datasheet for the records, and a macro sheet which would hold the information that the macros use to create the "Records" sheet based on the selection in the user sheet. After I get this next round of data out to them, I'll have some time, I hope, and restructure the whole thing to make it easier to manage. Dave Peterson wrote: Maybe it's better to work down and to the right: Option Explicit Sub test() Dim LastRow As Long Dim LastCol As Long Dim wksht As Worksheet Set wksht = ActiveSheet With wksht LastRow = .Range("a1").End(xlDown).Row LastCol = .Range("a1").End(xlToRight).Column .Range("a1", .Cells(LastRow, LastCol)).Name = "filterrange" End With End Sub If this didn't work, can you share the address of the table? And what's adjacent to it that should be avoided? For what it's worth, I've always tried to keep my tables on separate worksheets. If I mix tables (or even other stuff) on that same worksheet, it becomes just a little more difficult to delete or insert rows or columns. davegb wrote: Thanks, Dave! The macro runs now, but is selecting data outside the table (other info located further right in the spreadsheet. Is there a way to only select the data in the table? Dave Peterson wrote: Oopsie. I left out a closing paren: This: .range("a1",.cells(lastrow,lastcol).name = "filterrange" should be: .range("a1",.cells(lastrow,lastcol)).name = "filterrange" Sorry. davegb wrote: Thanks, Dave! I've changed the order of the steps so it goes across the top first, which, as you said, has no blanks because of the column headers. And I've made sure that the right column has no blanks. Should take care of that problem. I've discovered in discussion with the person feeding me the data from SPSS that this latest data, and some of the future data, comes from an entirely different database than what she had given before, and has blanks and some serious formatting problems when downloaded into Excel. Blank cells, blank cells that aren't really blank, leading and trailing spaces, etc. Fortunately, I've gleaned some good programs here and elsewhere to help with most of this. As for your code, it won't run. First try, I got a compile error on filterrange. I dimmed it as a range, and now I get a run error on the line: .range("a1",.cells(lastrow,las*tcol).name = "filterrange" I tried it with and without the quotes. I also turned it around, because it seemed to me that filterrange should be defined as the range from a1 to lastrow, lastcol, not the other way around. Is there a time when you define variables backwards, like here? I also tried changing it to xltoRight, then xlDown, because it seemed to me that from A1 I want to go to the right, then down to define the filterrange, not up and left. Is it just me, or does it seem like everything in this code is backwards? What am I missing? Any other ideas? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange macro behavior!
Ain't it the truth!
Dave Peterson wrote: Hindsight is 20/20. Or you live and you learn. Good luck. davegb wrote: It has occurred to me that this might have been easier had I made 3 sheets for each category (Of course, I didn't know when I started it would work out this way. After I gave my end-users the original data, they asked for the back-up records that determined the first round data.) But it would make sense to create a sheet for user data, a datasheet for the records, and a macro sheet which would hold the information that the macros use to create the "Records" sheet based on the selection in the user sheet. After I get this next round of data out to them, I'll have some time, I hope, and restructure the whole thing to make it easier to manage. Dave Peterson wrote: Maybe it's better to work down and to the right: Option Explicit Sub test() Dim LastRow As Long Dim LastCol As Long Dim wksht As Worksheet Set wksht = ActiveSheet With wksht LastRow = .Range("a1").End(xlDown).Row LastCol = .Range("a1").End(xlToRight).Column .Range("a1", .Cells(LastRow, LastCol)).Name = "filterrange" End With End Sub If this didn't work, can you share the address of the table? And what's adjacent to it that should be avoided? For what it's worth, I've always tried to keep my tables on separate worksheets. If I mix tables (or even other stuff) on that same worksheet, it becomes just a little more difficult to delete or insert rows or columns. davegb wrote: Thanks, Dave! The macro runs now, but is selecting data outside the table (other info located further right in the spreadsheet. Is there a way to only select the data in the table? Dave Peterson wrote: Oopsie. I left out a closing paren: This: .range("a1",.cells(lastrow,lastcol).name = "filterrange" should be: .range("a1",.cells(lastrow,lastcol)).name = "filterrange" Sorry. davegb wrote: Thanks, Dave! I've changed the order of the steps so it goes across the top first, which, as you said, has no blanks because of the column headers. And I've made sure that the right column has no blanks. Should take care of that problem. I've discovered in discussion with the person feeding me the data from SPSS that this latest data, and some of the future data, comes from an entirely different database than what she had given before, and has blanks and some serious formatting problems when downloaded into Excel. Blank cells, blank cells that aren't really blank, leading and trailing spaces, etc. Fortunately, I've gleaned some good programs here and elsewhere to help with most of this. As for your code, it won't run. First try, I got a compile error on filterrange. I dimmed it as a range, and now I get a run error on the line: .range("a1",.cells(lastrow,las*tcol).name = "filterrange" I tried it with and without the quotes. I also turned it around, because it seemed to me that filterrange should be defined as the range from a1 to lastrow, lastcol, not the other way around. Is there a time when you define variables backwards, like here? I also tried changing it to xltoRight, then xlDown, because it seemed to me that from A1 I want to go to the right, then down to define the filterrange, not up and left. Is it just me, or does it seem like everything in this code is backwards? What am I missing? Any other ideas? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange behavior | Excel Discussion (Misc queries) | |||
Excel Mac OS X - Strange Behavior | Excel Discussion (Misc queries) | |||
Strange behavior | Setting up and Configuration of Excel | |||
Strange behavior. | Excel Discussion (Misc queries) | |||
Strange Personal Macro Workbook Behavior | Excel Discussion (Misc queries) |