VB Code Problem

[Guest]

I have the following code and it works fine unless it can't find a row
containing the text "Open Time". I recieve the error message "Object
variable or With block variable not set. I would like to code to ignore this
section if it can't find the text "Open Time" and move onto the next section
of code. Can someone tell me how I can do this? Many thanks!


Sub FindMacro1()

Dim rng As Range
Cells(1, 1).Select
Set rng = Range("A1:IV65400").Find(what:="Open Time", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Range(rng, rng.End(xlToRight)).Cut
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Select
ActiveCell.End(xlUp).Offset(-1, 0).Select
Selection.EntireRow.Delete

 

[Rodrigo Ferreira]

try this:

Dim rng As Range
Cells(1, 1).Select
Set rng = Range("A1:IV65400").Find(what:="Open Time", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rng Is Nothing Then

Range(rng, rng.End(xlToRight)).Cut
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Select
ActiveCell.End(xlUp).Offset(-1, 0).Select
Selection.EntireRow.Delete
end if

 

[Guest]

That worked!

Many apologies but I'm new to writing code. Can you possibly tell me how to
write code that moves to a cell based upon critera? In other words, if I
find the text "Open Time" in cell A38 I would then like to move to the cell
to the immediate right in the next column so I can sum up that column which
would be cell B38.

 

[Rodrigo Ferreira]

You can do without macro:

=SUMPRODUCT(--(A1:A20="Open Time");A1:C20)

 

[Guest]

Many thanks for your response. Is it possible to convert it to code so it
will run automatically with the other code?

 

[Rodrigo Ferreira]

Range("AA" & Rows.Count - 1).Formula = "=SUMPRODUCT(--(A1:A20=""Open
Time""),B1:B20)"
SumValue = Range("AA" & Rows.Count - 1).Value
Range("AA" & Rows.Count - 1).ClearContents

SumValue = is your result

I was looking your code:

Set rng = Range("A1:IV65400").Find(what:="Open Time", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

And I have a question:
You have a loop to execute this part of code, right?
Cause if you don't have, it will find only the first "Open Time". If you
have another "Open Tmie", It won't find them

 

[Guest]

Again, many thanks for your replies!

I don't have a loop although there will only be 1 Open Time so I'm okay.
Thanks!