Message box

[jaycee]

I have a loop that checks column L for values of greater than 9, this then adds a message box to say this, what I need is the value in the corresponding row column "B" value to show in the message box.

IE L11 is 12 then messsage box should have the value for B11

 

[slinky]

Use activecell.Offset(0,-10).Value ​to grab the value from B (whilst in L)

 

[jaycee]

Thanks for the quick reply, but where do I put it in the messagebox
I would like something like the following
Message
"You have selected more than 9 Months for (value column B) do you require this"

 

[slinky]

What does the code look like right now?

If you want to use it in a messagebox, one route is to pass that value to a variable then display the variable as part of the text in the messagebox..
Something like this:
Text = ActiveCell.Offset(0, -10).Value
sure = MsgBox("You have selected more than 9 Months for " & Text & " do you require this", vbOKOnly, "Are you sure?")

(just made a quick edit to put the variable in the correct place!)

 

[jaycee]

Hi Slinky,
Thanks for that but I get error 1004 when trying

Below is my code

Sub Tour_Length()
Dim Text As String
 Dim r As Long
 Text = ActiveCell.Offset(0, -10).Value
    For r = Cells(Rows.Count, 12).End(xlUp).Row To 9 Step -1
        If Cells(r, 12).Value >= 9 Then MsgBox "You have stated that card Number " & vbNewLine & Text & "If this is correct then" & vbNewLine & "complete the extended tour form as well"
    Next r
End Sub

 

[jaycee]

I think I can see where the fault lies.

The cell in Column L is not beeing activated, I think therefore there is a fault with my loop?

 

[slinky]

Try that...
Sub Tour_Length()
Dim Text As String
Dim r As Long
For r = Cells(Rows.Count, 12).End(xlUp).Row To 9 Step -1
If Cells(r, 12).Value >= 9 Then
MsgBox("You have stated that card Number " & vbNewLine & Cells(r,2).value & " If this is correct then" & vbNewLine & "complete the extended tour form as well", vbOKOnly, "Sure?")
Next r
End Sub

(note, untested!)

 

[jaycee]

Hi Slinky,

Seems to work Ok now

Many thanks

 

[slinky]

Question; can you see why it works?

 

[jaycee]

Hi Slinky,

I did modify it slightly to the following

Sub Tour_Length()
Dim r As Long
For r = Cells(Rows.Count, 12).End(xlUp).Row To 9 Step -1
If Cells(r, 12).Value >= 9 Then MsgBox "You have stated that card Number " & vbNewLine & Cells(r, 2).Value & vbNewLine & " If this is correct then" & vbNewLine & "complete the extended tour form as well", vbOKOnly, "Sure?"
Next r
End Sub

The only problem I have now is that if I put this on the sheet to work after selection change, I get various errors as some of the cells are still blank and therefore cannot be used as data?