Returning value needs to be changed.

[ravikr2678]

Hi All,
Need some help ..

Sheets("Sheet2").Cells(1, 1).Value = arTemp(j)

This code returns value like this : $p$2

artemp(j) is an array, returning address of a cell.

So, what i need is to return 'p2' instead of '$P$2'

This is very Urgent, pls. help..

Thanks in advance.

[malik641]

When you store the address into arTemp(j), you are storing it as an absolute reference.

So you're probably doing this:
arTemp(j) = Range(i,"P").Address

When you should be doing:
arTemp(j) = Range(i,"P").Address(0,0)

Hurry!!!

[ravikr2678]

When you store the address into arTemp(j), you are storing it as an absolute reference.

So you're probably doing this:
arTemp(j) = Range(i,"P").Address

When you should be doing:
arTemp(j) = Range(i,"P").Address(0,0)

Hurry!!!

[ravikr2678]

Sorry, for not making myself clear..
well, i actually was working on the below code..


Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean
' --------------------------------------------------------------------------------------------------------------
' FindAll - To find all instances of the1 given string and return the row numbers.
' If there are not any matches the function will return false
' --------------------------------------------------------------------------------------------------------------
On Error GoTo Err_Trap
Dim rFnd As Range ' Range Object
Dim iArr As Integer ' Counter for Array
Dim rFirstAddress ' Address of the First Find
' -----------------
' Clear the Array
' -----------------
Erase arMatches
Set rFnd = oSht.Range(sRange).Find(What:=sText, LookIn:=xlValues, LookAt:=xlPart)
If Not rFnd Is Nothing Then
rFirstAddress = rFnd.Address
Do Until rFnd Is Nothing
iArr = iArr + 1
ReDim Preserve arMatches(iArr)
arMatches(iArr) = rFnd.Address ' rFnd.Row ' Store the Row where the text is found
Set rFnd = oSht.Range(sRange).FindNext(rFnd)
If rFnd.Address = rFirstAddress Then Exit Do ' Do not allow wrapped search
Loop
FindAll = True
Else
' ----------------------
' No Value is Found
' ----------------------
FindAll = False
End If

' -----------------------
' Error Handling
' -----------------------
Err_Trap:
If Err <> 0 Then
MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All"
Err.Clear
FindAll = False
Exit Function
End If
End Function


Sub Drive_The_FindAll_Function()
' Sample Sub to Drive the Function
Dim arTemp() As String 'Temp Array
Dim bFound As Boolean 'Flag
Dim i1 As Integer 'Array Counter
Dim j As Integer

j = 0
bFound = FindAll("Levemir", ActiveSheet, "D2:IT2", arTemp())
If bFound = True Then
For i1 = 1 To UBound(arTemp)
' The Address Can be used for extracting data
j = j + 1
Next i1
Else
MsgBox "Search Text Not Found"
End If

Sheets("Sheet2").Cells(1, 1).Value = arTemp(j)



End Sub


So, in sheet2 this is returning '$p$2' as out put..
I was wondering if i can get it as some 'column and row no.' so that i can useit further to find the 30th cell value in the same column down the line using an Offset syntax. :)

[ravikr2678]

Oh this worked..!! :)
Thank you so much.
It really helped me. :)

[malik641]

Not a bad guess, huh? :)