formatting inputbox

[Guest]

Question:

Can I make an inputbox vb anything? When I try this:

marker = InputBox("What is the row number in which the last Cost Center
number is located?", 32)

I get 32 as a title

When I try this:

marker = InputBox("What is the row number in which the last Cost Center
number is located?", vbSystemModal)

I get 32 as a title

When I try this (and lord only knows why I thought this might work!):

marker = InputBox("What is the row number in which the last Cost Center
number is located?", vbSystemModal = False)

I get an error of some sort that was so long ago (this morning) that I don't
remember.

I need the user to be able to look at the page I just pulled up for them and
count the number of rows. The data is coming to me from elsewhere and is
formated weirdly. So weirdly I thought it best to just ask the user.

If in case, dear helper, you are interested. I tried to count the rows from
the bottom up starting at a point in the data in which there are four
consecutive blank cells in column b. Using some manipulation of ActiveCell
offset several times over in which all of them are blank, etc. That was
yesterday evening. If you believe you have a more fruitful rendition I am
all ears.

Thanks,
Nicole

 

[Bob Phillips]

Nicole,

Try this

marker = Application.InputBox("What is the row number in which " & vbnewline
& _
"the last Cost Center number is located?", , , , , , , 1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dick Kusleika]

If in case, dear helper, you are interested. I tried to count the
rows from the bottom up starting at a point in the data in which
there are four consecutive blank cells in column b. Using some
manipulation of ActiveCell offset several times over in which all of
them are blank, etc. That was yesterday evening. If you believe you
have a more fruitful rendition I am all ears.

Are you counting from the "four consecutive cells" that are closest to the
top? I.e. if B8:B11 are blank and B13:B16 are blank, would the answer be 7
or 12? If 7, then the below code should do it. If 12, you'll need to
change your loop to a For Next loop and loop backward from the bottom.

Function CountRows(sh As Worksheet) As Long

Dim rCell As Range

For Each rCell In Intersect(sh.Columns(2), sh.UsedRange).Cells
If Not IsNull(rCell.Resize(4).FormulaArray) Then
If Len(rCell.Resize(4).FormulaArray) = 0 Then
If rCell.Row > 1 Then
CountRows = rCell.Offset(-1, 0).Row
Else
CountRows = 0
End If
Exit Function
End If
End If
Next rCell

End Function