VBA to find value

[dougkale]

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:

Some number

T

<tbody>
</tbody>

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?

 

[Anthony47]

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

 

[dougkale]

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.

 

[TRADER083]

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:

Some number

T

<tbody>
</tbody>

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,

 

[dougkale]

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.

 

[TRADER083]

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.

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

 

[JackDanIce]

You can use also .Find, as another suggestion, try:

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

 

[Rick Rothstein]

Give this a try...

[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]

 

[dougkale]

Thank you to all that replied. I am new to excel VBA and this board has proven an amazing resource!

 

[JackDanIce]

You're welcome