|
Using a values from a lookup table to populate a range
Hi-
I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
Can you give examples of the data in both workbooks. I'm not sure from yor
example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
Sure.
The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
Jill and I have been working on this to no avail. Please help!
"VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
I have a few questions
It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
Good questions. I cannot use standard zipcodes, as some of my ziphigh and
ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
try this code. You need to change the two SET stements on the top of the
code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
Hi Joel,
Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
I tested the code and it work. I just saw that I left the two periods off of
the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
Hi Joel-
This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 FL 35501 35999 CA 47701 47999 CA 48222 48999 MI 60001 60999 Thank you so much, Jill |
Using a values from a lookup table to populate a range
See Answers below
"VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: Hi- I really need some input on this one. I have a lookup table that identifies valid zipcodes for states/provinces. What I need is to create a macro that helps poplulate a number of worksheets with the valid zipcodes for the identified state. For instance: My Lookup Table (in a separate file) has the valid ZipLow and ZipHighs for each state (see sample below). I have a macro that lookups the state and inserts the number of rows necessary for each state (as they vary per state) which gives me a good table to work with. Then I would like to populate the ZipHigh and ZipLow columns based on the State column value until the table is filled with appropriate zips for each state in my table. I would imagine the easiest way to do this would be to see the value in the state column, then review the lookup table to copy in the range that is equal to the state value, then repeat for each new state found. I am confused about how to copy in the dynamic range from the lookup table, which would vary depending on the state. Sample lookup table: State ZipLow ZipHigh FL 33301 33999 FL 34401 34999 |
Using a values from a lookup table to populate a range
All is good! And I really mean that! You have made our day! One more
request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: A B C D E CA 1a NameA 47701 47999 CA 2a NameA 48222 48999 CA 1b NameB 47701 47999 CA 2b NameB 48222 48999 CA 1c NameC 47701 47999 CA 2c NameC 48222 48999 CA 1d NameD 47701 47999 CA 2d NameD 48222 48999 I hope that is more clear. Thanks for your consideration! "Joel" wrote: Can you give examples of the data in both workbooks. I'm not sure from yor example if the table you provided is the source or the destination data. "Jill Smith" wrote: |
Using a values from a lookup table to populate a range
Youo have to change the first two set statements
Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "VickiV" wrote: Sure. The Source is the sample provided originally. It is a table of all states and their acceptable zips, plus some other columns of data I do not need to bring into the destination worksheet. It has a named range of Territory that identifies the table range. Column A lists the State field, Columns B & C have the Zips field. Their column headings are identical to the corresponding column headings in the destination field (the data is just in a different columnar order). The destination will only have data for one State at a time. I have a worksheet for each state, and I want to filter the Source for the matching state of the desitination worksheet. For instance, FL has 3 valid rows of data in Source. I want to copy the FL data for columns B & C to the corresponding columns in the destination (D & E). Then last but not least, in the destination worksheet, copy this range of values down to the end of the destination table. So for FL destination worksheet, it would look like this (FL has 3 valid rows of zips): For instance, FL destination will look like this (simplified...alot of other columns in reality): Columns: A B C D E FL 1a NameA 33301 33999 FL 2a NameA 34401 34999 FL 3a NameA 35501 39999 FL 1b NameB 33301 33999 FL 2b NameB 34401 34999 FL 3b NameB 35501 39999 FL 1c NameC 33301 33999 FL 2c NameC 34401 34999 FL 3c NameC 35501 39999 The other states would have the same repeating pattern for the range of valid zips for them. For instance: CA has only 2 valid zips. It would copy the 2 valid rows/columns of zip data to the appropriate column (D & E in destination example), then repeat the patten down to the end of the table. For instance, CA destination will look like (simplified...alot of other columns in reality): Columns: |
Using a values from a lookup table to populate a range
Hi Joel-
This code brings in the correct range of rows for a state, but it does not copy down the values in the columns to the end of the table. Any ideas? Regards, Pia "Joel" wrote: Youo have to change the first two set statements Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub |
Using a values from a lookup table to populate a range
I missed another period
LastRow = Range("A" & Rows.Count).End(xlUp).Row If adding the period to the line above doesn't work. The add a message box like below to help determine the problem. I'm using column A to determine the last row of data. LastRow = .Range("A" & Rows.Count).End(xlUp).Row MsgBox ("LastRow of data is : " & LastRow) "VickiV" wrote: Hi Joel- This code brings in the correct range of rows for a state, but it does not copy down the values in the columns to the end of the table. Any ideas? Regards, Pia "Joel" wrote: Youo have to change the first two set statements Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Form(0).Submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") |
Using a values from a lookup table to populate a range
Hi Joel,
We had corrected the typo for .Range. We have added the message box. The last row indicated is correct....it is the last row for Column A as expected (which is row 83) on this sample xls. Is a loop required? Thank you very much for your guidance on this. We are learning alot. Vicki "Joel" wrote: I missed another period LastRow = Range("A" & Rows.Count).End(xlUp).Row If adding the period to the line above doesn't work. The add a message box like below to help determine the problem. I'm using column A to determine the last row of data. LastRow = .Range("A" & Rows.Count).End(xlUp).Row MsgBox ("LastRow of data is : " & LastRow) "VickiV" wrote: Hi Joel- This code brings in the correct range of rows for a state, but it does not copy down the values in the columns to the end of the table. Any ideas? Regards, Pia "Joel" wrote: Youo have to change the first two set statements Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents |
Using a values from a lookup table to populate a range
I think I understand your question. You are asking if a loop is required to
find the LastRow. The answer is no unlsess there is data in column A after the last state data. Let me explain how "END: works. LastRow = .Range("A" & Rows.Count).End(xlUp).Row Rows.Count is the last row on the worksheet which is 65536 in Excel 2003. Excel goes to Column A [.Range("A65536")] and search up [END(xlup)] towards row 1 looking for the 1st cell that contains data. "VickiV" wrote: Hi Joel, We had corrected the typo for .Range. We have added the message box. The last row indicated is correct....it is the last row for Column A as expected (which is row 83) on this sample xls. Is a loop required? Thank you very much for your guidance on this. We are learning alot. Vicki "Joel" wrote: I missed another period LastRow = Range("A" & Rows.Count).End(xlUp).Row If adding the period to the line above doesn't work. The add a message box like below to help determine the problem. I'm using column A to determine the last row of data. LastRow = .Range("A" & Rows.Count).End(xlUp).Row MsgBox ("LastRow of data is : " & LastRow) "VickiV" wrote: Hi Joel- This code brings in the correct range of rows for a state, but it does not copy down the values in the columns to the end of the table. Any ideas? Regards, Pia "Joel" wrote: Youo have to change the first two set statements Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: I have a few questions It look like you are sortine the destinations by name and number (1a, 2a, 3a...) where is the name a number coming from? There si a website to get zipcodes. I wrote a macro last year for somebody to get the names of the city from the zip code using this website. I can do something similar if it helps. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True |
Using a values from a lookup table to populate a range
Hi Joel,
Can you also explain how this stmt works: CopyRange.Copy Destination:=.Range("C" & StartRow & ":C" & LastRow) And this one: If (NewRows Mod NumRows) = 0 Then Greatly appreciated, Vicki "Joel" wrote: I think I understand your question. You are asking if a loop is required to find the LastRow. The answer is no unlsess there is data in column A after the last state data. Let me explain how "END: works. LastRow = .Range("A" & Rows.Count).End(xlUp).Row Rows.Count is the last row on the worksheet which is 65536 in Excel 2003. Excel goes to Column A [.Range("A65536")] and search up [END(xlup)] towards row 1 looking for the 1st cell that contains data. "VickiV" wrote: Hi Joel, We had corrected the typo for .Range. We have added the message box. The last row indicated is correct....it is the last row for Column A as expected (which is row 83) on this sample xls. Is a loop required? Thank you very much for your guidance on this. We are learning alot. Vicki "Joel" wrote: I missed another period LastRow = Range("A" & Rows.Count).End(xlUp).Row If adding the period to the line above doesn't work. The add a message box like below to help determine the problem. I'm using column A to determine the last row of data. LastRow = .Range("A" & Rows.Count).End(xlUp).Row MsgBox ("LastRow of data is : " & LastRow) "VickiV" wrote: Hi Joel- This code brings in the correct range of rows for a state, but it does not copy down the values in the columns to the end of the table. Any ideas? Regards, Pia "Joel" wrote: Youo have to change the first two set statements Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 FL Paul Acct3 FL Paul Acct3 etc..... Now I am trying to autopopulate the ZipHigh/ZipLow/ZipDefintion values for FL throughout the worksheet using the zip source file from original posting. I thought this would be the easiest way to build the final StateAccount file...please share your thoughts. "Joel" wrote: |
Using a values from a lookup table to populate a range
1) Excell combines number and string to gether automatically in this statement CopyRange.Copy Destination:=.Range("C" & StartRow & ":C" & LastRow) So if StartRow = 5 and LastRow = 10 CopyRange.Copy Destination:=.Range("C5:C10") CopyRange was defined earlier in the code with a SET statement. This line is just copying the RANGE defined earlier in the code to column C. 2) The line is modular arithmetic If (NewRows Mod NumRows) = 0 Then if you have the following 0 Mod 3 = 0 1 Mod 3 = 1 2 Mod 3 = 2 3 Mod 3 = 0 4 Mod 3 = 1 5 Mod 3 = 2 6 Mod 3 = 0 7 Mod 3 = 1 It is the remainder after you divide 7/3 "VickiV" wrote: Hi Joel, Can you also explain how this stmt works: CopyRange.Copy Destination:=.Range("C" & StartRow & ":C" & LastRow) And this one: If (NewRows Mod NumRows) = 0 Then Greatly appreciated, Vicki "Joel" wrote: I think I understand your question. You are asking if a loop is required to find the LastRow. The answer is no unlsess there is data in column A after the last state data. Let me explain how "END: works. LastRow = .Range("A" & Rows.Count).End(xlUp).Row Rows.Count is the last row on the worksheet which is 65536 in Excel 2003. Excel goes to Column A [.Range("A65536")] and search up [END(xlup)] towards row 1 looking for the 1st cell that contains data. "VickiV" wrote: Hi Joel, We had corrected the typo for .Range. We have added the message box. The last row indicated is correct....it is the last row for Column A as expected (which is row 83) on this sample xls. Is a loop required? Thank you very much for your guidance on this. We are learning alot. Vicki "Joel" wrote: I missed another period LastRow = Range("A" & Rows.Count).End(xlUp).Row If adding the period to the line above doesn't work. The add a message box like below to help determine the problem. I'm using column A to determine the last row of data. LastRow = .Range("A" & Rows.Count).End(xlUp).Row MsgBox ("LastRow of data is : " & LastRow) "VickiV" wrote: Hi Joel- This code brings in the correct range of rows for a state, but it does not copy down the values in the columns to the end of the table. Any ideas? Regards, Pia "Joel" wrote: Youo have to change the first two set statements Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also bringing in a Zipdefinition value from another column as well....so they are all custom values. The name and number fields come from a another source file called StateAccounts. This StateAccounts source file lists the State (below example is a StateAccounts file for FL), and unique Name and Account Number on each row (see example): State Name AccountNumber FL Jill Acct1 FL Vicki Acct2 FL Paul Acct3 FL Vicki Acc4 FL Sam Acct5 FL Sam Acc6 I have to blow out the Name and Account Number field for each row of valid zip values in the Zip Source file for FL. So in the end this StateAccount source file which started with 6 rows, blows out to 18 rows (since FL has 3 rows of valid zip values) I created a macro that inserts the number of rows for each Name/Account combination needed to insert the 3 valid zip rows for FL. So my incomplete StateAccount file now looks like: State Name AccountNumber ZipLow ZipHigh ZipDefinition FL Jill Acct1 FL Jill Acct1 FL Jill Acct1 FL Vicki Acct2 FL Vicki Acct2 FL Vicki Acct2 FL Paul Acct3 |
Using a values from a lookup table to populate a range
Hi Joel,
The code is very close to working the way we need. We still cannot get it to copy the range down the column. We noticed the following differences when we change the code: It copies down to end of table but does not give us the correct result: When using this statement, only the first row populates for column B to D and this first row copies all the way down to the last row of the table : Set CopyRange = .Range("B" & c.Row & ":D" & c.Row) The original statement only populates the first instance correctly with the Range of values (representing 3 rows of data in Columns B to D), but it does not copy down to end of table. The original stmt is below. Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) Does this help identify what is causing it to not copy the range to end of table? Any ideas how to get the entire range (B2 to D4) to copy down the table vs. just the first row It almost seems like the following statement needs to shift down and do a loop till end of table is found. CopyRange.Copy _ Destination:=.Range("C" & StartRow & ":C" & LastRow) Thanks, Vicki "Joel" wrote: 1) Excell combines number and string to gether automatically in this statement CopyRange.Copy Destination:=.Range("C" & StartRow & ":C" & LastRow) So if StartRow = 5 and LastRow = 10 CopyRange.Copy Destination:=.Range("C5:C10") CopyRange was defined earlier in the code with a SET statement. This line is just copying the RANGE defined earlier in the code to column C. 2) The line is modular arithmetic If (NewRows Mod NumRows) = 0 Then if you have the following 0 Mod 3 = 0 1 Mod 3 = 1 2 Mod 3 = 2 3 Mod 3 = 0 4 Mod 3 = 1 5 Mod 3 = 2 6 Mod 3 = 0 7 Mod 3 = 1 It is the remainder after you divide 7/3 "VickiV" wrote: Hi Joel, Can you also explain how this stmt works: CopyRange.Copy Destination:=.Range("C" & StartRow & ":C" & LastRow) And this one: If (NewRows Mod NumRows) = 0 Then Greatly appreciated, Vicki "Joel" wrote: I think I understand your question. You are asking if a loop is required to find the LastRow. The answer is no unlsess there is data in column A after the last state data. Let me explain how "END: works. LastRow = .Range("A" & Rows.Count).End(xlUp).Row Rows.Count is the last row on the worksheet which is 65536 in Excel 2003. Excel goes to Column A [.Range("A65536")] and search up [END(xlup)] towards row 1 looking for the 1st cell that contains data. "VickiV" wrote: Hi Joel, We had corrected the typo for .Range. We have added the message box. The last row indicated is correct....it is the last row for Column A as expected (which is row 83) on this sample xls. Is a loop required? Thank you very much for your guidance on this. We are learning alot. Vicki "Joel" wrote: I missed another period LastRow = Range("A" & Rows.Count).End(xlUp).Row If adding the period to the line above doesn't work. The add a message box like below to help determine the problem. I'm using column A to determine the last row of data. LastRow = .Range("A" & Rows.Count).End(xlUp).Row MsgBox ("LastRow of data is : " & LastRow) "VickiV" wrote: Hi Joel- This code brings in the correct range of rows for a state, but it does not copy down the values in the columns to the end of the table. Any ideas? Regards, Pia "Joel" wrote: Youo have to change the first two set statements Sub GetZipcodes() Set LookupSht = ThisWorkbook.Sheets("Sheet1") Set ResultSht = ActiveSheet.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) End If End With With ResultSht StartRow = 2 LastRow = Range("A" & Rows.Count).End(xlUp).Row NewRows = LastRow - StartRow + 1 'Test if NewRow is a multiple of NumRows If (NewRows Mod NumRows) = 0 Then MsgBox ("Number of rows in Destination Sheet" & _ "isn't a multiple of the Number of rows for state") Else CopyRange.Copy _ Destination:=.Range("D" & StartRow & ":D" & LastRow) End If End With End Sub "VickiV" wrote: All is good! And I really mean that! You have made our day! One more request, and if it is too much we can put a new post. We reviewed another post of yours to copy a range of values and we would like to do the same. The code you provided in the prior post was: Range("B2:C5").Copy For RowCount = 6 to 200 step 4 Range("B" & RowCount).Paste Next RowCount However, we would like to modify the code to copy the dynamic/variable range created by the Sub GetZipCodes all the way down to the last record, thereby filling in the data down the columns D, E, and now F. Is this possible? Best Regards, Vicki "Joel" wrote: See Answers below "VickiV" wrote: Hi Joel- This is very cool! Thank you! I am able to bring in the range of values I need. You have gotten us so far on this worksheet build! Thank you! Can you tell me how I can add the following flexibility to this code: 1) Run the code against the active destination worksheet, vs. the defining the destination worksheet by name (the worksheet names change) Answer: from: Set ResultSht = Sheets("Sheet2") to: Set ResultSht = ActiveSheet 2) Bring in 3 columns of info B, C, D to destionation D, E, & F columns, rather just 2 columns. Answer: from Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) to Set CopyRange = .Range("B" & c.Row & ":D" & LastRow) 3) I added the periods in front of .Ranges below as you indicated, even for the line above the one you specified which also was missing a period. However I still get that compile error on that specific IF,Then,Else block so I commented it out and all works just fine. However, I would like to use that If,Then,Else block because it does catch errors so any more insight to that error message would be great. I am using Excel 2007: The compile error is below. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Answer: The line was too long and wrapped the double quote and closing parethesis should be on the same line as the MsgBox "Joel" wrote: I tested the code and it work. I just saw that I left the two periods off of the following line If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then I must of had the Result worksheet selected when I ran the code and didn't have the problem. I suspect the rowcount went to the last row of the worksheet (65536) and got an error while reading this row. Add the two periods and it should work "VickiV" wrote: Hi Joel, Thank you very much. I have modified the set statements to match my worksheet names. The Zip Def column is column D of the Zipcode source file (next to the ZipLow (Column B) and Ziphigh (Column C) columns. In running the code, I am getting a VB Compile error: "Can't assign to read only property" on the following line: Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't Match ") Any ideas? Thank you, Vicki "Joel" wrote: try this code. You need to change the two SET stements on the top of the code to match your workbook and worksheet. I also don't know where the Zip Definition is coming from. Sub GetZipcodes() Set LookupSht = Thisworkbook.Sheets("Sheet1") Set ResultSht = Thisworkbook.Sheets("Sheet2") State = ResultSht.Range("A2") 'Find State on LookupSht With LookupSht Set c = .Columns("A").Find(what:=State, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find State : " & State) Else 'find number of rows for state LastRow = c.Row Do While .Range("A" & (LastRow + 1)) = State And _ LastRow <= Rows.Count LastRow = LastRow + 1 Loop NumRows = LastRow - c.Row + 1 Set CopyRange = .Range("B" & c.Row & ":C" & LastRow) End If End With RowCount = 2 StartRow = RowCount With ResultSht Do While Range("B" & RowCount) < "" 'wait until last row of Name before doing the copy If Range("B" & RowCount) < Range("B" & (RowCount + 1)) Then If (RowCount - StartRow + 1) < NumRows Then Name = .Range("B" & RowCount) MsgBox ("Error in " & Name & " Acount. Number of Rows don't match") Else CopyRange.Copy Destination:=.Range("D" & StartRow) StartRow = RowCount + 1 End If End If RowCount = RowCount + 1 Loop End With End Sub "VickiV" wrote: Good questions. I cannot use standard zipcodes, as some of my ziphigh and ziplow values are custom values rather than official zipcodes. And I am also |
| All times are GMT +1. The time now is 12:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com