VBA Virtual Loop

[Peggy2015]

Hello,

How do I write code to move on to another question if the information doesn't exist on my spreadsheet? My code is:

'Select Start of range
Range("B1").Select

While ActiveCell.Value <> ""
'This writes 'Cash' in column A against cash transactions.
If ActiveCell.Value <> "" Then ActiveCell.Offset(0, -1).Value = "Cash"
ActiveCell.Offset(1, 0).Select
Wend

'Select Start of range
Columns("B:B").Select

Selection.Find(What:="Cheque", After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select

While ActiveCell.Value <> ""

'This writes 'Cheque' in column A against cheque transactions.
If ActiveCell.Value <> "" Then ActiveCell.Offset(0, -1).Value = "Cheque"
ActiveCell.Offset(1, 0).Select
Wend

'Select Start of range
Columns("B:B").Select

Selection.Find(What:="EFT", After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select

While ActiveCell.Value <> ""

'This writes 'EFT' in column A against EFT transactions.
If ActiveCell.Value <> "" Then ActiveCell.Offset(0, -1).Value = "EFT"
ActiveCell.Offset(1, 0).Select

Wend


The highlighted area in blue above comes back with a run-time error '91': Object variable or With block variable not set.


I hope you can help me move on to my next search criteria. There will be a number of times when Cheques will not exist on the report and maybe EFT from time to time, although rare.

Best wishes,
Peggy
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cash[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cash[/TD]
[TD]Stationery[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]Cards[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]EFT[/TD]
[TD]EFT[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EFT[/TD]
[TD]Cards[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]EFT[/TD]
[TD]Calendars[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]EFT[/TD]
[TD]Pencils[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Maybe (untested)...
Btw in your table you have EFT against lines which don't have EFT in column B

[color=darkblue]Sub[/color] findit()
    [color=darkblue]Dim[/color] C [color=darkblue]As[/color] Range, firstAddress [color=darkblue]As[/color] [color=darkblue]String[/color]

    [color=darkblue]With[/color] Columns("B:B")
        [color=darkblue]Set[/color] C = .Find(What:="Cheque", After:=.Cells(Rows.Count), LookIn:=xlFormulas, _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False)

        [color=darkblue]If[/color] [color=darkblue]Not[/color] C [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            firstAddress = C.Address
            [color=darkblue]Do[/color]
                C.Offset(0, -1).Value = "Cheque"
                [color=darkblue]Set[/color] C = .FindNext(C)
            [color=darkblue]Loop[/color] [color=darkblue]While[/color] [color=darkblue]Not[/color] C [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] And C.Address <> firstAddress
        [color=darkblue]End[/color] [color=darkblue]If[/color]

        [color=darkblue]Set[/color] C = .Find(What:="EFT", After:=.Cells(Rows.Count), LookIn:=xlFormulas, _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False)

        [color=darkblue]If[/color] [color=darkblue]Not[/color] C [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            firstAddress = C.Address
            [color=darkblue]Do[/color]
                C.Offset(0, -1).Value = "EFT"
                [color=darkblue]Set[/color] C = .FindNext(C)
            [color=darkblue]Loop[/color] [color=darkblue]While[/color] [color=darkblue]Not[/color] C [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] And C.Address <> firstAddress
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[James_Latimer]

.

 

[Peggy2015]

.

Hello James,
Did something happen to your message? It seems to have disappeared?

 

[dmt32]

Hi,
another way maybe?

Option Base 1


Sub Search()
    Dim rng As Range, cell As Range
    Dim m As Variant, SearchItem As Variant
    
    SearchItem = Array("Cheque", "EFT")
    Set rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    
    For Each cell In rng.Cells
        m = Application.Match(cell.Value, SearchItem, False)
        With cell.Offset(, -1)
            If Not IsError(m) Then If Len(.Value) = 0 Then .Value = SearchItem(m)
        End With
    Next cell
End Sub

note:
-Option Base 1 statement which MUST sit at very TOP of the code page OUTSIDE any procedure.
- ranges are unqualified and it is assumed the correct sheet is the activesheet when code run.

Hope Helpful

Dave