ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Max Value in a set of data (https://www.excelbanter.com/excel-discussion-misc-queries/87397-max-value-set-data.html)

Steph
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.

Anna
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.

Steph
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.

Anna
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.

Anna
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.

Steph
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.

Steph
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.

Anna
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.

Steph
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.

Anna
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.


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com