raul
Thu Dec 14 21:57:00 CST 2006
Since that you already have the data in Excel I really think you ought to
work with the data in that application. I am also sure that what you want to
do can be done quite nicely in Excel. You may want to post your question in
the Excel Programming Newsgroup if you don't get an answer under the latest
posting.
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming&lang=en&cr=US
Raul
"Matt Reid" wrote:
> Thanks again Raul. I still can't seem to get it to do what I want it to
> do. I think I am lacking in the skills as I can't apply what you've
> given me to my situation. I have reposted my dilemma under the posting
> 'Finding duplicates'. In that post I have described the problem in a
> different way.
>
> Matt
>
> Raul wrote:
> > You can exclude a column by using a different array for each column. You can
> > use the following snippet to determine the last row in each column to help
> > create the individual arrays.
> >
> > For j = 1 to NumColumns
> > Worksheets(ThisSheet).Cells(FirstRow, FirstCol -1 + j).Select
> > Selection.End(xlDown).Select
> > ActiveCell.Select
> > LastRow(j) = ActiveCell.Row
> > Next j
> >
> > I hope this helps,
> > Raul
> >
> > "Matt Reid" wrote:
> >
> > > No worries. Thanks anyway Lee.
> > > What I've got now is some code that checks a range (the whole sheet)
> > > and returns whether it has found the value in another column. However,
> > > because the range is the whole sheet it checks itself and reports
> > > itself as found! How can I exclude the column that the value belongs to
> > > from the search range?
> > > Code:
> > >
> > > 'Get number of columns
> > > y = y - 1
> > >
> > >
> > > i = 4
> > > t = 1
> > >
> > > DO until t > y
> > >
> > > 'Until the rows run out, incrementing i
> > > Do Until objExcel.Cells(i, t).Value = ""
> > > 'Set the value to search for to row i, column t
> > > strName = objExcel.Cells(i, t).Value
> > > 'Set the range to search
> > > Set cols = objExcel.Range("A1").SpecialCells(11)
> > > 'Find the value strname in the range set
> > > Set objSearch = cols.Find(strName)
> > >
> > > If objSearch Is Nothing Then
> > > MsgBox strname & " is not found"
> > > Else
> > > ob = objsearch.Column
> > > MsgBox strname & " is in column " & ob
> > > End If
> > >
> > > i = i + 1
> > >
> > > Loop
> > > t = t + 1
> > > Loop
> > >
> > >
> > > Lee Peedin wrote:
> > > > Matt,
> > > > One of the VBScript guys will have to answer that. I automate Excel
> > > > through a different language (ooRexx), but monitor this NG for
> > > > "gleaning" purposes. :-)
> > > >
> > > > Lee
> > > >
> > > > On 13 Dec 2006 05:52:43 -0800, "Matt Reid" <megabucket@hotmail.com>
> > > > wrote:
> > > >
> > > > >Thanks Lee.
> > > > >
> > > > >Can I create an array and then search that array for values contained
> > > > >in the array itself?
> > > > >E.g. if I have two rows in column 1, and two rows in column 2, I need
> > > > >to take the value in (1,1) and search all the other values for it and
> > > > >record the column it's in. Then I search for the value in (2,1) in all
> > > > >the other values as well. Can I create the array using all the values
> > > > >in the worksheet from row 4 to the end of each column, and then search
> > > > >the array using the values that make up the array?
> > > > >
> > > > >Many thanks
> > > > >
> > > > >Matt
> > > > >
> > > > >
> > > > >Lee Peedin wrote:
> > > > >> Not exactly sure of the VBScript syntax, but the constant "xlLastCell"
> > > > >> will return the last used column.row in the format:
> > > > >> $max_column$max_row
> > > > >>
> > > > >> Parse that value to determine how "deep" in the workbook you need to
> > > > >> "explore"
> > > > >>
> > > > >> lastcell = ActiveCell.SpecialCells(xlLastCell).Address
> > > > >>
> > > > >> Lee
> > > > >>
> > > > >> On 13 Dec 2006 04:28:58 -0800, "Matt Reid" <megabucket@hotmail.com>
> > > > >> wrote:
> > > > >>
> > > > >> >Hi Raul,
> > > > >> >
> > > > >> >Thanks for this. I am not exactly a scripter, so please bear with me. I
> > > > >> >have been asked to do this, and it is not my usual job.
> > > > >> >
> > > > >> >How do I determine the number of rows if all the columns are different
> > > > >> >lengths?
> > > > >> >
> > > > >> >Matt
> > > > >> >
> > > > >> >
> > > > >> >Raul wrote:
> > > > >> >> You could do this in Excel with VBA by passing the data from the worksheet
> > > > >> >> into an array and working with the array.
> > > > >> >>
> > > > >> >>
> > > > >> >> Dim DataArray() As Variant 'or String
> > > > >> >> Dim SearchResultsArray() As Long
> > > > >> >>
> > > > >> >> 'determine number of row and columns then
> > > > >> >>
> > > > >> >> ReDim DataArray(NumRows, NumCols)
> > > > >> >> ReDim SearchResultsArray(NumCols)
> > > > >> >>
> > > > >> >> ' load the values from the worksheet into the array as follows
> > > > >> >>
> > > > >> >> ThisSheet = ActiveSheet.Name
> > > > >> >> FirstRow = 1 'or whatever you want
> > > > >> >> FirstCol = 1 'or whatever you want
> > > > >> >>
> > > > >> >> For i = 1 To NumRows
> > > > >> >> For j = 1 To NumCols
> > > > >> >> DataArray(i, j) = Worksheets(ThisSheet).Cells(FirstRow - 1 + i, _
> > > > >> >> FirstCol - 1 + j).Value
> > > > >> >> Next j
> > > > >> >> Next i
> > > > >> >>
> > > > >> >> OR
> > > > >> >>
> > > > >> >> Set DataRange = Range(Worksheets(ThisSheet).Cells(FirstRow, FirstCol), _
> > > > >> >> Worksheets(ThisSheet).Cells(LastRow, LastCol))
> > > > >> >>
> > > > >> >> DataArray = DataRange.Value
> > > > >> >>
> > > > >> >>
> > > > >> >>
> > > > >> >> I hope this helps,
> > > > >> >> Raul
> > > > >> >>
> > > > >> >>
> > > > >> >> "Matt Reid" wrote:
> > > > >> >>
> > > > >> >> > Hi,
> > > > >> >> >
> > > > >> >> > What I have already is a vbscript that creates and populates an Excel
> > > > >> >> > spreadsheet with multiple columns of data. Now what I require is to
> > > > >> >> > take the data from each row in the each column, and search the other
> > > > >> >> > columns for this data. It then needs to do the same for the second
> > > > >> >> > column, and the third, and so on.
> > > > >> >> > So if I have columns of data, the script will take data from row 1,
> > > > >> >> > column 1 and check to see if that data is in any other cell. It then
> > > > >> >> > needs to tell me which column it is in. Once all the rows from column 1
> > > > >> >> > have been searched for, it then does the same for column 2, column 3,
> > > > >> >> > etc.
> > > > >> >> > Here is what I have so far:
> > > > >> >> > The value y has been set elsewhere and represents the number of
> > > > >> >> > columns.
> > > > >> >> >
> > > > >> >> > y = y - 1
> > > > >> >> >
> > > > >> >> >
> > > > >> >> > i = 4
> > > > >> >> > t = 1
> > > > >> >> > Do Until t = y
> > > > >> >> > 'Set range?
> > > > >> >> > Do Until objExcel.Cells(i, t).Value = ""
> > > > >> >> > strName = objExcel.Cells(i, t).Value
> > > > >> >> > Set objSearch = cols.Find(strName)
> > > > >> >> >
> > > > >> >> > If objSearch Is Nothing Then
> > > > >> >> > Else
> > > > >> >> > Msgbox y
> > > > >> >> > MsgBox strName & " was found."
> > > > >> >> > End If
> > > > >> >> >
> > > > >> >> > i = i + 1
> > > > >> >> >
> > > > >> >> > Loop
> > > > >> >> > t = t + 1
> > > > >> >> > Loop
> > > > >> >> >
> > > > >> >> > I am having difficulty defining the range to search at the moment. Any
> > > > >> >> > help would be very much appreciated.
> > > > >> >> >
> > > > >> >> > Matt
> > > > >> >> >
> > > > >> >> >
> > >
> > >
>
>