Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 112
Default Any way to pretty this up?

I am trying to copy data from "Engine Data" and paste it into a new sheet.
Engine Data contains many columns of FG_HC... I only want the one under the
[Hertz] heading. The format of the Engine Data looks like the following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC", RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,124
Default Any way to pretty this up?

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Matt S" wrote in message
...
I am trying to copy data from "Engine Data" and paste it into a new sheet.
Engine Data contains many columns of FG_HC... I only want the one under
the
[Hertz] heading. The format of the Engine Data looks like the following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC", RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Any way to pretty this up?

maybe something like this, but you don't give enough information to actually
complete the code, (like where you discern the lastrow from). but have a look
and maybe you can complete it on your own:


Option Explicit
Sub test()
Dim arr As Variant
Dim rngfound As Range
Dim itmfound As Range
Dim i As Long
Dim lastrow As Long
arr = Array("FG_HC", "CO", "HC", "NO")

With Sheets("Engine Data")
Set rngfound = .Cells.Find(What:="[Hertz]", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, _
SearchFormat:=False)

If Not rngfound Is Nothing Then
For i = LBound(arr) To UBound(arr)
With .Range(rngfound.Address,
..Range(rngfound.Address).End(xlToRight))

Set itmfound = .Find(What:=arr(i),
After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate
End With
.Range(rngfound.Address,
..Range(rngfound.Address).End(xlToRight)).Copy
Sheets("MFCs").Range("F10").PasteSpecial xlPasteAll
ActiveWorkbook.Names.Add Name:="FG_HC",
RefersToR1C1:="=MFCs!R13C6:R" & _
lastrow & "C"
Next
End If
End With
End Sub
--


Gary Keramidas
Excel 2003


"Matt S" wrote in message
...
I am trying to copy data from "Engine Data" and paste it into a new sheet.
Engine Data contains many columns of FG_HC... I only want the one under the
[Hertz] heading. The format of the Engine Data looks like the following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC", RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 112
Default Any way to pretty this up?

Don and Gary thanks so much. I probably gave more information than needed
and also made the post too broad. I was more looking for a syntax cleaning.
This is what I have so far in my attempts to clean it up... I'm stuck on the
last part where I define the final pasted range as a name. Don I will not be
able to send you the file. It's got too much classified information in it.
Here is what I have so far:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]").Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Find
What:="FG_HC").Activate
Range(Selection, Selection.End(xlDown)).Copy
Destination:=Sheets("MFCs").Range("F10")

'It works up to this point perfectly... then the following line doesn't work
out. I'm trying to get rid of the LastRow reference.

ActiveWorkbook.Names.Add Name:="FG_HC",
RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?

Thanks,
Matt

"Don Guillett" wrote:

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Matt S" wrote in message
...
I am trying to copy data from "Engine Data" and paste it into a new sheet.
Engine Data contains many columns of FG_HC... I only want the one under
the
[Hertz] heading. The format of the Engine Data looks like the following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC", RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt


.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 112
Default Any way to pretty this up?

ok, I tried one more thing and this seemed to work. Is there a way to make
it less condensed? For example... how come the following doesn't work?
Sheets("Engine Data").Cells.Find(What:="[Hertz]").Activate

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]").Activate
ActiveCell.Range("A2",
Range("A2").End(xlToRight)).Find(What:="FG_HC").Ac tivate
Range(Selection, Selection.End(xlDown)).Copy
Destination:=Sheets("MFCs").Range("F10")
ActiveWorkbook.Names.Add Name:="FG_HC",
RefersTo:=Sheets("MFCs").Range("F10:F" & Sheets("MFCs").Cells(Rows.Count,
"E").End(xlUp).Row)


Thanks again!
Matt


"Gary Keramidas" wrote:

maybe something like this, but you don't give enough information to actually
complete the code, (like where you discern the lastrow from). but have a look
and maybe you can complete it on your own:


Option Explicit
Sub test()
Dim arr As Variant
Dim rngfound As Range
Dim itmfound As Range
Dim i As Long
Dim lastrow As Long
arr = Array("FG_HC", "CO", "HC", "NO")

