+ Reply to Thread
Results 1 to 4 of 4

blank value for row and column in msgbox

Hybrid View

  1. #1
    jjfjr
    Guest

    blank value for row and column in msgbox

    Hi;

    I'm trying to get some code working that inspects information on one sheet
    called "data" and displays any matches in the other sheet called "result".
    The result sheet is split into two areas: search criteria and results. When a
    submit button is clicked after criteria is entered in the cells, matching
    rows in the data sheet will be displayed in the results area of the "result"
    sheet. I set some variables as follows:

    Dim MyRow As Integer, MyCol As Integer
    Dim CritRow As Integer, CritRng As String, RightCol As Integer
    Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer

    DataRng = "A2:H2" ' range of column headers for Data table
    CritRng = "B3:I5" ' range of cells for Criteria table
    ResultsRng = "B8:I8"
    LeftCol = Range(ResultsRng).Column
    RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1

    When the following code is executed, the value of TopRow is blank.

    TopRow = Worksheets("Data").Range(DataRng).Row
    MsgBox "TopRow= ", TopRow

    Likewise, the following code shows blanks for the values of MyCol and MyRow.

    For MyRow = TopRow + 1 To BottomRow
    MsgBox "MyRow=", MyRow
    For MyCol = LeftCol To RightCol
    MsgBox "MyCol=", MyCol
    MsgBox "Cells(MyRow,MyCol).Value=", Cells(MyRow, MyCol).Value
    If Cells(MyRow, MyCol).Value <> "" Then CritRow = MyRow
    Next
    Next

    As a result, the CritRow is always 0 so the following else clause is never
    executed.

    If CritRow = 0 Then
    MsgBox "No Criteria detected"
    Else
    CritRng = Range(Cells(TopRow, LeftCol), Cells(CritRow, RightCol)).Address

    The Data sheet is filled with information. so I'm at a loss as to why I'm
    getting blank values. Any help is greatly appreciated.
    --
    JJFJR

  2. #2
    Toppers
    Guest

    RE: blank value for row and column in msgbox

    Hi,

    Do the following changes:

    MsgBox "TopRow= " & TopRow
    MsgBox "MyRow= " & MyRow
    MsgBox "MyCol= " & MyCol

    HTH

    "jjfjr" wrote:

    > Hi;
    >
    > I'm trying to get some code working that inspects information on one sheet
    > called "data" and displays any matches in the other sheet called "result".
    > The result sheet is split into two areas: search criteria and results. When a
    > submit button is clicked after criteria is entered in the cells, matching
    > rows in the data sheet will be displayed in the results area of the "result"
    > sheet. I set some variables as follows:
    >
    > Dim MyRow As Integer, MyCol As Integer
    > Dim CritRow As Integer, CritRng As String, RightCol As Integer
    > Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer
    >
    > DataRng = "A2:H2" ' range of column headers for Data table
    > CritRng = "B3:I5" ' range of cells for Criteria table
    > ResultsRng = "B8:I8"
    > LeftCol = Range(ResultsRng).Column
    > RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1
    >
    > When the following code is executed, the value of TopRow is blank.
    >
    > TopRow = Worksheets("Data").Range(DataRng).Row
    > MsgBox "TopRow= ", TopRow
    >
    > Likewise, the following code shows blanks for the values of MyCol and MyRow.
    >
    > For MyRow = TopRow + 1 To BottomRow
    > MsgBox "MyRow=", MyRow
    > For MyCol = LeftCol To RightCol
    > MsgBox "MyCol=", MyCol
    > MsgBox "Cells(MyRow,MyCol).Value=", Cells(MyRow, MyCol).Value
    > If Cells(MyRow, MyCol).Value <> "" Then CritRow = MyRow
    > Next
    > Next
    >
    > As a result, the CritRow is always 0 so the following else clause is never
    > executed.
    >
    > If CritRow = 0 Then
    > MsgBox "No Criteria detected"
    > Else
    > CritRng = Range(Cells(TopRow, LeftCol), Cells(CritRow, RightCol)).Address
    >
    > The Data sheet is filled with information. so I'm at a loss as to why I'm
    > getting blank values. Any help is greatly appreciated.
    > --
    > JJFJR


  3. #3
    jjfjr
    Guest

    RE: blank value for row and column in msgbox

    Hi;

    Thanks for the idea...it worked! I also have the following line:

    MsgBox "Cells(MyRow,MyCol).Value=" , Cells(MyRow, MyCol).Value

    which was showing up as blank and then I changed it to:

    MsgBox "Cells(MyRow,MyCol).Value=" & Cells(MyRow, MyCol).Value

    but it still comes up blank...any ideas?

    Thanks


    "Toppers" wrote:

    > Hi,
    >
    > Do the following changes:
    >
    > MsgBox "TopRow= " & TopRow
    > MsgBox "MyRow= " & MyRow
    > MsgBox "MyCol= " & MyCol
    >
    > HTH
    >
    > "jjfjr" wrote:
    >
    > > Hi;
    > >
    > > I'm trying to get some code working that inspects information on one sheet
    > > called "data" and displays any matches in the other sheet called "result".
    > > The result sheet is split into two areas: search criteria and results. When a
    > > submit button is clicked after criteria is entered in the cells, matching
    > > rows in the data sheet will be displayed in the results area of the "result"
    > > sheet. I set some variables as follows:
    > >
    > > Dim MyRow As Integer, MyCol As Integer
    > > Dim CritRow As Integer, CritRng As String, RightCol As Integer
    > > Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer
    > >
    > > DataRng = "A2:H2" ' range of column headers for Data table
    > > CritRng = "B3:I5" ' range of cells for Criteria table
    > > ResultsRng = "B8:I8"
    > > LeftCol = Range(ResultsRng).Column
    > > RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1
    > >
    > > When the following code is executed, the value of TopRow is blank.
    > >
    > > TopRow = Worksheets("Data").Range(DataRng).Row
    > > MsgBox "TopRow= ", TopRow
    > >
    > > Likewise, the following code shows blanks for the values of MyCol and MyRow.
    > >
    > > For MyRow = TopRow + 1 To BottomRow
    > > MsgBox "MyRow=", MyRow
    > > For MyCol = LeftCol To RightCol
    > > MsgBox "MyCol=", MyCol
    > > MsgBox "Cells(MyRow,MyCol).Value=", Cells(MyRow, MyCol).Value
    > > If Cells(MyRow, MyCol).Value <> "" Then CritRow = MyRow
    > > Next
    > > Next
    > >
    > > As a result, the CritRow is always 0 so the following else clause is never
    > > executed.
    > >
    > > If CritRow = 0 Then
    > > MsgBox "No Criteria detected"
    > > Else
    > > CritRng = Range(Cells(TopRow, LeftCol), Cells(CritRow, RightCol)).Address
    > >
    > > The Data sheet is filled with information. so I'm at a loss as to why I'm
    > > getting blank values. Any help is greatly appreciated.
    > > --
    > > JJFJR


  4. #4
    Dave Peterson
    Guest

    Re: blank value for row and column in msgbox

    I didn't see where you were determining the bottomrow?

    And if Data isn't the activesheet, you could have trouble.

    and your msgbox lines need to look more like:

    msgbox "some string=" & somevar
    Not
    msgbox "some string=", somevar

    I'm not sure if this helps, but maybe...

    Option Explicit
    Sub testme01()

    Dim MyRow As Integer, MyCol As Integer
    Dim CritRow As Integer, CritRng As String, RightCol As Integer
    Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer
    Dim DataRng As String
    Dim ResultsRng As String


    DataRng = "A2:H2" ' range of column headers for Data table
    CritRng = "B3:I5" ' range of cells for Criteria table
    ResultsRng = "B8:I8"
    LeftCol = Range(ResultsRng).Column
    RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1

    'When the following code is executed, the value of TopRow is blank.

    With Worksheets("data")
    TopRow = .Range(DataRng).Row
    MsgBox "TopRow= " & TopRow
    BottomRow = .Range(DataRng).Rows.Count + TopRow - 1
    MsgBox "bottomrow= " & BottomRow

    'Likewise, the following code shows blanks for the
    'values of MyCol and MyRow.

    For MyRow = TopRow + 1 To BottomRow
    MsgBox "MyRow=", MyRow
    For MyCol = LeftCol To RightCol
    MsgBox "MyCol=", MyCol
    MsgBox ".Cells(MyRow,MyCol).Value=" & .Cells(MyRow, MyCol).Value
    If .Cells(MyRow, MyCol).Value <> "" Then CritRow = MyRow
    Next MyCol
    Next MyRow
    End With

    End Sub

    Notice the dots in front of the range objects--that means that they belong to
    the previous with statement--in this case worksheets("data").



    jjfjr wrote:
    >
    > Hi;
    >
    > I'm trying to get some code working that inspects information on one sheet
    > called "data" and displays any matches in the other sheet called "result".
    > The result sheet is split into two areas: search criteria and results. When a
    > submit button is clicked after criteria is entered in the cells, matching
    > rows in the data sheet will be displayed in the results area of the "result"
    > sheet. I set some variables as follows:
    >
    > Dim MyRow As Integer, MyCol As Integer
    > Dim CritRow As Integer, CritRng As String, RightCol As Integer
    > Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer
    >
    > DataRng = "A2:H2" ' range of column headers for Data table
    > CritRng = "B3:I5" ' range of cells for Criteria table
    > ResultsRng = "B8:I8"
    > LeftCol = Range(ResultsRng).Column
    > RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1
    >
    > When the following code is executed, the value of TopRow is blank.
    >
    > TopRow = Worksheets("Data").Range(DataRng).Row
    > MsgBox "TopRow= ", TopRow
    >
    > Likewise, the following code shows blanks for the values of MyCol and MyRow.
    >
    > For MyRow = TopRow + 1 To BottomRow
    > MsgBox "MyRow=", MyRow
    > For MyCol = LeftCol To RightCol
    > MsgBox "MyCol=", MyCol
    > MsgBox "Cells(MyRow,MyCol).Value=", Cells(MyRow, MyCol).Value
    > If Cells(MyRow, MyCol).Value <> "" Then CritRow = MyRow
    > Next
    > Next
    >
    > As a result, the CritRow is always 0 so the following else clause is never
    > executed.
    >
    > If CritRow = 0 Then
    > MsgBox "No Criteria detected"
    > Else
    > CritRng = Range(Cells(TopRow, LeftCol), Cells(CritRow, RightCol)).Address
    >
    > The Data sheet is filled with information. so I'm at a loss as to why I'm
    > getting blank values. Any help is greatly appreciated.
    > --
    > JJFJR


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1