This will give a result based on the same logic in the table that I posted
earlier.
Sub GetGrahDegrees()
Dim varArray As Variant
Dim varOutput As Variant
Dim varFinal As Variant
Dim rng As Range
Dim lngCol As Long
Dim lngRow As Long
Dim lngActual As Long
Dim lngLoop As Long
Const lngTotalGrah As Long = 8
Application.ScreenUpdating = 0
Set rng = Worksheets("Grah").Cells.Find(What:="RAVI",
LookAt:=xlWhole).Offset(2)
varArray = rng.Parent.Range(rng, rng.End(xlDown).End(xlToRight))
ReDim varOutput(1 To UBound(varArray), 1 To 4)
ReDim varFinal(1 To UBound(varArray), 1 To 4)
Worksheets("Getlist").UsedRange.Offset(1, 3).Clear
Set rng =
Worksheets("Getlist").Range("A1").CurrentRegion.Columns(1).Cells
For Each rng In rng
lngCol = Application.Match(rng.Value,
Worksheets("Grah").Cells.Find(What:="RAVI", LookAt:=xlWhole).Resize(1,
lngTotalGrah), 0)
For lngRow = LBound(varArray) To UBound(varArray) - 1
If (varArray(lngRow, lngCol) <= rng(1, 2).Value And
varArray(lngRow + 1, lngCol) >= rng(1, 2).Value) Or _
(varArray(lngRow, lngCol) >= rng(1, 2).Value And
varArray(lngRow + 1, lngCol) <= rng(1, 2).Value + 1 And (varArray(lngRow +
1, lngCol) - varArray(lngRow, lngCol)) > 0) Or _
(varArray(lngRow, lngCol) < rng(1, 2).Value + 1 And
varArray(lngRow + 1, lngCol) >= rng(1, 2).Value + 1) Then
For lngLoop = 0 To 1
varOutput(lngRow + lngLoop, 1) = rng.Value
varOutput(lngRow + lngLoop, 2) = rng.Offset(, 1).Value
varOutput(lngRow + lngLoop, 3) = varArray(lngRow +
lngLoop, lngCol)
varOutput(lngRow + lngLoop, 4) = varArray(lngRow +
lngLoop, lngTotalGrah + 1)
Next lngLoop
End If
If Not IsEmpty(varOutput(lngRow, 1)) Then
lngActual = lngActual + 1
For lngLoop = 1 To 4
varFinal(lngActual, lngLoop) = varOutput(lngRow,
lngLoop)
varFinal(lngActual + 1, lngLoop) = varOutput(lngRow +
1, lngLoop)
Next lngLoop
End If
Next lngRow
ReDim varOutput(1 To UBound(varArray), 1 To 4)
Next rng
Worksheets("GetList").Range("D1").Resize(UBound(varFinal), 4).Value =
varFinal
Application.ScreenUpdating = 1
Erase varOutput
Erase varFinal
Erase varArray
Set rng = Nothing
lngActual = Empty
lngCol = Empty
lngRow = Empty
lngLoop=Empty
End Sub
Regards,
Sam Mathai Chacko
On Sun, Nov 27, 2011 at 12:58 AM, Sam Mathai Chacko <samde...@gmail.com>wrote:
> I don't suppose trying to force the output to look correct by looking up a
> grah that gives 14 related degrees in sequential order makes the cut.
>
> I believe the output should come something like this
>
> RAVI 225 224.9459578 12/1/2011 12:53 RAVI 225 225.030421 12/1/2011
> 14:53 RAVI 225 225.1148858 12/1/2011 16:53 RAVI 225 225.1993525 12/1/2011
> 18:53 RAVI 225 225.2838208 12/1/2011 20:53 RAVI 225 225.368291 12/1/2011
> 22:53 RAVI 225 225.4527628 12/2/2011 0:53 RAVI 225 225.5372364 12/2/2011
> 2:53 RAVI 225 225.6217118 12/2/2011 4:53 RAVI 225 225.7061888 12/2/2011
> 6:53 RAVI 225 225.7906676 12/2/2011 8:53 RAVI 225 225.8751481 12/2/2011
> 10:53 RAVI 225 225.9596304 12/2/2011 12:53 RAVI 225 226.0441143 12/2/2011
> 14:53 CHANDRA 255 254.1491998 11/27/2011 16:53 CHANDRA 255 255.3211126
> 11/27/2011
> 18:53 CHANDRA 255 256.4900705 11/27/2011 20:53 CHANDRA 255 254.9275255
> 12/25/2011
> 4:53 CHANDRA 255 256.1047022 12/25/2011 6:53 SHUKR 247 246.917992
> 11/27/2011
> 6:53 SHUKR 247 247.0213734 11/27/2011 8:53 SHUKR 247 247.1247536 11/27/2011
> 10:53 SHUKR 247 247.2281325 11/27/2011 12:53 SHUKR 247 247.3315102
> 11/27/2011
> 14:53 SHUKR 247 247.4348866 11/27/2011 16:53 SHUKR 247 247.5382617
> 11/27/2011
> 18:53 SHUKR 247 247.6416355 11/27/2011 20:53 SHUKR 247 247.7450081
> 11/27/2011
> 22:53 SHUKR 247 247.8483793 11/28/2011 0:53 SHUKR 247 247.9517491 11/28/2011
> 2:53 SHUKR 247 248.0551177 11/28/2011 4:53
> Nemi, could you please check if this is correct? If not, I'm lost for
> logic.
>
> Sam Mathai Chacko
>
>
> On Sat, Nov 26, 2011 at 8:38 PM, Sam Mathai Chacko <samde...@gmail.com>wrote:
>
>> Just a query on this one. What happens to Chandra 255? Is it supposed to
>> come like this?
>>
>> 225.22 226.46 227.69 228.91 230.14 231.36 232.58 233.80 235.02
>> 236.24 237.45 238.66 239.87 219.97
>> Sam
>>
>>
>> On Sat, Nov 26, 2011 at 8:09 PM, dguillett1 <dguille...@gmail.com> wrote:
>>
>>> try attached. When responding it is best to REPLY instead of a new
>>> thread.
>>>
>>> Don Guillett
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> *From:* Nemi Gandhi <nemigan...@gmail.com>
>>> *Sent:* Saturday, November 26, 2011 2:59 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* $$Excel-Macros$$ grah+date querry
>>>
>>> Thank you Don... the code works perfectly. but what if i want the result
>>> of two or more grah. Please proved code for them. And also explain the code
>>> if possible so that can alter the row and column nos where i will use this
>>> sheet. (the col and row will change in new sheet where i will use it).
>>>
>>> --
>>> Nemi Gandhi
>>> 98204 92963
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>>
>>
>> --
>> Sam Mathai Chacko
>>
>
>
>
> --
> Sam Mathai Chacko
>
--
Sam Mathai Chacko
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
