Msgbox with the value of last cell in a range + the month

[JohnBi]

Hello to every body,
even if there are plenty of examples I am struggle to obtain in a msgbox the value of the last cell with its month in a dynamic range.
For example (see picture) Sept = 189.
Then, when the next cell will be with data: Oct=xxx, and so on.
Thanks for your help
Regards
John

 

[Joe4]

Are you really just trying to pull the value from the current month and current year and display it in a Message Box?

 

[rollis13]

I assume you have no other data in that sheet except what showen in the attached image. Try this, I adapted a snippet that I found in my archives:

Option Explicit
Sub FindLastValue()
    Dim row   As Long
    Dim col As Long
    Dim val As String
    With Cells.SpecialCells(xlCellTypeConstants).Areas
        row = .Item(.Count)(.Item(.Count).Count).row
        col = .Item(.Count)(.Item(.Count).Count).Column
        val = Cells(row, col).Text
    End With
    MsgBox Cells(1, col) & " = " & val
End Sub

Ps. Hi to all.

 

[JohnBi]

Yes, It might sound bizarre or eveny crazy, but this is only part of a more complex sheet and this should a fly info to check if I am doing the right input.
Thanks and Regards
John

 

[JohnBi]

I assume you have no other data in that sheet except what showen in the attached image. Try this, I adapted a snippet that I found in my archives:

Option Explicit
Sub FindLastValue()
    Dim row   As Long
    Dim col As Long
    Dim val As String
    With Cells.SpecialCells(xlCellTypeConstants).Areas
        row = .Item(.Count)(.Item(.Count).Count).row
        col = .Item(.Count)(.Item(.Count).Count).Column
        val = Cells(row, col).Text
    End With
    MsgBox Cells(1, col) & " = " & val
End Sub

Ps. Hi to all.

Thank you for your reply and help, but there are many other data in the sheet!
I'll see if I am able (many doubts!) to adapt it.
Regards
John

 

[Joe4]

Yes, It might sound bizarre or eveny crazy, but this is only part of a more complex sheet and this should a fly info to check if I am doing the right input.
Thanks and Regards
John

There does not seem to be any sort of consistency in your month names in row 5.
Most are the first three letters, but then you have entries like "March", "July", and "Sept".
If you could choose constant 3 letter abbreviations, or full name for all, it would be much easier to program against.

 

[rollis13]

I'll have another guess, use for example range:

Option Explicit
Sub FindLastValue()
    Dim uRow   As Long
    Dim col As Long
    Dim cVal As String
    Dim lr As Long
    lr = Range("A2").End(xlDown).row + 1
    With Range(Cells(1, 1), Cells(lr, 13)).SpecialCells(xlCellTypeConstants).Areas
        uRow = .Item(.Count)(.Item(.Count).Count).row
        col = .Item(.Count)(.Item(.Count).Count).Column
        cVal = Cells(uRow, col).Text
    End With
    MsgBox Cells(1, col) & " = " & cVal
End Sub

 

[JohnBi]

I'll have another guess, use for example range:

Option Explicit
Sub FindLastValue()
    Dim uRow   As Long
    Dim col As Long
    Dim cVal As String
    Dim lr As Long
    lr = Range("A2").End(xlDown).row + 1
    With Range(Cells(1, 1), Cells(lr, 13)).SpecialCells(xlCellTypeConstants).Areas
        uRow = .Item(.Count)(.Item(.Count).Count).row
        col = .Item(.Count)(.Item(.Count).Count).Column
        cVal = Cells(uRow, col).Text
    End With
    MsgBox Cells(1, col) & " = " & cVal
End Sub

Thank you Sir,
this is exactly what I am looking for! Not been able to achieve this result with my Vba knwoledge!
Again, many many thanks.
John

 

[rollis13]

Thanks for the positive feedback, glad we were able to be of some help.