Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Importing Data | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |