one way:

Dim myrng As Range
Dim myString As String

Set myrng = Range("G4") 'or G8
myString = myrng.Offset(0, -5).Text



In article ,
(dontwanna) wrote:

OK I was able to figure out the last problem just one more and i will
leave you people alone (at least for a little while)

Can you please tell me how to pull data from another row

ex

I pass a string and a range to a function

I then compare the string to the cells in the range.

say the string is in cell g4

say i want to retrieve the string that is in cell b4. how can i tell
it to pull from b4

(if string was in cell g8 then i would want to retrieve the string
from b8)

Thanks.

dontwanna

"Dontwanna" wrote in message
...
Ok thanks for the excellent help, it really helped.

Now for an easy one

I have a cell (c3) that contains the absolute row and column of a cell
(say
$e$9)
How can I pull the contents out of a cell in another excel file using the
contents of the cell above and place in the current cell that i am looking
at (g5).

Something like [spreadsheet.xls]sheet1=cell("contents",c3)

I know the above will not work but it might help explain the issue.

Thanks again for your help.

"J.E. McGimpsey" wrote in message
...
One non-programming way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=INDEX(data1!G:G,MATCH(TRUE,(data1!G1:G65535<work sheet2!C2),0))

One programming way:

Public Function FirstNonMatch(sNonFind As String, rng As Range) _
As Variant
Dim cell As Range
Dim found As Boolean
For Each cell In rng
If Not IsEmpty(cell.Value) And (cell.Value < sNonFind) Then
found = True
Exit For
End If
Next cell
If Not found Then
FirstNonMatch = CVErr(xlErrNA)
Else
FirstNonMatch = cell.Text
End If
End Function


Call as

Dim myvar
myvar = FirstNonMatch(Sheets("worksheet2").Range("C2"), _
Sheets("data1").Range("G1:G" & Range("G" & _
Rows.Count).End(xlUp).Row))
If VarType(myvar) = vbError Then
MsgBox "Not found"
Else
MsgBox myvar
End If


or from the worksheet

=firstnonmatch(worksheet2!C2,G1:G52)


In article ,
(dontwanna) wrote:

I need to create a procedure that excel can call that will then take
in a string variable and return another string variable.

Here is what i need.

Say I pass in the string 'jones' from worksheet2 cell c2. I need the
procedure to loop through all of the cells in column g of a seperate
excel spreadsheet (say the name is data1) and once it reaches a cell
with a string other than 'jones' I need the procedure to exit and
return the value of the string that does not equal the string 'jones'.

Is this possible in Excel???

Thanks again in advanced for your help!!