Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Steph
 
Posts: n/a
Default Max Value in a set of data

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Anna
 
Posts: n/a
Default Max Value in a set of data

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

  #3   Report Post  
Posted to microsoft.public.excel.misc
Steph
 
Posts: n/a
Default Max Value in a set of data

I am sorry, but i am a novice at macros. When i cut in paste into VBA, the
following remains in red and I do not know where the problem lies.


Cells.Find(What:=Range("f2").Value, (After:=ActiveCell), (LookIn:=xlValues),
(LookAt:= _xlPart), (SearchOrder:=xlByRows), (SearchDirection:=xlNext),
(MatchCase:=False))


"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Anna
 
Posts: n/a
Default Max Value in a set of data

How about copy below:

Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

Columns("A:A").Select
Selection.Find(What:=Range("f2").Value, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub







"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

  #5   Report Post  
Posted to microsoft.public.excel.misc
Anna
 
Posts: n/a
Default Max Value in a set of data

Hope this is last coding to be copied:

Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

Range("a1").Select

Do
Do While ActiveCell.Value < Range("f2").Value
If Range("a1").Value = Range("f2").Value Then 'if the name code is
different with the next one
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = Range("f2").Value


ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub

Cheers.
Anna


"Steph" wrote:

I am sorry, but i am a novice at macros. When i cut in paste into VBA, the
following remains in red and I do not know where the problem lies.


Cells.Find(What:=Range("f2").Value, (After:=ActiveCell), (LookIn:=xlValues),
(LookAt:= _xlPart), (SearchOrder:=xlByRows), (SearchDirection:=xlNext),
(MatchCase:=False))


"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.



  #6   Report Post  
Posted to microsoft.public.excel.misc
Steph
 
Posts: n/a
Default Max Value in a set of data

This worked excellent. Thank You so much! If I wanted to search for a
specific number rather than the max value could i just change this line:

answer = Application.WorksheetFunction.Max(myRange)? Say i was looking for
500....

"Anna" wrote:

Hope this is last coding to be copied:

Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

Range("a1").Select

Do
Do While ActiveCell.Value < Range("f2").Value
If Range("a1").Value = Range("f2").Value Then 'if the name code is
different with the next one
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = Range("f2").Value


ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub

Cheers.
Anna


"Steph" wrote:

I am sorry, but i am a novice at macros. When i cut in paste into VBA, the
following remains in red and I do not know where the problem lies.


Cells.Find(What:=Range("f2").Value, (After:=ActiveCell), (LookIn:=xlValues),
(LookAt:= _xlPart), (SearchOrder:=xlByRows), (SearchDirection:=xlNext),
(MatchCase:=False))


"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

  #7   Report Post  
Posted to microsoft.public.excel.misc
Steph
 
Posts: n/a
Default Max Value in a set of data

One other question...if I wanted to do this manny times. Look for 400 store
here look for 500 store here, would I use the same method?

"Anna" wrote:

How about copy below:

Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

Columns("A:A").Select
Selection.Find(What:=Range("f2").Value, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub







"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

  #8   Report Post  
Posted to microsoft.public.excel.misc
Anna
 
Posts: n/a
Default Max Value in a set of data

Sorry I do not understand your question exactly. Below is the example that
find the first value (i.e. 500) in column A from row 1.

Sub macro()
Range("a1").Select

Do
Do While ActiveCell.Value < "500"
If Range("a1").Value = "500" Then
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = "500"

End Sub



"Steph" wrote:

This worked excellent. Thank You so much! If I wanted to search for a
specific number rather than the max value could i just change this line:

answer = Application.WorksheetFunction.Max(myRange)? Say i was looking for
500....

"Anna" wrote:

Hope this is last coding to be copied:

Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

Range("a1").Select

Do
Do While ActiveCell.Value < Range("f2").Value
If Range("a1").Value = Range("f2").Value Then 'if the name code is
different with the next one
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = Range("f2").Value


ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub

Cheers.
Anna


"Steph" wrote:

I am sorry, but i am a novice at macros. When i cut in paste into VBA, the
following remains in red and I do not know where the problem lies.


Cells.Find(What:=Range("f2").Value, (After:=ActiveCell), (LookIn:=xlValues),
(LookAt:= _xlPart), (SearchOrder:=xlByRows), (SearchDirection:=xlNext),
(MatchCase:=False))


"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

  #9   Report Post  
Posted to microsoft.public.excel.misc
Steph
 
Posts: n/a
Default Max Value in a set of data

Sorry for the confusion. Here is what I actually have...

I want to write a macro to do the following:
Filter data from sheet 1 and move each specific set of data(equipment 1,2,3
etc all contained in sheet 1) to sheet 2. 3. 4 on down to sheet 8. For
instance I want to filter the time and temperature values corrosponding to a
certain set of equipment. Equipment 1, time and temperature, will be placed
on sheet 2. Equipment 2, time and temperature, will be placed on sheet 3 and
so on. Sheet one conatins all the data. I want to find it and place it
separtely.

(My macro can already do this.)

I now want to go through each of the eight sheets, find the max temperature
value in column d, move ten cells up and then plot everything from the ten
cells up and the rest following.
(My macro can plot)

What i can't figure out is how to write a loop that can go through each
sheet and do this. How can I make my macro do this.

Again thank you for the help. Writing Macros is a new endeavour for me.

"Anna" wrote:

Sorry I do not understand your question exactly. Below is the example that
find the first value (i.e. 500) in column A from row 1.

Sub macro()
Range("a1").Select

Do
Do While ActiveCell.Value < "500"
If Range("a1").Value = "500" Then
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = "500"

End Sub



"Steph" wrote:

This worked excellent. Thank You so much! If I wanted to search for a
specific number rather than the max value could i just change this line:

answer = Application.WorksheetFunction.Max(myRange)? Say i was looking for
500....

"Anna" wrote:

Hope this is last coding to be copied:

Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

Range("a1").Select

Do
Do While ActiveCell.Value < Range("f2").Value
If Range("a1").Value = Range("f2").Value Then 'if the name code is
different with the next one
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = Range("f2").Value


ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub

Cheers.
Anna


"Steph" wrote:

I am sorry, but i am a novice at macros. When i cut in paste into VBA, the
following remains in red and I do not know where the problem lies.


Cells.Find(What:=Range("f2").Value, (After:=ActiveCell), (LookIn:=xlValues),
(LookAt:= _xlPart), (SearchOrder:=xlByRows), (SearchDirection:=xlNext),
(MatchCase:=False))


"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

  #10   Report Post  
Posted to microsoft.public.excel.misc
Anna
 
Posts: n/a
Default Max Value in a set of data

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double
Dim rng As Range

For Each wks In Worksheets
answer = Application.Max(wks.Range("d1:d2000"))
If answer totalAnswer Then
totalAnswer = answer
Set rng = Application.Index(wks.Range("d1:d2000"), _
Application.Match(answer, wks.Range("d1:d2000"), 0))
End If
Next wks

Application.Goto rng, True

End Sub

'answered by Tom Ogilvy


"Steph" wrote:

Sorry for the confusion. Here is what I actually have...

I want to write a macro to do the following:
Filter data from sheet 1 and move each specific set of data(equipment 1,2,3
etc all contained in sheet 1) to sheet 2. 3. 4 on down to sheet 8. For
instance I want to filter the time and temperature values corrosponding to a
certain set of equipment. Equipment 1, time and temperature, will be placed
on sheet 2. Equipment 2, time and temperature, will be placed on sheet 3 and
so on. Sheet one conatins all the data. I want to find it and place it
separtely.

(My macro can already do this.)

I now want to go through each of the eight sheets, find the max temperature
value in column d, move ten cells up and then plot everything from the ten
cells up and the rest following.
(My macro can plot)

What i can't figure out is how to write a loop that can go through each
sheet and do this. How can I make my macro do this.

Again thank you for the help. Writing Macros is a new endeavour for me.

"Anna" wrote:

Sorry I do not understand your question exactly. Below is the example that
find the first value (i.e. 500) in column A from row 1.

Sub macro()
Range("a1").Select

Do
Do While ActiveCell.Value < "500"
If Range("a1").Value = "500" Then
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = "500"

End Sub



"Steph" wrote:

This worked excellent. Thank You so much! If I wanted to search for a
specific number rather than the max value could i just change this line:

answer = Application.WorksheetFunction.Max(myRange)? Say i was looking for
500....

"Anna" wrote:

Hope this is last coding to be copied:

Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

Range("a1").Select

Do
Do While ActiveCell.Value < Range("f2").Value
If Range("a1").Value = Range("f2").Value Then 'if the name code is
different with the next one
ActiveCell.Select
Else
ActiveCell.Offset(1, 0).Select

Exit Do
End If
Loop

Loop Until ActiveCell.Value = Range("f2").Value


ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub

Cheers.
Anna


"Steph" wrote:

I am sorry, but i am a novice at macros. When i cut in paste into VBA, the
following remains in red and I do not know where the problem lies.


Cells.Find(What:=Range("f2").Value, (After:=ActiveCell), (LookIn:=xlValues),
(LookAt:= _xlPart), (SearchOrder:=xlByRows), (SearchDirection:=xlNext),
(MatchCase:=False))


"Anna" wrote:

You may copy below into vb editor and test.


Sub macro()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:d2000")
answer = Application.WorksheetFunction.Max(myRange)
Range("f2").Select
ActiveCell.Value = answer

With Worksheets(1).Range("a1:a2000")
Cells.Find(What:=Range("f2").Value, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End With

ActiveCell.Offset(0, 3).Select
Selection.Copy
Range("g2").Select
ActiveSheet.Paste

End Sub



"Steph" wrote:

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

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
Pull data from another sheet based on certain criteria steve_sr2 Excel Discussion (Misc queries) 1 February 23rd 06 10:08 AM
Importing Data Jillian Excel Worksheet Functions 9 December 23rd 05 12:45 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Saving data in a worksheet within a workbook Homeuser Excel Discussion (Misc queries) 2 August 21st 05 10:49 PM
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM


All times are GMT +1. The time now is 10:07 AM.

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

About Us

"It's about Microsoft Excel"