I'm not sure how to start this code.
I have a lot of rows/columns of data, but I need help coding it so that all I'd have to enter in is the record temperature, and then within each specific row, to find the times that specific record temperature comes up, and to automatically output the years (columns) that it showed up in in specific cells.
Can anyone help me, or at least help me get started?
Thanks!
Can you post a sample workbook?
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Gladly. How do I put the workbook on here though?
Basically I need to be able to search an individual row (The date) and look for the maximum temp record (given) in each specific row. Wherever the temperature is, I want it to automatically output into the column marked (Year) (and if there are multiple, just keep adding to the right of the cell) the year(s) that specific temperature happened.
I hope that makes sense...
Last edited by aestasreif; 02-13-2013 at 09:49 PM. Reason: unnecessary text.
Go to advanced and click on the paper clip
Hope this works.
I'll look at it anon.
This works for me
However, you will have to move maximum temp to column 2, and year to column 3.
I uploaded code with sample workbook.
Regards
JS.
SampleWorkBookForMacroHelp.xlsm
Sub record()
Dim Year As String
Dim cell As Object
Dim col As Integer
Dim row As Integer
Dim count As Long, count2 As Long, x As Integer, y As Integer
Dim max As Integer
Dim several As Boolean
Cells.Find("Dates").EntireColumn.SpecialCells(xlCellTypeConstants).Select
col = Selection.Column
count = Selection.count
For row = 2 To count 'looping trough all dates, but skips the label
max = 0
Cells(row, col).EntireRow.SpecialCells(xlCellTypeConstants).Select
count2 = Selection.count
For x = 4 To count2 'looping through all years of record
Cells(row, x).Select
If Selection = max Then
Year = Year & ", " & Cells(1, x).Text
End If
If Selection > max Then 'check if max
max = Selection
Year = Cells(1, x).Text
End If
If x = count2 Then
Cells(row, 2).Select
Selection = max
Selection.Offset(0, 1) = Year
End If
Next x
Next row
End Sub
Last edited by Stroem; 02-14-2013 at 03:17 AM.
Hi aestasreif,
Try this:
Directions for running the routine(s) just supplied
Please Login or Register to view this content.
Copy the code to the clipboard
Press ALT + F11 to open the Visual Basic Editor.
Open a macro-enabled Workbook or save your Workbook As Macro-Enabled
Select “Module” from the Insert menu
Type "Option Explicit" then paste the code under it
With the cursor between Sub and End Sub press F5 (F8 to Single Step)
OR
Press ALT + Q to close the code window.
Press ALT + F8 then double click on the macro name
This works beautifully in my sample code, but when I adjust it to the workbook I need (has 130 columns and 363 rows), it doesn't quite work.. The ones with multiple years it works. But when it's just 1 year, it pulls up some weird date with a year of 1905...which doesn't make sense to me.
Do you know a way to fix this? I moved the record year and year columns over next to the first "date" column. And adjusted the row = # to match accordingly, but that problem won't go away.
^ Posted twice...
Last edited by aestasreif; 02-14-2013 at 05:27 PM. Reason: Unnecessary post.
To xladept:
Your code also worked beautifully in my sample workbook, but it has error/debug messages when I put it in my workbook that I need to use it in.
I'm not sure how to fix it though...
The one I'm using has 367 rows and the columns are set up like:
Date. (Blank). 1890. 1891. (all the way to 2012). (Blank). Date(same as first column). WarmMaximumRecord. Year.
I'm hesitant about posting that actual workbook on here, but I can email it if you'd need to see it.
Thank you so much for your help so far!
VBA?
Formula
K2;
=MAX(C2:I2)
L2;
=INDEX(C$1:I2,1,MATCH(K2,C2:I2,0))
and filldown
@jindon:
My excel changed...somehow. I don't have the letters on mine, like C1 or the others.
Here's what mine looks like on the "index" without the $'s:
=INDEX(R[-1]C[-9]:RC[-3],1,MATCH(RC[-1],R[-1]C[-9]:RC[-3],0))
When it's set up like this, where do the $ signs go...? I've never seen my excel have that set up before. :/
Go to "File" tab at the top of the Excel ribbon.
select "Options"
select "Formulas"
then untick "R1C1 reference style"
Ah ok. Thanks!
When I used the index on it, it only catches one of the years. If the max/min temperature is on there more than once though, it only puts one of the years. Is there a way to fix that?
Just a few rows would let me rewrite the routine.
1) to a Standard module
2) Change the formula in L2 toPlease Login or Register to view this content.
=LookUps(C$1:I2,K2," - ")
and filldown
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks