Copying row onto the bottom of a list

[RobShaw]

Hi

I have created a tool at work which looks up certain products via combo box and refines the selections in a second combo to the ones that apply to that model. For example: All of the models are photocopiers basically, so I select a particular photo copier and the the optional accessories available for it appear in the drop down list of the second combo box.

That is all sorted, I then do a series of vlookup's to fetch the pricing informaiton and description etc, and this forms a nice neat row at the top of my sheet.

I then have a button called Add that copies the fetched row into a list. Currently it does this by inserting a new row (always at the same place and copying the fetched row above into the newly created row.

Fetched data - - -
Column headers
Insert blank Row
data 3
data 2
data 1


Hope thats clear. What I really need to do is paste them into the list in the other direction i.e. add each item to the bottom of the list instead of at the top and I can't find a neat way of doing it.

I've considered using a count function to count the items already in the list and try to do a cell reference based on the count+1 sort of thing but I could use a few pointers or if you know of a better way of doing this I'd apreciate any help.

Thanks in advance

 

[~Zodiac~]

Range("A2").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
Target=ActiveCell.Address

' now copy your data

Range(Target).Select
ActiveSheet.Paste

i hope this is what you search for

 

[RobShaw]

OK, so is A2 the beginning of the list?

I have 27 rows I think A6:I32, this is the potential list range everything else is above that, so in your formula, where you have A2 would I have A6?

And also would I need a dim Target? what is the data type for that,

Apologies very new to VBA.

 

[~Zodiac~]

yes A2 is the first cell in with data inside, in your case that would be A6

Target Can be defined as Dim Target, but that is not nessesary it works fine in my macro without declaring it that way.

There is no need to for apologies, everyone on this forum started sometime with less knowledge then the person has now.

I myself asked many questions similar to yours, i was glad to find a topic where i had an answer to.


good luck with your macro

 

[RobShaw]

Thanks for your help, the code worked a treat. How come I've never heard of cell.find function before? That could have saved me many many hours of frustraing work!

Thanks again

 

[RobShaw]

Just one last thing on cell.find (hopefully)

the bit where you have target = ...

I need to validate that to make sure its not overwriting my sum row, i tried putting an if (target >35) then loop but it always seems to pass, what data type is target in your example?