Gaining the Column A Value

C

Corey

I have the following code that lists Numerical Values in Column C that are >= to a Textbox Value,
But
As the Values are Anywhere Between 2-22 rows Below the Value in Column A,
i have been unable to gain this value from Column A and be able to use this to be the Value of a
Label when a CommandButton is pressed.

The value is Identified in the Following Code, But i think i need to go Backwards to Find the Value,
and have not been able to do this?

Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ListBox3.Clear
If TextBox1.Value <> "" Then
Dim lastcell As Long
Dim myrow As Long
Dim i As Long
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 1 To lastcell
If .Cells(myrow, 1) <> "" Then ' <=== THIS IS THE VALUE HERE I NEED TO IDENTIFY
If .Cells(myrow, 1).Offset(-1, 2).Value = ListBox1.Value Then
If .Cells(myrow, 1).Offset(-1, 6).Value = ListBox2.Value Then
For i = 2 To 22
If .Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = False And Cells(myrow, 3).Offset(i,
0).Value <> "" _
And IsNumeric(.Cells(myrow, 3).Offset(i, 0)) Then
If .Cells(myrow, 3).Offset(i, 0).Value <> "" And .Cells(myrow, 3).Offset(i, 0).Value >=
Val(TextBox1.Value) Then

ListBox3.AddItem Cells(myrow, 3).Offset(i, 0)
ListBox3.List(ListBox3.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address
End If
End If
Next i
End If
End If
End If
Next
End With
End If
Label8 = ListBox3.ListCount
Application.ScreenUpdating = True
End Sub

Want to do something like:
 
C

Corey

Rest of Message(sent accendently before completing

Private Sub CommandButton2_Click()
Label7 = ListBox3.Value
Application.ScreenUpdating = True
End Sub


Corey....
 
C

Corey

Got a Little Further:
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim lastcell As Long
Dim myrow As Long
Dim i As Long
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 1 To lastcell
If .Cells(myrow, 3) <> "" Then
If .Cells(myrow, 3).Value = Val(TextBox1.Value) Then
For i = 2 To 22
If .Cells(myrow, 3).Offset(i, -2).Value <> "" Then
If IsNumeric(.Cells(myrow, 3).Offset(i, -2)) = True Then
Label7 = .Cells(myrow, 3).Offset(i, -2).Value
End If
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

But the Value is in Column A but it is not the Correct Value?

Rest of Message(sent accendently before completing

Private Sub CommandButton2_Click()
Label7 = ListBox3.Value
Application.ScreenUpdating = True
End Sub


Corey....
 
T

Tom Ogilvy

If you want to walk backwards from the first row that is >= Textbox1 in
column C until you find a number in column A, then put that number in Label7
and quit looking:
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim lastcell As Long
Dim myrow As Long
Dim i As Long
Dim bExit as Boolean
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count,
"C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 1 To lastcell
bExit = False
If .Cells(myrow, 3) <> "" Then
If .Cells(myrow, 3).Value >= Val(TextBox1.Value) Then
For i = MyRow To 2 step - 1
If .Cells(i, 1).Value <> "" Then
If IsNumeric(.Cells(i, 1)) = True Then
Label7 = .Cells(i, 1).Value
bExit = True
End If
End If
if bExit then exit for
Next i
End If
End If
if bExit then exit for
Next myrow
End With
Application.ScreenUpdating = True
End Sub
 
C

Corey

Tom,
The value placed in Textbox1 is a Minimum Value i need to find in the Column C.
It represents various lengths contained in a roll
Each roll has a unique Numerical ID (Column A)

There can be up to 20 separate values in each Column A roll ID

I have code that can find a value >= Textbox1 value,
But i need to identify the Column A Value that is Accociated to that minumum value(it will be in
Column C

I tried your code
I get a Value that is in Column A, but ALWAYS the 1st value,
Not the Value associated to the Listbox3 value


Corey....
If you want to walk backwards from the first row that is >= Textbox1 in
column C until you find a number in column A, then put that number in Label7
and quit looking:
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim lastcell As Long
Dim myrow As Long
Dim i As Long
Dim bExit as Boolean
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count,
"C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 1 To lastcell
bExit = False
If .Cells(myrow, 3) <> "" Then
If .Cells(myrow, 3).Value >= Val(TextBox1.Value) Then
For i = MyRow To 2 step - 1
If .Cells(i, 1).Value <> "" Then
If IsNumeric(.Cells(i, 1)) = True Then
Label7 = .Cells(i, 1).Value
bExit = True
End If
End If
if bExit then exit for
Next i
End If
End If
if bExit then exit for
Next myrow
End With
Application.ScreenUpdating = True
End Sub
 
T

Tom Ogilvy

First, you should make a copy of your workbook and test any code I post in
that copy so your original code is not disturbed. This should be your
normal practice for any help you get from anyone. Frankly, your
descriptions require quite a bit of interpretation and code provided could
be off the mark (based on incorrect intepretations).

This is what I understand you to say:
Assuming the value in Textbox1 only occurs in a single cell in Column C and
you want to find the value in column A of that row:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim lastcell As Long
Dim rng as Range
Dim rng1 as Range
lastcell = Worksheets("InspectionData") _
.Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
set rng1 = .Range("C1:C" & lastrow)
End with
set rng = rng1.Find( _
what:=Textbox1.Value, _
After:=rng1(rng1.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
Label7.Caption = rng.offset(0,-2)
else
Label7.Caption = "Not Found"
end if
Application.ScreenUpdating = True
End Sub

or same assumptions about the value in C being unique, then
If your data is like this

A C
ID1
3
6
9
1


and you search for 1 in C and want to put ID1 in Label 7

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim lastcell As Long
Dim rng as Range
Dim rng1 as Range
lastcell = Worksheets("InspectionData") _
.Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
set rng1 = .Range("C1:C" & lastrow)
End with
set rng = rng1.Find( _
what:=Textbox1.Value, _
After:=rng1(rng1.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
if not Isempty(rng.offset(0,-2)) then
Label7.Caption = rng.offset(0,-2).Value
else
label7.Caption = rng.offset(0,-2).End(xlup).Value
end if
else
Label7.Caption = "Not Found"
end if
Application.ScreenUpdating = True
End Sub


You spoke about listbox3, but it has never appeared in your code, so I have
no idea how that figures in.
 
C

Corey

Tom,
I will have a better look at your post, but in the meantime,
Listbox3 is a list populated where the value (>=) to the Textbox1 value is chosen from.

ALSO,
There CAN be (and is) sometimes the SAME value in Column C in different Column A ID's
Ie.

A C
ID2
3
6
9 <== Same as in previous ID
1

ID2 C

10
4
9<= Same
8
2
4



Corey....


First, you should make a copy of your workbook and test any code I post in
that copy so your original code is not disturbed. This should be your
normal practice for any help you get from anyone. Frankly, your
descriptions require quite a bit of interpretation and code provided could
be off the mark (based on incorrect intepretations).

This is what I understand you to say:
Assuming the value in Textbox1 only occurs in a single cell in Column C and
you want to find the value in column A of that row:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim lastcell As Long
Dim rng as Range
Dim rng1 as Range
lastcell = Worksheets("InspectionData") _
.Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
set rng1 = .Range("C1:C" & lastrow)
End with
set rng = rng1.Find( _
what:=Textbox1.Value, _
After:=rng1(rng1.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
Label7.Caption = rng.offset(0,-2)
else
Label7.Caption = "Not Found"
end if
Application.ScreenUpdating = True
End Sub

or same assumptions about the value in C being unique, then
If your data is like this

A C
ID1
3
6
9
1


and you search for 1 in C and want to put ID1 in Label 7

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim lastcell As Long
Dim rng as Range
Dim rng1 as Range
lastcell = Worksheets("InspectionData") _
.Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
set rng1 = .Range("C1:C" & lastrow)
End with
set rng = rng1.Find( _
what:=Textbox1.Value, _
After:=rng1(rng1.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
if not Isempty(rng.offset(0,-2)) then
Label7.Caption = rng.offset(0,-2).Value
else
label7.Caption = rng.offset(0,-2).End(xlup).Value
end if
else
Label7.Caption = "Not Found"
end if
Application.ScreenUpdating = True
End Sub


You spoke about listbox3, but it has never appeared in your code, so I have
no idea how that figures in.
 
C

Corey

Not really sure if this will make things easier or more difficult to solve,
but the following code is what populates Listbox3.
It When selected it may be easier to get Label7 to display the Column A value in this code(??) than
by pressing a CommandButton?
I do not know.

Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ListBox3.Clear
If TextBox1.Value <> "" Then
Dim lastcell As Long
Dim myrow As Long
Dim i As Long
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 1 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Value = ListBox1.Value Then
If .Cells(myrow, 1).Offset(-1, 6).Value = ListBox2.Value Then
For i = 2 To 22
If .Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = False Then
If Cells(myrow, 3).Offset(i, 0).Value <> "" Then
If IsNumeric(.Cells(myrow, 3).Offset(i, 0)) Then
If .Cells(myrow, 3).Offset(i, 0).Value <> "" And .Cells(myrow, 3).Offset(i, 0).Value >=
Val(TextBox1.Value) Then
ListBox3.AddItem Cells(myrow, 3).Offset(i, 0)
ListBox3.List(ListBox3.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address

End If
End If
End If
End If
Next i
End If
End If
End If
Next
End With
End If
Label8 = ListBox3.ListCount
Application.ScreenUpdating = True
End Sub


Corey....
 
T

Tom Ogilvy

It looks like you find each non-empty cell in column A (myrow)

then you look to the previous row (myrow - 1) and see if the cell in column
B is equal to the value in Listbox1 and the cell in column F is equal to the
value in Listbox2

if so, then you loop from myrow + 2 to myrow + 22 and if a cell in column
3 is numeric, does not have a strike through font and is greater than or
equal to the value in the textbox, it is added to listbox3.

for your command button to work, you would have to find that same reference
row (myrow) and work from there. It doesn't do that now - it loops through
all the data in column C - and since you say data values can have duplicates,
it would not restrict itself to the items in Listbox3. I think the easiest
would be to add a hidden column in listbox3 that records what ever
information you want to show in label7 and when the selection is made in
listbox3, just extract that from the list. Or you could use a hidden column
to record the row number of the elements in listbox3.

If you need help with that, then I would probably need you to send me the
workbook.

(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why does the line of code NOT work? 2
It was working but now not 0
how to change For i - to refer to specific columns instead of rows 6
>= Greater than or Equal to Not working 2
VBA Coding Help for Beginner 0
Problem with Chnaging fonts to Strikethrough 1
Textbox to display Listbox 2nd column value 3
Do i need a Next or Loop statement ? 1

Top