VBA to find value

dougkale

New Member
Joined
Sep 22, 2017
Messages
25
I am using VBA to do some clean up to a work sheet after users enter some data.

Every sheet is going to have only one instance of the following cell combination:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Some number[/TD]
[TD]T[/TD]
[/TR]
</tbody>[/TABLE]


So I want to find the value in the cell to the left of the one cell with a T in it, produce a message box bringing that value to the attention of the user if the value is not zero.

Any quick slick ideas? Tips?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Wouldn't be sufficient filtering the two columns for text=T and for value<>0?

You can record a macro that apply this filtering on the active sheet, and another that remove the filters; then assign a shortcut (like Contr-Shift-F /Contr-Shift-R) or an icon in your QuickAccessBar for easy of starting them.

Bye
 
Upvote 0
I am using the Workbook_BeforeSave event to do a whole host of validation and reformatting. If this cell is not zero I want the user to be notified before saving.
 
Upvote 0
I am using VBA to do some clean up to a work sheet after users enter some data.

Every sheet is going to have only one instance of the following cell combination:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Some number
[/TD]
[TD]T
[/TD]
[/TR]
</tbody>[/TABLE]


So I want to find the value in the cell to the left of the one cell with a T in it, produce a message box bringing that value to the attention of the user if the value is not zero.

Any quick slick ideas? Tips?

Hi,

A couple of questions:

1. How many spreadsheets (will all of them have a "T" entry)?
2. Is the "T" entry at a random location on each spreadsheet or does it only appear in certain column(s) or row(s), or can "T" even be in the first column? Knowing a general location to look for is beneficial.
3. Is the value we are accessing on the left of "T" equal to "0" or blank?

Thank you,
 
Upvote 0
I will know that the T will be in column O and the value to be examined will be in column N. But I do not know what row they will occur in. they will however be in the same row. the value will always be a number, never blank. there is only one worksheet to be considered.

Essentially users are entering a series of transaction codes and transaction amounts into a spread sheet. When they close the spread sheet I am performing some balancing (Already) for each transaction code I determine if the corresponding transactions are a debit or a credit, and I sum the transaction amounts for that transaction code and label it as a debit amount or a credit amount. I then follow those sums with a sum of the debits and credits (Again, I am already doing this) I simply want to add functionality that will warn the user if the two do not balance.
 
Upvote 0
Try the following: the code will stop searching after 500000 line in case there is no T in column O, you can remove it or adjust it according to your data.

Code:
Public Sub find_value()
Dim ws As Worksheet
Dim xlrow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
'Assuming this is the worksheet where the entries are entered.
xlrow = 1
Do
    If Trim(ws.Cells(xlrow, "O")) = "T" Then
        MsgBox "The value is: " + CStr(ws.Cells(xlrow, "N"))
        ws.Cells(xlrow, "N").Select
        Exit Do
    End If
    xlrow = xlrow + 1
    If xlrow = 500000 Then
        MsgBox "T not found."
        Exit Sub
    End If
    'Stop searching after 500000th line in case there is no T value in column O.
Loop
End Sub
 
Upvote 0
You can use also .Find, as another suggestion, try:
Code:
Public Sub M1()

    Dim LR      As Long
    Dim rng     As Range
    Dim strMsg  As String
        
    With Sheets("Sheet1")
        LR = .Cells(.Rows.count, 15).End(xlUp).row
        Set rng = .Cells(1, 15).Resize(LR).find(what:="T", LookIn:=xlValues, lookat:=xlWhole)
        strMsg = "T not found"
        If Not rng Is Nothing Then strMsg  = "The value is: " & rng.Offset(, -1).Value
        Set rng = Nothing         
    End With
    
    MsgBox strMsg , vbOKOnly, "Value " & IIf(InStr(strMsg, "not"), "Not ", "") & "Found"            
    
End Sub
 
Last edited:
Upvote 0
Give this a try...
Code:
[table="width: 500"]
[tr]
	[td]Sub Tcheck()
  Dim Tcell As Range
  Set Tcell = Columns("O").Find("T", , xlValues, xlWhole, , , False, , False)
  If Tcell.Offset <> 0 Then
    Tcell.Select
    MsgBox "Please check the selected T cell."
  End If
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top