Max Value in a set of data

[Steph]

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.

[Anna]

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.

[Steph]

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.

[Anna]

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.

[Anna]

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.

[Steph]

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.

[Steph]

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.

[Anna]

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.

[Steph]

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.

[Anna]

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.