?
Solved

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

Posted on 2014-08-13
11
Medium Priority
?
401 Views
Last Modified: 2014-09-10
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?
0
Comment
Question by:dma70
  • 4
  • 4
  • 2
10 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40258905
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
 
LVL 35

Expert Comment

by:Norie
ID: 40259036
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
 
LVL 1

Author Comment

by:dma70
ID: 40259067
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Expert Comment

by:Norie
ID: 40259156
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
 
LVL 1

Author Comment

by:dma70
ID: 40259171
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
 
LVL 35

Expert Comment

by:Norie
ID: 40259189
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
 
LVL 1

Author Comment

by:dma70
ID: 40259208
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
 
LVL 35

Expert Comment

by:Norie
ID: 40259254
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
 
LVL 1

Accepted Solution

by:
dma70 earned 0 total points
ID: 40259263
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40314001
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question