I need to write a vba procedure for the following. I have two columns on a spreadsheet. The left column represents time and the right column represents the corresponding degrees above the horizon for the moon. The time starts with moonrise and continues for a 24 hour period using minutes (1400 total). The moon angle typical starts at about -1 at moonrise. What I need to do is write a procedure that will look through he moon angle column and when it gets to 30 degrees above the horizon return the associated time to a cell on another sheets then I need it to continue through the same column and return the time the moon falls back below 30 degrees. This will be run from a user form to populate cells on a card containing solar and lunar data for an aviation knee card/checklist. Any ideas?
VBA Index, Match Excel 2007
-
-
-
Re: VBA Index, Match Excel 2007
Hi wid2001,
Welcome to OzGrid!!
Let us know how the following goes:
Code
Display MoreOption ExplicitSub Macro1() Dim lngStartRow As Long, _ lngEndRow As Long, _ lngMyRow As Long Dim wstSourceTab As Worksheet, _ wstOutputTab As Worksheet Application.ScreenUpdating = False Set wstSourceTab = Sheets("Sheet1") 'Source data tab. Change to suit. Set wstOutputTab = Sheets("Sheet2") 'Output data tab. Change to suit. lngStartRow = 2 'Starting row number for data. Change to suit. lngEndRow = wstSourceTab.Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Find the first value equal to, or greater than 30 in Col. B of the the 'wstSourceTab' For lngMyRow = lngStartRow To lngEndRow If wstSourceTab.Range("B" & lngMyRow).Value >= 30 Then wstOutputTab.Range("A2").Value = wstSourceTab.Range("A" & lngMyRow).Value 'Returns the value to cell A2 of the 'wstOutputTab'. Change to suit. Exit For End If Next lngMyRow lngStartRow = lngMyRow 'Starting from the location of the first value equal to, or greater than 30 in Col. B of the the 'wstSourceTab', 'find the next value lower than 30. For lngMyRow = lngStartRow To lngEndRow If wstSourceTab.Range("B" & lngMyRow).Value < 30 Then wstOutputTab.Range("A3").Value = wstSourceTab.Range("B" & lngMyRow).Value 'Returns the value to cell A3 of the 'wstOutputTab'. Change to suit. Exit For End If Next lngMyRow Application.ScreenUpdating = True MsgBox "Values have now been returned" End Sub
Regards,
Robert
-
Re: VBA Index, Match Excel 2007
Thanks for you response and your time. I appreciate it. I will give this a shot and see what I come up with.
-
Re: VBA Index, Match Excel 2007
I found a solution. If anyone reads this and has a similiar problem let me know and I can show you what worked for me. See above.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!