Find text and show value if condition is met

by

I’ve got an answer that will find the first instance of USD and check if TOTAL PURCHASE is 9 rows below it, if found then copy the adjacent cell to D1, if not found then search for the next USD and repeat the checks:

Option Explicit

Sub vbavjezba()
Dim total As Variant
Dim usd As Variant
Dim rng As Range

Set rng = Worksheets(1).Range("A1:A21")
Set usd = Worksheets(1).Range("A1:A21").Find(what:="USD") 'Find the first instance of USD

repeatcheck:
If usd.Offset(9, 0).Value = "TOTAL PURCHASE" Then
    Worksheets(1).Range("D1").Value = usd.Offset(9, 1).Value
Else
    Set usd = rng.FindNext(After:=usd) 'Find the next instance of USD
    If Not usd Is Nothing Then 'IF found
        GoTo repeatcheck 'go back to repeatcheck to see if TOTAL PURCHASE is 9 rows below it
    Else
        MsgBox "Not Found!"
    End If
End If
End Sub

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. Excel check len string separated by comma [closed]
  4. How can I insert variable into formula in VBA
  5. Wait until ActiveWorkbook.RefreshAll finishes – VBA
  6. Code in VBA loops and never ends. How to fix this?
  7. Excel column number from column name
  8. Pop up the Excel Statusbar?
  9. VBA – destroy a modeless UserForm instance properly
  10. Execute a command in command prompt using excel VBA
  11. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  12. Copy data from another Workbook through VBA
  13. Excel VBA Load worksheet in Function
  14. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  15. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  16. Load csv file into a VBA array rather than Excel Sheet
  17. Insert line break in wrapped cell via code
  18. Change color of certain characters in a cell
  19. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  20. VBA Macro crashes after 32000 rows
  21. How to create dynamic variable names VBA
  22. How to break a long string into multiple lines
  23. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  24. simple vba code gives me run time error 91 object variable or with block not set
  25. Calling regasm without administrative rights for COM interop in Excel VBA
  26. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  27. How do I activate a specific workbook and a specific sheet?
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment