creating loops

biker_boy

New Member
Joined
Dec 3, 2004
Messages
24
i need a create a macro which goes down a table of results, say A1 to A10 and then selects that range then link it to this macro and paste the value into the bold area, the reason that it needs to be a loop is that the table of results will increase and these need to be displayed in a drop down menu any ideas? thanks

ActiveSheet.Shapes("Drop Down 39").Select
With Selection
.ListFillRange = " '$A$1:$A$10 "
.LinkedCell = "$m$14"
.DropDownLines = 8
.Display3DShading = False
End With
End Sub
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
biker_boy,

This doesn't look clear to me :confused:

... selects that range then link it link what ?? to this macro and paste the value into the bold area which value in which bold area ?? , the reason that it needs to be a loop is that the table of results will increase and these need to be displayed in a drop down menu any ideas? this looks to me as a dynamic range do a search for that ...

could you provide an example of what precisely you're trying to achieve?

kind regards,
Erik
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

outside the macro create a dynamic name which will cover the reqired range. You can then hard code the code with the defined name.

.listfillrange = range("linkrange").

To create the name select Insert, Name, Define, add the name linkrange and in the refers to put in the formula
=Sheet1!$A$1:OFFSET(Sheet1!$A$1, COUNTA(Sheet1!$A:$A)-1,0)

HTH

Tony
 

biker_boy

New Member
Joined
Dec 3, 2004
Messages
24
sorry, what im trying to do is create a macro which will update the format range on a drop down menu , usually its a set range, which you define, but my problem is that the range will increase so i was thinking of creating a macro which would start at a set cell, say A1 then see if there was a value in that cell if there was move down to the cell, see if there was a value in there, this would carry on until it found a cell with no value in it. then it would select the range which all the values are in, say A1:A10 and paste it into the format range of the drop down menu, below if the macro i created for a set range, the bold bit is where the range would go, hope that helps

Sub frame_option_1()

'
ActiveSheet.Shapes("Drop Down 1").Select
With Selection
.ListFillRange = "$A$1:$A$10"
.LinkedCell = "$M$13"
.DropDownLines = 8
.Display3DShading = False
End With
End Sub
 
L

Legacy 98055

Guest

ADVERTISEMENT

Use:

.ListFillRange = Range([a1], [a65536].End(xlUp)).Address
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
A dynamic drop down list can be made with no use of VBA . Which means that the list will be modified even if the user does not have macros enabled. :wink:

Part 1 : Name a Dynamic Range
(this example uses colA of Sheet1 as dyno Range.
1. Goto tool Bar and Select ... Insert ...Name..define
1b Give your range a name (eg "DynoList" )
1c In the "Refers To" are copy this ...
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

1d. Ok you way out of Name Window

Part2: Make Drop down
Use Data Validation wizard to create drop down box
2a. goto Tool Bar...Data...Data Validation...
2.b. Select Settings Tab
2.c. In the Allow Box Select "List"
2.d. In the Source Box type the an equal sign and the Name of your dynamic range eg ....
=DynoList

Boomba OK your way out and your done.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
I would go with Nimrod & Tony's suggestion.
If you end up going with the code solution, use Right Click's listfill range code, and then at the end you can add this line:
SendKeys "{ESC}"
to deselect the dropdown box before ending your code.

Hope it helps,
Dan
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top