Macro that pics varied lengths of Rows

Hi skashyap and welcome to the forum,

For us to help, we must understand the question. If attach a workbook with what you have and what you expect as your answer, we have a much better time understanding. I simply don't understand what you are asking for.

Hi skashyap,

Sorry, but I must be dense. I still don't see what you want. Do you want to jump down to the gray row after putting a number in?
In your example if you type a number in A12 it would then jump down to A19? Is this what you are looking for?

What exactly triggers the jump? Should this only work for the specific number you give above?

Hi skashyap,

Is this an Excel question or an Access question? I'm still looking for why I don't understand the question. If it is an Access question, are you working with a form? What version of Access are you using? What view of forms are you dealing with? I'm not sure I'm much help with this question.

Access and Excel are much different. You still haven't told me what program you are using with your question. The Forum you posted the question in is for Access. Access uses forms and the VBA code for it is much different than the VBA for Excel. I haven't understood much of any of this question yet. You have given an Excel Workbook example but now you suggest you simply need the VBA to do your task. I'm still working on understanding the task. Wow, this is hard question to figure out.

OK skashyap,

I'm taking a wild guess on what you are asking for.

Open the attached and run the included macro. It will ask for a number that you supply. It will then place the active cell at the bottom of the comments in column B. I sure hope this is what you want.

Hi skashyap,

I'm an old retired guy who only checks this forum a few hours each day. We all work for free on this forum. NZ and Seattle are in completely different time zones. It is 7:30 am Saturday as I'm reading this post. I see it is 2:30 am Sunday in NZ. I just got up and drinking my second cup of coffee. I guess the answer is "Yes I'm here", but in a different timezone.

When you say "typed in a box", I have no idea if the box is a cell or a message box or something else. It looks like you have a poorly structured spreadsheet. It looks like you want to jump down to some account number like your "448795" as mentioned in post #13. I thought you would like my attached workbook above. Did you run the included macro in what I gave you? Did it make sense? What did it do or didn't do that you need? When you say "it must be on sheet 1", what is "it" referring to? I'm still pretty clueless on what you want and if what I've suggested helped you at all.

Could you step back from typing in a number and describe the whole process? What kind of business or data is this involved with? What are you trying to use the spreadsheet for? How does it help what you are trying to accomplish? These questions might help us solve some problem you are having.

OK skashyap try this one,

I'm sorry but you dropped off my radar. Good thing you PMed me or I wouldn't have found your new messages. I've also been on a holiday the last few days.
See if the attached is what you want. If you put a different number/text into the B2 cell on the Data Input sheet it will fire a macro.
This macro will look at the first sheet in the workbook and find the input number and return the text below it to the Data Input Sheet.

Is this what you are looking for finally? See the attached and let me know.

Hey skashyap,

The file is in .xls format and the macros are enabled. You simply need to accept the warnings. The warnings are because the macros are enabled. Here it is again.

I just tried it again and it works fine on my machine. I have no idea why it wouldn't work on your system.

Hi Skashyap,

You've private messaged me a few times saying you are only getting two columns of data moved from sheet 1 to sheet 2. You keep asking to know how to re-record the macro that does the work. The answer is that: Recording a Macro doesn't do exactly what is needed to solve your problem. I wrote the macro with more features than a recorded macro will do.

The above being said I think I have an answer. The variable "LastColumn" seems to be the problem if you are only getting two columns moved. Change the line of:

LastColumn = ActiveCell.Column
to
LastColumn = 6

In the code in the Module and see if this doesn't fix your problem.

The new code will then look like the below:

Please Login or Register  to view this content.