Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
I have a list of data on sheet 2. Each time a figure occurs for a nam matching the one on sheet 1 I want this figure to be inserted ont sheet 1 as the most recent figure (i.e. shifting all cells in the ro to the right). Each name occurs multiple times on sheet two and thus need the macro to read work through the list on sheet 2 updating as i works rather than after it finishes. Is this possible? Thanks very much Phi -- phil200 ----------------------------------------------------------------------- phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509 View this thread: http://www.excelforum.com/showthread.php?threadid=54943 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
On sheet2, assume the list of names is in column 1 starting in A1. The
figure is in column B adjacent to the name The name to search for originates in A1 of sheet1 Dim sh as worksheet, nm as String Dim rng as Range, cell as Range set sh = worksheets("sheet1") nm = sh.range("A1").Value with worksheets("Sheet2") set rng = .range(.cells(1,1),.cells(1,1).end(xldown)) End with for each cell in rng if cell.value = nm sh.columns(1).Insert sh.Range("A1").Value = cell.offset(0,1).vlaue end if Next Perhaps the above will give you a start. -- Regards, Tom Ogilvy "phil2006" wrote: I have a list of data on sheet 2. Each time a figure occurs for a name matching the one on sheet 1 I want this figure to be inserted onto sheet 1 as the most recent figure (i.e. shifting all cells in the row to the right). Each name occurs multiple times on sheet two and thus I need the macro to read work through the list on sheet 2 updating as it works rather than after it finishes. Is this possible? Thanks very much Phil -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549433 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
Thanks! I still don't quite understand where I put this into tho? I this the macro? Please excuse my ignorance -- phil200 ----------------------------------------------------------------------- phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509 View this thread: http://www.excelforum.com/showthread.php?threadid=54943 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
That is the essential code of the macro. You need to name it
Sub MyMacro() Dim sh as worksheet, nm as String Dim rng as Range, cell as Range set sh = worksheets("sheet1") nm = sh.range("A1").Value with worksheets("Sheet2") set rng = .range(.cells(1,1),.cells(1,1).end(xldown)) End with for each cell in rng if cell.value = nm sh.columns(1).Insert sh.Range("A1").Value = cell.offset(0,1).vlaue end if Next End Sub You need to do Alt+F11 to get to the vbe, then do Insert =Module in the menu there and then paste the code into that module. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.mvps.org/dmcritchie/excel....htm#tutorials vba tutorials listed after the excel tutorials. -- Regards, Tom Ogilvy "phil2006" wrote: Thanks! I still don't quite understand where I put this into tho? Is this the macro? Please excuse my ignorance! -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549433 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
I think Tom posted his answer to someone else's question here.
"Tom Ogilvy" wrote in message ... That is the essential code of the macro. You need to name it Sub MyMacro() Dim sh as worksheet, nm as String Dim rng as Range, cell as Range set sh = worksheets("sheet1") nm = sh.range("A1").Value with worksheets("Sheet2") set rng = .range(.cells(1,1),.cells(1,1).end(xldown)) End with for each cell in rng if cell.value = nm sh.columns(1).Insert sh.Range("A1").Value = cell.offset(0,1).vlaue end if Next End Sub You need to do Alt+F11 to get to the vbe, then do Insert =Module in the menu there and then paste the code into that module. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.mvps.org/dmcritchie/excel....htm#tutorials vba tutorials listed after the excel tutorials. -- Regards, Tom Ogilvy "phil2006" wrote: Thanks! I still don't quite understand where I put this into tho? Is this the macro? Please excuse my ignorance! -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549433 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
Macros inside macros, and pasting into macro code. | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
Macros not appearing in the Tools Macro Macros list | Excel Programming | |||
Suppress the Disable Macros / Enable Macros Dialog | Excel Programming |