With Sheets("Engine Data")
Set rngfound = .Cells.Find(What:="[Hertz]", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, _
SearchFormat:=False)

If Not rngfound Is Nothing Then
For i = LBound(arr) To UBound(arr)
With .Range(rngfound.Address,
..Range(rngfound.Address).End(xlToRight))

Set itmfound = .Find(What:=arr(i),
After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate
End With
.Range(rngfound.Address,
..Range(rngfound.Address).End(xlToRight)).Copy
Sheets("MFCs").Range("F10").PasteSpecial xlPasteAll
ActiveWorkbook.Names.Add Name:="FG_HC",
RefersToR1C1:="=MFCs!R13C6:R" & _
lastrow & "C"
Next
End If
End With
End Sub
--


Gary Keramidas
Excel 2003


"Matt S" wrote in message
...
I am trying to copy data from "Engine Data" and paste it into a new sheet.
Engine Data contains many columns of FG_HC... I only want the one under the
[Hertz] heading. The format of the Engine Data looks like the following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC", RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt


.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,565
Default Any way to pretty this up?

RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?


RefersTo:=Sheets("MFCs").ActiveCell.Address



"Matt S" wrote in message
...
Don and Gary thanks so much. I probably gave more information than needed
and also made the post too broad. I was more looking for a syntax
cleaning.
This is what I have so far in my attempts to clean it up... I'm stuck on
the
last part where I define the final pasted range as a name. Don I will not
be
able to send you the file. It's got too much classified information in
it.
Here is what I have so far:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]").Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Find
What:="FG_HC").Activate
Range(Selection, Selection.End(xlDown)).Copy
Destination:=Sheets("MFCs").Range("F10")

'It works up to this point perfectly... then the following line doesn't
work
out. I'm trying to get rid of the LastRow reference.

ActiveWorkbook.Names.Add Name:="FG_HC",
RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?

Thanks,
Matt

"Don Guillett" wrote:

If desired, send your file to my address below. I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Matt S" wrote in message
...
I am trying to copy data from "Engine Data" and paste it into a new
sheet.
Engine Data contains many columns of FG_HC... I only want the one under
the
[Hertz] heading. The format of the Engine Data looks like the
following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC",
RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG
species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt


.



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 63
Default Any way to pretty this up?

That is your code creating a named range.


On Sun, 3 Jan 2010 15:41:43 -0500, "JLGWhiz" wrote:

RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?


RefersTo:=Sheets("MFCs").ActiveCell.Address



"Matt S" wrote in message
...
Don and Gary thanks so much. I probably gave more information than needed
and also made the post too broad. I was more looking for a syntax
cleaning.
This is what I have so far in my attempts to clean it up... I'm stuck on
the
last part where I define the final pasted range as a name. Don I will not
be
able to send you the file. It's got too much classified information in
it.
Here is what I have so far:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]").Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Find
What:="FG_HC").Activate
Range(Selection, Selection.End(xlDown)).Copy
Destination:=Sheets("MFCs").Range("F10")

'It works up to this point perfectly... then the following line doesn't
work
out. I'm trying to get rid of the LastRow reference.

ActiveWorkbook.Names.Add Name:="FG_HC",
RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?

Thanks,
Matt

"Don Guillett" wrote:

If desired, send your file to my address below. I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Matt S" wrote in message
...
I am trying to copy data from "Engine Data" and paste it into a new
sheet.
Engine Data contains many columns of FG_HC... I only want the one under
the
[Hertz] heading. The format of the Engine Data looks like the
following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC",
RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG
species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt

.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Pretty easy If loop - help!! nemadrias Excel Worksheet Functions 9 July 7th 06 12:13 AM
Pretty Buttons... SamuelT Excel Discussion (Misc queries) 5 May 25th 06 04:25 PM
I think this should be pretty simple... Levi Excel Discussion (Misc queries) 2 May 9th 06 11:41 PM
Went to your site pretty cool! Marc New Users to Excel 0 December 2nd 04 04:51 AM
Pretty basic VBA help....if you know how!! Big Chris[_10_] Excel Programming 1 October 22nd 03 12:56 AM


All times are GMT +1. The time now is 03:35 PM.

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"