Message Box help vba

[caritx]

I have this code which works great but in the text I also want it to display the line count for column A and the sum of AZ

Sub test()
'
' Batch test
'
Dim addbatch As String

addbatch = InputBox("Batch")

Range("BC1").Select
ActiveCell.Offset(1, 0).FormulaR1C1 = addbatch


'
End Sub

I really appreciate any help you can give! Thanks

 

[xenou]

Can you give an example of what would be entered in the input box? I'm not sure I see where we get from there to a line count or a sum.

ξ

 

[caritx]

I have a message box that says this. I would like it to be the text over the imput box:

MsgBox ("Gifts" & " " & Range("B1")) & vbCrLf & "Sum" & " " & Range("B2") & vbCrLf & "Batch Name:"<o></o>


Thanks!

 

[xenou]

Not sure, is this closer to what you need:

Dim msg As String
Dim addbatch As String

msg = "Gifts" & " "
msg = msg & WorksheetFunction.CountA(Range("A2:A65536")) '//Count of Column A, starting at row 2
msg = msg & vbCrLf & "Sum" & " "
msg = msg & WorksheetFunction.Sum.Range("AZ2:AZ65536") '//Sum of Column AZ, starting at row 2
msg = msg & vbCrLf & "Batch Name:"

addbatch = InputBox(msg, "Batch")

Range("BC1").Offset(1, 0).FormulaR1C1 = addbatch

 

[caritx]

compile error:

argument not optional

highlights the

.sum

 

[caritx]

Currently I have two boxes and I want to combine them. I want the gift and sum info above the field where they can enter the batch data.

 

[xenou]

Fixed the compile error, I think. What I am unsure of is if I am summing, counting properly. Check my comments (in green) below - are they right for the cells to add up or count up? Note that we can format the numbers later.

Dim msg As String
Dim addbatch As String

msg = "Gifts" & " "
msg = msg & WorksheetFunction.CountA(Range("A2:A65536")) [COLOR="SeaGreen"]'//Count of Column A, starting at row 2[/COLOR]
msg = msg & vbCrLf & "Sum" & " "
msg = msg & WorksheetFunction.Sum(Range("AZ2:AZ65536")) [COLOR="seagreen"]'//Sum of Column AZ, starting at row 2[/COLOR]
msg = msg & vbCrLf & "Batch Name:"

addbatch = InputBox(msg, "Batch")

Range("BC1").Offset(1, 0).FormulaR1C1 = addbatch