macros

[phil2006]

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

[Tom Ogilvy]

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

[phil2006]

Thanks! I still don't quite understand where I put this into tho? Is this the macro? Please excuse my ignorance!

[Tom Ogilvy]

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

[Dean]

I think Tom posted his answer to someone else's question here.


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>