Re: Excel find row data in every column

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxx>
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@xxxxxxxxxxx>
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






.

Relevant Pages

  • Re: I believe Ive found an Access bug...can anyone confirm?
    ... Set objXL = CreateObject ... and use the Excel function from Access. ... Dim IRRValues() As Variant ' Set up array. ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to calculate frequency of lottery numbers?
    ... Microsoft MVP Excel ... I downloaded the previous 100 PowerBall games ... array K1:K59. ... histogram to visually display the frequencies ...
    (microsoft.public.excel.newusers)
  • Re: Evaluate v. WorksheetFunction: best practice?
    ... The argument passed to Evaluate must resolve to a string, ... representation of the VBA variable value or array (use Excel constant ... arguments are VBA arrays or Excel ranges. ...
    (microsoft.public.excel.programming)
  • Re: Excel find row data in every column
    ... Thanks anyway Lee. ... 'Find the value strname in the range set ... Set objSearch = cols.Find ... Can I create an array and then search that array for values contained ...
    (microsoft.public.scripting.vbscript)
  • Re: creating Excel Application Object in C#?
    ... This article demonstrates how to automate Microsoft Excel by using ... you can retrieve a two-dimensional array of values for ... errorMessage = String.Concat(errorMessage, ...
    (microsoft.public.excel.programming)