How to soft code a cell location in a Range.Select statement in visual basic?

I have a visual basic module with the statement " Range(target).select".   I have the variable target set = cell N13"

target = "n13"

Now I want to make the cell (ie the value of the string assigned to target) variable.  that is I want to put the range.select statement in a loop where I vary the location of the "target" cell.   How do I accomplish this?
LVL 1
dma70Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
First you shouldn't use target as the variable name since Target is an Excel reserved word and it may get confused.

As to your question, do you mean something like this?

MyTarget = Selection.Range
0
NorieVBA ExpertCommented:
How do you want to loop?

By the way, Target is not a reserved wore but it can be unwise to use it under certain circumstances.
0
dma70Author Commented:
I just want the loop to progress from a starting row to an ending row and softcode the argument for the Range(Location).select statement.

So for example I want to loop thru rows 14 thru row 17 in column 10 (K).   I need to construct the 4 cell locations K14, k15, k16 and k17 without having to list them explicitly, as for example (Location="K14", then Location = "K15"....)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NorieVBA ExpertCommented:
You don't need to 'construct' the cell locations to do that, there are various ways you could handle it.

Here's one.
For Each Target In Range("K14:K17")

Next Target

Open in new window

0
dma70Author Commented:
is there a way to use the column number instead of the "K".  What I am really looking for is the ability to use variable row numbers and column numbers.  Also does your example do the same think as "selecting" the Range {ie equivalent to Range(xxx).select}
0
NorieVBA ExpertCommented:
Using the column number shouldn't be a problem.
For Each Target in Cells(14,10).Resize(4)

Next Target

Open in new window



However, I don't know why you want to use Select

In Excel VBA there is hardly ever (never?) a need to select.

What are you actually going to do with the cells you want to refer to?
0
dma70Author Commented:
I will admit I am probably not using it correctly -- I took the code from a record macro script and instead of repeating the series of commands explicitly I want to put them in a loop where I soft code the cell location.
 
Here is the code script:

Source = "m13"
    target = "n13"
 
    Sheets("output").Select
    Range(Source).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("sheet1").Select
    Range("BP10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BQ25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("output").Select
    Range(target).Select
 
   
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   


I want to be able to vary the variable "source" and "target"  for many cells in a the loop but want to reference row and column numbers (R1C1) rather than explicit cell locations.
0
NorieVBA ExpertCommented:
What, in words, do you want to do?

Obviously you want to copy from one sheet to another but it's not really clear which sheet you want to copy from and which sheet is the destination, or which cells/ranges are involved.

In the (recorded?) code you just posted it appears you are copying M13 from 'output' to BP10 on 'sheet1', then you are copying BQ25 on 'sheet1' to N13 on 'output'.

Or something along those lines.
0
dma70Author Commented:
yes -- I think I figured out a much simplier solution -- I just copy the value in on one sheet to the value in another sheet using worksheet(sheet name).cells(row, column)

thanks for your help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.