VBA Virtual Loop

Peggy2015

Board Regular
Joined
Oct 19, 2015
Messages
109
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
AB
1CashCash
2CashStationery
3CashCards
4
7EFTEFT
8EFTCards
9EFTCalendars
10EFTPencils
11
12

<tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Maybe (untested)...
Btw in your table you have EFT against lines which don't have EFT in column B:confused:

Code:
[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]
 
Last edited:
Upvote 0
Hi,
another way maybe?


Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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