How to add more than 200+ values in my VBA code?

Question

m trying to use one VBA code which filters the city and paste the data accordingly in another sheet with reference to some column names

M trying to make a long list in array but it not taking a value more than 35, below is the mentioned code

Sub CopyDataByCity()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long
Dim citiesToFilter() As Variant
Dim city As Variant
Dim headerRow As Range
Dim currentRow As Long

' Set the source sheet
Set sourceSheet = ThisWorkbook.Sheets("Portfolio Summary")

' Set the target sheet
Set targetSheet = ThisWorkbook.Sheets("RAW DATA")

' Define the list of cities to filter on in the desired order
citiesToFilter = Array("Jacksonville, FL", "Houston-The Woodlands-Sugar Land, TX", "Seattle-Tacoma-Bellevue, WA", "Minneapolis-St. Paul-Bloomington, MN-WI", "San Antonio-New Braunfels, TX", "Portland-Vancouver-Hillsboro, OR-WA", "Philadelphia-Camden-Wilmington, PA-NJ-DE-MD", "Boston-Cambridge-Newton, MA-NH", "Nashville-Davidson--Murfreesboro--Franklin, TN", "Columbus, OH", "Indianapolis-Carmel-Anderson, IN", "Raleigh-Cary, NC", "Cincinnati, OH-KY-IN", "Richmond, VA", "Urban Honolulu, HI", "Greensboro-High Point, NC", "Sacramento-Roseville-Folsom, CA", "Colorado Springs, CO", "Stockton, CA", "San Jose-Sunnyvale-Santa Clara, CA", "Harrisburg-Carlisle, PA", "San Francisco-Oakland-Berkeley, CA", "Louisville/Jefferson County, KY-IN", "Reno, NV", "Lakeland-Winter Haven, FL", "San Diego-Chula Vista-Carlsbad, CA", "Fort Collins, CO", "Salt Lake City, UT", "Boulder, CO", "Kansas City, MO-KS", "Baltimore-Columbia-Towson, MD", "Memphis, TN-MS-AR", "Tallahassee, FL", "North Port-Sarasota-Bradenton, FL", "Tucson, AZ")

' Find the last row of data in the source sheet
lastRow = sourceSheet.Cells(Rows.Count, "A").End(xlUp).Row

' Get the header row
Set headerRow = sourceSheet.Range("6:6")

' Set the starting row in the target sheet
currentRow = 2 ' Assuming the header row is in row 1

' Loop through the cities to filter on in the desired order
For Each city In citiesToFilter
    ' Loop through the rows in the source sheet
    For i = 8 To lastRow ' Assuming the header row is in row 7
        ' Check if the city name in column Q matches the current city
        If sourceSheet.Cells(i, "Q").Value = city Then
            ' If the city name matches, copy the specified columns and paste into the target sheet
            targetSheet.Range("A" & currentRow).Value = sourceSheet.Cells(i, "C").Value ' Copy column
            targetSheet.Range("B" & currentRow).Value = sourceSheet.Cells(i, "H").Value ' Copy column
            targetSheet.Range("C" & currentRow).Value = sourceSheet.Cells(i, "D").Value ' Copy column
            targetSheet.Range("D" & currentRow).Value = sourceSheet.Cells(i, "S").Value ' Copy column
            targetSheet.Range("E" & currentRow).Value = sourceSheet.Cells(i, "I").Value ' Copy column
            targetSheet.Range("F" & currentRow).Value = sourceSheet.Cells(i, "R").Value ' Copy column
            targetSheet.Range("G" & currentRow).Value = sourceSheet.Cells(i, "W").Value ' Copy column
            targetSheet.Range("H" & currentRow).Value = sourceSheet.Cells(i, "N").Value ' Copy column
            targetSheet.Cells(currentRow, "I").Value = city ' Paste the city name to column I in the target sheet
            currentRow = currentRow + 1 ' Increment the current row variable
        End If
        
        ' Check if the next row is blank or if the city changes
        If sourceSheet.Cells(i + 1, "A").Value = "" Or sourceSheet.Cells(i + 1, "Q").Value <> city Then
            ' If the next row is blank or if the city changes, skip over it
            i = i + 1
        End If
    Next i
Next city
End Sub

score 9 · Answer 1 · answered Apr 17 '23 at 08:37

a) There is no limit of 35 elements for an array. You are reaching the maximum length of a code line of the VBA editor (1024 characters).

b) You can avoid that length limit by using line continue character at the end of the line and continue on the next line:

citiesToFilter = Array("Jacksonville, FL", "Houston-The Woodlands-Sugar Land, TX", _
                "Seattle-Tacoma-Bellevue, WA", (...)

However, there is also a limit of 25 lines that you can combine with that, so you might reach a limit there also.

c) You should use a function to get the list of cities so that you separate the program logic and the creation of the filter values. Have in mind that this list might change or you have a typo in one of the names and you will have a hard time to find the correct place in your code. Maybe something like that:

Function getCityList() As String()
    Dim cityList(1 to 200) as String
    cityList(1) = "Jacksonville, FL"
    cityList(2) = "Houston-The Woodlands-Sugar Land, TX"
    cityList(3) = "Seattle-Tacoma-Bellevue, WA"
    (...)
    getCityList = cityList
End Function

d) Instead of hard code the list in your VBA code, I guess it's a much better idea to have the list in a sheet. If you don't want the user to see that list, just hide it. The following code assumes that you have a sheet "CityList" with a named range "CityList"). If you can live with the fact that the list is a 2-dimensional array (you will need to change the For Each city In citiesToFilter statement)

Function getCityList() As Variant       
    Dim cityList 
    cityList= ThisWorkbook.Sheets("CityList").Range("CityList").Value
    getCityList = cityList 
End Function

If you prefer to have a one-dimensional array, just convert it:

Function getCityList() As String()        
    Dim sheetValues As Variant, i As Long
    sheetValues = ThisWorkbook.Sheets("CityList").Range("CityList").Value
    ReDim cityList(1 To UBound(sheetValues, 1)) As String
    For i = 1 To UBound(sheetValues, 1)
        cityList(i) = sheetValues(i, 1)
    Next
    getCityList = cityList
End Function

score 0 · Answer 2 · answered Apr 18 '23 at 03:44

If I understand you correctly...

The data in sourceSheet is from column A to Z starting from row 8 (where row 7 is the header). The data in column Q starting from row 8 to whatever is the city name mentioned in citiesToFilter.

In targetSheet, there is no data, except the header in A1:H1 (8 columns).

The code below is run from the explanation above.

Expected result, to each row of the found city cell in sourceSheet column Q (which criteria is coming from the loop to each element in citiesToFilter), put the value of "C", "H", "D", "S", "I", "R", "W", "N" found row city to targetSheet column A to H respectively which row is the lastrow with data + 1.

Sub test()
Dim sourceSheet As Worksheet, targetSheet As Worksheet
Dim rgS As Range, rgT As Range, rgR As Range
Dim citiesToFilter, el, arrCol, col

Set sourceSheet = ThisWorkbook.Sheets("Portfolio Summary")
Set rgS = sourceSheet.Range("Q8", sourceSheet.Range("Q" & Rows.Count).End(xlUp))
Set targetSheet = ThisWorkbook.Sheets("RAW DATA")
Set rgT = targetSheet.Range("A2")

citiesToFilter = Array("Jacksonville, FL", "Houston-The Woodlands-Sugar Land, TX", "Seattle-Tacoma-Bellevue, WA", "Minneapolis-St. Paul-Bloomington, MN-WI", "San Antonio-New Braunfels, TX", "Portland-Vancouver-Hillsboro, OR-WA", "Philadelphia-Camden-Wilmington, PA-NJ-DE-MD", "Boston-Cambridge-Newton, MA-NH", "Nashville-Davidson--Murfreesboro--Franklin, TN", "Columbus, OH", "Indianapolis-Carmel-Anderson, IN", "Raleigh-Cary, NC", "Cincinnati, OH-KY-IN", "Richmond, VA", "Urban Honolulu, HI", "Greensboro-High Point, NC", "Sacramento-Roseville-Folsom, CA", "Colorado Springs, CO", "Stockton, CA", "San Jose-Sunnyvale-Santa Clara, CA", "Harrisburg-Carlisle, PA", "San Francisco-Oakland-Berkeley, CA", "Louisville/Jefferson County, KY-IN", "Reno, NV", "Lakeland-Winter Haven, FL", "San Diego-Chula Vista-Carlsbad, CA", "Fort Collins, CO", "Salt Lake City, UT", "Boulder, CO", "Kansas City, MO-KS", "Baltimore-Columbia-Towson, MD", "Memphis, TN-MS-AR", "Tallahassee, FL", "North Port-Sarasota-Bradenton, FL", "Tucson, AZ")
arrCol = Array("C", "H", "D", "S", "I", "R", "W", "N")

For Each el In citiesToFilter
    With rgS
        If .Find(el, lookat:=xlWhole) Is Nothing Then GoTo nxtEL:
        .Replace el, True, xlWhole, , False, , False, False
        Set rgR = .SpecialCells(xlConstants, xlLogical)
        .Replace True, el, xlWhole, , False, , False, False
    End With

    For Each cell In rgR
        For Each col In arrCol
            rgT.Value = sourceSheet.Range(col & cell.Row).Value
            Set rgT = rgT.Offset(0, 1)
        Next
    Set rgT = rgT.Offset(1, 0).End(xlToLeft)
    Next
nxtEL:
Next el

End Sub

rgS is the range of data of city in sourceSheet, starting from Q8 to whatever row.
rgT is the starting cell to fill in targetSheet.
arrCol is the array of the expected column source to be put into target Sheet.

There are 3 loops,
A. loop for each element in citiesToFilter
B. loop for each found cell in rgS
C. loop for each column name in arrCol

Example of the first iteration of A....
"Jacksonville, FL" is found in Q10, Q15, Q20 of sourceSheet.
So those cells (Q10, Q15, Q20) are in rgR variable.

Now the next loop is B, to loop to each cell in rgR
Then go to loop C, to loop to each col in arrCol and put the result accordingly.

Example of the first iteration of B, the looped cell is cell Q10, which row is 10

So in the first iteration of loop C, sourceSheet.Range(col & cell.Row) is cell C10 , and it put C10 value into rgT ---> sourceSheet cell A2 first iteration of loop C.

In the second iteration of loop C, sourceSheet.Range(col & cell.Row) is cell H10, and it put H10 value into the next rgT.

And so on until the 8th iteration of loop C finish.

The next iteration of loop B, the looped cell is cell Q15, which row is 15.

So in the first iteration of loop C, sourceSheet.Range(col & cell.Row) is cell C15 , and it put C15 value into the next rgT...

In the second iteration of loop C, sourceSheet.Range(col & cell.Row) is cell H15, and it put H15 value into the next rgT.

And so on until the 8th iteration of loop C finish.
And so on until all the cell of loop B finish.
And then it goes to the 2nd element in citiesToFilter.

Sorry, it's difficult for me to explain.

let me try this one, thanks for the explanation @karma – dhanesh sharma Apr 18 '23 at 13:50 — dhanesh sharma, Apr 18 '23 at 13:50

How to add more than 200+ values in my VBA code?

2 Answers2