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

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

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

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