loop through columns to find current month

[spq24]

I have 2 rows of data the first row is the months (Jan-11, Feb-11, etc.) below each month is data for that current month. I am trying to use vba to find the current month, and place the data below it. I have everything else done but how to find out if its the current month and then to paste the data into the cell 2 below that proper month.

Right now I am using a formula: =month(today()) to get the current month and then use a vlookup to show the name in the proper format. I was figuring on using a loop to go through the column and if they match then put it below there but I am sure there is a better way.

If there is not a better way, how should I set up the loop?

Thanks

 

[MrKowz]

I don't think VBA will be required here at all. See if this gets you on the right track:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Jan-11</td><td style=";">Feb-11</td><td style=";">Mar-11</td><td style=";">Apr-11</td><td style=";">May-11</td><td style=";">Jun-11</td><td style=";">Jul-11</td><td style=";">Aug-11</td><td style="text-align: right;;"></td><td style=";">Value for current month:</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">20</td><td style="text-align: right;;">30</td><td style="text-align: right;;">40</td><td style="text-align: right;;">50</td><td style="text-align: right;;">60</td><td style="text-align: right;;">70</td><td style="text-align: right;;">80</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K1</th><td style="text-align:left">=INDEX(<font color="Blue">A2:H2,,MATCH(<font color="Red">TEXT(<font color="Green">TODAY(<font color="Purple"></font>),"mmm-yy"</font>),A1:H1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Norie]

You could use Find.

Dim rngThisMonth As Range
Dim varMonthValue
 
    Set rngThisMonth = Range("1:1").Find(Format(Now, "mmm-yy"), , xlValues)
 
    If rngThisMonth Is Nothing Then
        MsgBox "Not found"
    Else
        
        varMonthValue = InputBox("Enter value for this month:", Format(Now, "mmmm") & " value.", 0)
        
        If varMonthValue <> "" Then
        
            rngThisMonth.Offset(1) = varMonthValue

        End If
    
    End If

By the way are you sure you need a VLOOKUP to get the month name?

This formula would return MAY-11.

=TEXT(TODAY(), "mmm-yy")

 

[spq24]

Hey,

I can't get this to work for me. So, I have cell O13 doing a COUNTA of another table which is where I get the number from. So the vba code copies this number, than I want it to find the current month, and paste that number 2 cells below the current month.

Does this help?

 

[Norie]

What doesn't work?

The code or the formula?

Both should, but both could also be affected by how exactly you've formatted the dates/months in row 1.

 

[spq24]

Neither is working for what I need to do. copy cell O13, find the current month Jan-11:Dec-11, and paste it into the cell 2 below the current month. (So, if the value in O13 is 10, it would find May-11(cell F11) and paste it two below there (cell F3)

 

[Norie]

Not working how exactly?

Do you get an input box with the code?

If you don't then that might be because of the formatting of the date in the workbook or in the code.

Also, I assumed the months were in row 1 but now I'm very confused - how is F3 2 rows below F11?

Is that just a typo?

If they are in row 1 and you want to put the value in row 3, change Offset(1) to Offset(2) in the code.

 

[spq24]

the box does come up but I'd prefer to have no user interaction. When I run through the code it doesn't do anything though. It pops up with the box I put 5 in it and then run through the rest of it but it does nothing. It doesn't put the number anywhere or go to the proper cell

 

[Norie]

Well for the first part just remove the inputbox code replace it with this.

 If rngThisMonth Is Nothing Then

        MsgBox "Not found"

    Else
        
            varMonthValue = Range("O13").Value
                
            rngThisMonth.Offset(2) = varMonthValue
    
    End If

You don't need the message box unless you want to inform the user the month wasn't found.

You also don't need the variable varMonthValue, it can just be replaced with the range reference.

Try that and if it's still not doing anything then we might need more information.

The only reason for that happening (or not happening I suppose) I can think of right now is the ranges aren't 'looking' at the right worksheet.