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
:
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)
:
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
:
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 & "
" & 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 & "
" & LastRow)
End If
End With
End Sub
:
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
:
See Answers below
:
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 & "
" & 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
:
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
:
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
:
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
:
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
