VBA or formula to show sheet name based off two criteria.

[jdaron]

Hello all,
I am trying to have excel show me the sheet name of a workbook based off two criteria. what i am trying to do is get the formula or VBA to show me the sheet names for everything with a date between 1/1/2014 -12/31/2014 and value is less than 2,500. All the sheets are formatted the same so the dates are all in column
G and the value is in column I of each sheet. so for instance if column G on "sheet 30" is 7/1/2014 and the value in column I is 1,000 it would show "sheet 30" as an end result( or something similar to that). i expected this to show multiple sheets and multiple times for some sheets(i.e. multiple items could meet the criteria in the same sheet). I am just not sure of the best way to achieve this. Please tell me if any more information would help as this is my first post. Thanks in advance to anyone hat can help.

 

[sericom]

Give this a go

Sub sample()
    For Each s In ThisWorkbook.Worksheets
        For Each c In s.Range("G1:G" & s.Range("G" & Rows.Count).End(xlUp).Row)
            If c.Offset(, 1) > 0 And c.Offset(, 1) <= 2500 And IsDate(c) Then
                If Year(c) = 2014 Then
                    t = t & s.Name & " - Value:" & c.Text & ", Date:" & c.Offset(, 1).Text & vbNewLine
                End If
            End If
        Next
    Next
    MsgBox t
End Sub

 

[jdaron]

Hello Thanks for the reply sadly i tried this and it does not work it just gives me a blank message box. Maybe i am doing something wrong ? any help would be nice. thanks again.

 

[sericom]

Sorry, i tested in columns G and H.
Wherever you see Offset(,1), change it to Offset(,2)

 

[jdaron]

Thank you very much! Just one thing sorry i am still new to this VBA but it looks like the message box is hitting is Character limit(sorry i did not mention i expected it to be quite a lot of valid expressions). do you know how i could get this to somewhere without a character limit. Anything will do a new sheet or on an existing sheet.
Thanks again so much.

 

[sericom]

This should place them on Sheet1 starting at A1

Sub sample()
    i = 1
    For Each s In ThisWorkbook.Worksheets
        For Each c In s.Range("G1:G" & s.Range("G" & Rows.Count).End(xlUp).Row)
            If c.Offset(, 1) > 0 And c.Offset(, 1) <= 2500 And IsDate(c) Then
                If Year(c) = 2014 Then
                    Worksheets("Sheet1").Range("A" & i) = s.Name & " - Value:" & c.Text & ", Date:" & c.Offset(, 1).Text
                    i = i + 1
                End If
            End If
        Next
    Next
End Sub