Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > VB Code Problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 150
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 79
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 150
Default 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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 79
Default 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






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 150
Default 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







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 79
Default 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









Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 150
Default 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










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
XLS to CSV Code Problem carl Excel Worksheet Functions 0 March 28th 07 01:21 AM
Strange problem with Code PH NEWS Excel Worksheet Functions 1 September 27th 06 10:16 AM
Little problem with this code... simonsmith Excel Discussion (Misc queries) 11 May 21st 06 04:02 AM
Can anyone figure this code problem please simonsmith Excel Discussion (Misc queries) 1 May 18th 06 08:20 PM
Problem with criteria when using it from VBA Code Alvaro Silva Excel Worksheet Functions 0 December 15th 05 12:25 AM


All times are GMT +1. The time now is 03:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement