J
jjfjr
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.
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.