INPUT RANGE

[dancer]

In the following code I want to have the program ask me what range and then
substitute that range for "Dasher_accounts".

How?


For Each cell In ThisWorkbook.Sheets("lists") _
.Range("Dasher_accounts").Cells.SpecialCells(xlCellTypeConstants)

ThisWorkbook.Sheets("Sheet1").Range("crit_cell").Value = cell.Value
make_chart
ActiveWorkbook.Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Export
_
Filename:=Fname, FilterName:="GIF"

.Attachments.Add Fname

Next cell

 

[Dave Peterson]

Does the range have to be on Lists in the workbook with the code?



Dim myRng as range
dim myRngC as range
dim Cell as range

set myRng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select a range",type:=8)
on error goto 0

if myrng is nothing then
exit sub 'user hit cancel
end if

if myrng.parent.range("a1").address(external:=true) _
<> thisworkbook.worksheets("lists").range("a1").address(external:=true) then
msgbox "Please select a range on Lists in: " & thisworkbook.name
exit sub
end if

set myrngC = nothing
on error resume next
set myrngc = myrng.cells.specialcells(xlcelltypeconstants)
on error goto 0

if myrngc is nothing then
msgbox "no constants in that range!"
exit sub
end if

for each cell in myrngc.cells
'do your stuff.
....