macros

[phil2006]

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

 

[Guest]

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.

 

[phil2006]

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

 

[Guest]

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/excel.htm#tutorials
vba tutorials listed after the excel tutorials.

 

[Dean]

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