using variables to remember cells for later reference

[Joe2114]

Hi, this is my first post, so here we go...
for my Statistics coursework, I am required to find info about a variety of people from a large table of data in excel. I have crafted a series of HLOOKUP formula which return all the relevant data from entry of a randomly generated reference number. the issue comes when I need to copy the results from the formula to a separate table. I am currently doing this manually with the paste special - values function but for 35 different numbers, this is taking an age.I have created a Macro to do this for me, but I am having troubles getting VB to return to the appropriate cell to paste the data again. here is my code. note this is my FIRS EVER VB code, or code of any type for that matter, and it is compiled from the internet's worth of utterly useless articles I have been able to find (can it seriously be that I am the only one who wants to do this? someone must have come before...) anyway, here goes...

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 28/01/2010 by Joe
'
' Keyboard Shortcut: Ctrl+l
'
    Dim StartCol As Integer
    Dim StartRow As Integer
    StartRow = ActiveRow
    StartCol = ActiveColumn
    Selection.Copy
    Range("AF13").Select
    ActiveSheet.Paste
    Range("AG13", "AL13").Select
    Selection.Copy
    Cells(StartRow, StartCol + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

for further clarification, I will post a screenshot showing what I want done. apparently, it is the line "Cells(StartRow, StartCol + 1).Select" that seems to be the problem, but as I said, I have NO experience of VB so I'm not sure how to fix the problem, which, according to Excel, is "Application- or User-defined" and I have no Idea what that means. I need this fixed swiftly so I can meet my deadline.
anyway, thanks in advance, Bye.

 

[SkipVought]

He,

Well yout jpg is virtually useless. There is nothing to compare what you EXPLAINED aboev and what you DISPLAYED.

You did not explain WHICH cell was selected and what the state of your sheet was when you pasted. And I can only GUESS rhat row is 13 and what columns are AG:AL.

Furthermore, if I were a betting man, I'd wager some significant bread, that there's a much better way to accomplish your requirement.

But here's shot at the VBA...

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 28/01/2010 by Joe
'
' Keyboard Shortcut: Ctrl+l
'
    Selection.Copy
    Range("AF13").end(xltoright).Offset(0,1).PasteSpecial xlvalues
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Joe2114]

sorry for the awful explanation, but like I said, that was my first post and I was kinda panicking. anyway, unfortunately that didn't quite work as expected. the aim was to be able to:
- select Any cell with a number,
- have that number be copied to cell AF13,
- then the formulas do their thing and generate results in cells AG13 to AL13.
- I then copy these results and paste special them in the cell one cell to the right of the original cell, (not one cell to the right of the formulae, as happened with that code you provided)
I hope this explanation serves better than my old one, thanks.

 

[SkipVought]

So why don't you want formulas in the columns adjacent to all your values? That would avoid all that coly 'n' paste activity.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Joe2114]

Oh yeah... I only thought of that before I started putting $ in my formulas... I suppose it would work now... Thanks for pointing out the obvious

 

[SkipVought]

Do rows get added to your table?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Joe2114]

what, do I get more data? no, not for this project.