I'm a newbie to visual basic and an accountant so I'm sorry if this question is too simple. I seem to be strugling with the find and find next structure.
I'm attempting to find a general ledger code and place another code next to it, obtained from a lookup table. For example, 72001... I'm trying to have the system return that range, then move one cell to the left and place that number from another cell - let's say 240. The end result would be 240 on the left cell (pulled from the table on another cell) and 72001 on the right cell.
I'm trying to simplify the problem by creating a smaller chunk of code in a sub and am still struggling to understand the concept of find/findnext even at a basic level. At this point I'm just testing the function and it's still not doing what I want; I don't understand why that is.
Sub abc()
Dim A As Range
Do While Not A Is Nothing
Set A = Range("B1:B100").Find(what:="A")
MsgBox (A)
Loop
End Sub
Shouldn't the code keep looping while A is not a null data set? I placed a bunch of "A'"s on the B column, yet I still don't get any message box. Why is nothing happening? I even tried to take the "Not" out, yet nothing (no pun) still happens. I don't understand.
I've tried the above code to no avail and the following (below). For the above, I don't get an error, unless I take the "not" out, which I don't understand. I tried the below as well and get a frustrating "invalid use of object error." I'm simply trying to replace the A characters with the find function.
Sub abc()
Dim A As Range
Dim B As Range
Set A = Range("B1:B100").Find(what:="A", after:="A1")
If A Is Not Nothing Then A.Value = Z
Else
Do Until Not B Is Nothing
Set B = Range("B:B100").Find(what:="A", after:="A1")
MsgBox ("A")
Loop
End Sub
When you write Dim A As Range it actually means
Dim A As Range: Set A = Nothing
So when you continue with Do While Not A Is Nothing ('do while A is something'), for obvious reasons, the loop is never entered (since A is nothing).
Study and play around with the following.
Also, study the documentation in detail since there is much more to the Find method than I am sharing here.
Sub abc()
Const SearchString As String = "A"
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
Dim rg As Range: Set rg = ws.Range("B1:B100")
' The following will attempt to find a cell equal to "A".
' It will start looking in cell `B1`, after the supplied last cell 'B100'
' since the default parameter of 'SearchDirection' is 'xlNext'.
' It will find "a" and "A" since the default parameter of 'MatchCase'
' is 'False'.
Dim cell As Range: Set cell = rg.Find(What:=SearchString, _
After:=rg.Cells(rg.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole)
Dim FirstAddress As String
If Not cell Is Nothing Then
' Store the address of the first found cell in a variable.
FirstAddress = cell.Address
Do
' It is advisable when developing a loop, to use
' 'DoEvents' so you can exit an endless loop with 'Esc'
' or 'Pause/Break'. When done, out-comment it.
'DoEvents
' Do something with the found cell.
MsgBox "Found """ & cell.Value & """ in cell """ _
& cell.Address(0, 0) & """.", vbInformation
' Find the next cell equal to "A". Note how you are supplying
' the current cell as the parameter for the 'After' argument
' so it will start by looking into the next cell.
Set cell = rg.FindNext(After:=cell)
' Since 'Find' or 'FindNext' will try to find 'forever',
' from 'B1' to 'B100', and again, and again, you need to compare
' the address of the newly found cell with the address of the first
' found cell to be able to exit the loop once it got back
' to the first found cell.
Loop While cell.Address <> FirstAddress
'Loop Until cell.Address = FirstAddress
Else
MsgBox "No """ & SearchString & """ was found.", vbExclamation
End If
End Sub