I see...no, each worksheet has a different number of zip codes. Maybe we
could put something in the code that would write the word "END" after all the
data in column C on all the worksheets and use the code as is...although it
is also bringing the word TOTAL over as well. I tried the A1 and "", but
received no results. I put the word END in column C on 4 pages and it worked
great to the 5th page. I've got 150 sheets though...ugh!
We are so close though!! Thanks so much already!
"Joel" wrote:
> I forgot to tell you that I needed to know where the last row oif data was in
> each worksheet. I added the word END to the last cell of column C. If there
> is another indication of the last row of data let me know. Maybe another
> column has data in every row that can be used
>
> then replace
> Do While StrComp(MyWorksheet.Range("C1").Offset(rowOffset:=RowCount - 1,
> columnOffset:=0), "End") <> 0
>
> with
>
> Do While StrComp(MyWorksheet.Range("A1").Offset(rowOffset:=RowCount - 1,
> columnOffset:=0), "") <> 0
>
> this tests for no data in column A.
>
> "David" wrote:
>
> > Almost there...
> > I fixed the line with Cell_Data =
> > MyWorksheet... into one line and ran the program.
> >
> > Got an Error 1004 on line;
> > Do While StrComp(MyWorksheet.Range("C1").Offset(rowOffset:=RowCount - 1,
> > columnOffset:=0), "End") <> 0
> >
> > The results I got were the zips from the first worksheet, but also got one
> > cell of text, and then the macro stopped.
> > Here is the results received.,...
> > 73301
> > 78613
> > 78630
> > 78641
> > 78642
> > 78645
> > 78646
> > 78717
> > 78718
> > 78720
> > 78726
> > 78729
> > 78730
> > 78731
> > 78732
> > 78750
> > 78755
> > 78756
> > 78757
> > 78759
> > 78766
> > 78799
> > Total
> > The stopped.
> >
> > "Joel" wrote:
> >
> > > Create a new worksheet that is named summary and use the code below. Column
> > > c on the summary sheet should be formated as text, otherwise, leading zeroes
> > > will be truncated.
> > >
> > > Sub zip_code()
> > > Dim Cell_Data As String
> > >
> > > zipcode_count = 0
> > > For Each MyWorksheet In Workbooks(ThisWorkbook.Name).Worksheets
> > > If StrComp("Summary", MyWorksheet.Name) <> 0 Then
> > > RowCount = 1
> > >
> > > Do While StrComp(MyWorksheet.Range("C1"). _
> > > Offset(rowOffset:=RowCount - 1, columnOffset:=0), "End") <> 0
> > >
> > > Cell_Data =
> > > MyWorksheet.Range("C1").Offset(rowOffset:=RowCount - 1, columnOffset:=0).Value
> > > If Len(Cell_Data) = 5 Then
> > >
> > > Found_Char = False
> > >
> > > For char_count = 1 To 5
> > > character = Mid(Cell_Data, char_count, 1)
> > > If (character < "0") And (character > "9") Then
> > > Found_Char = True
> > > Exit For
> > > End If
> > > Next char_count
> > >
> > > If Found_Char = False Then
> > >
> > > Worksheets("Summary").Range("C1").Offset(rowOffset:=zipcode_count, _
> > > columnOffset:=0) = Cell_Data
> > > zipcode_count = zipcode_count + 1
> > >
> > > End If
> > >
> > > End If
> > >
> > > RowCount = RowCount + 1
> > >
> > >
> > > Loop
> > > End If
> > > Next MyWorksheet
> > >
> > > End Sub
> > >
> > >
> > > "David" wrote:
> > >
> > > > I need to create a list of all zip codes on one worksheet that are contained
> > > > on 150 individual worksheets. Each worksheet has zip codes and some text in
> > > > column C. I want to create a worksheet at the end of the workbook and run a
> > > > macro that will list all the zip codes contained on the 150 worksheets in one
> > > > row so I can compare all the zip codes to a master list in another worksheet.
> > > > The macro needs to start with sheet 1, look at all the values in Column C,
> > > > add the ones that have 5 digit zip codes, and ignore blanks and text, and go
> > > > through all worksheets until the last sheet which will contain the list.
> > > > Can I get some help?
|
Similar Threads
