VB Code Problem
 

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

VB Code Problem
 

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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

VB Code Problem
 

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" wrote:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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

VB Code Problem
 

You can do without macro:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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" wrote:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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

VB Code Problem
 

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

"Rodrigo Ferreira" wrote:

You can do without macro:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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" wrote:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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

VB Code Problem
 

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

--

Rodrigo Ferreira


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

"Rodrigo Ferreira" wrote:

You can do without macro:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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" wrote:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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

VB Code Problem
 

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!

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

--

Rodrigo Ferreira


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

"Rodrigo Ferreira" wrote:

You can do without macro:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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" wrote:

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

--

Rodrigo Ferreira


"Stan" escreveu na mensagem
...
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