PDA

View Full Version : unable to fetch the data from excel sheet based on a given condition.



ravikr2678
10-31-2007, 03:14 PM
Hi All,
Need some help.. for example : below is the data which i have in excel.
7/20/2007 7/27/2007 8/3/2007
Name Levemir1 Levemir2 Levemir3
NATION 29788.5972953 2.6363167 8.974
NATION 1.070 6.4451706 9.0091895
NATION 12082.1521246 3.6271272 0.252
North 1514 3.8221515 7.56316
Midwest 8667.7328 689.6729 309.436
East 6476.0964 67.89167 09.38
South 13844.271 13583.9791 4806.524


So, I need to get the value ( 4806.524) of 'Levemer' (3rd column) in 'south' (Last row) region for latest week i.e 8/3/2007 pasted on any sheet2 cell.
Pls. let me know the code for this..
Basically, i need a cell value for which the row and the corresponding column should match..

Pls. help me..this is so very urgent..
THanks in advance..

xld
10-31-2007, 03:54 PM
=INDEX(A1:D9,MATCH("South",A1:A9,0),MATCH(--"2007-08-03",A1:D1,0))

ravikr2678
10-31-2007, 04:37 PM
=INDEX(A1:D9,MATCH("South",A1:A9,0),MATCH(--"2007-08-03",A1:D1,0))

ravikr2678
10-31-2007, 04:57 PM
Oh I'm so sorry ..that I could not make the problem clear..
However thank you so much for the quick reply.

Below is the code that i've developed...


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



This code outputs the addres of a cell, that fine..
but what i need is the Value of the cell which is 30 rows down from that particular cell which is displayed with reference.

Hope this is possible frm here..
pls. paste the code if you can solve this..
if i had your email address i could have given the excel sheet data po explain you better..
Pls. look in to it..
thanks a ton. :)

xld
10-31-2007, 05:02 PM
I gave you a non-code solution, in one line. The smileys were corrected as well.

ravikr2678
10-31-2007, 05:33 PM
=INDEX(A1:D9,MATCH("South",A1:A9,0),MATCH(--"2007-08-03",A1:D1,0))

Oh yes, but then.. the date changes every week..as new columns are added..
above code works only if we know the date,
so it has to match with the latest input date automatically..

ravikr2678
10-31-2007, 05:38 PM
If I'm not makingmyself clear..I will send you the excell sheet..
if i can get the email address.. ( If this is not an issue )

xld
11-01-2007, 01:21 AM
=INDEX(A1:D9,MATCH("South",A1:A9,0),MATCH(--"2007-08-03",A1:D1,0))

I am not interested in providing a VBA solution for a problem that is easily solved by a formula.