Hello Rooknyc,
I will refer to using the Immediate Window in Visual Basic to
illustrate the solution. If you want to follow along, enter Visual
Basic (Tools -> Macro -> Visual Basic Editor and then use View ->
Immediate Window.
There are at least a few different ways to solve this problem. With
your example using Range, you must construct a string with the
appropriate text. Using your example where variable R1 (not cell) has
value 3, you can enter something like...
print "B"&format(r1)&":G"&format(r1)
in the immediate window to produce the result
"B3:G3"
This is the correct string to provide to Range. I determined that
"format" was needed using a search in Visual Basic of convert number
to string which refers to the "Str" function. Unfortunately using Str
in place of format produces
"B 3:G 3"
which has extra spaces we do not need. The "See Also" under Str points
to Format which has the proper explanation and function.
Using this would lead to a solution like...
R1=3
S$="B"&format(R1)&":G"&format(R1)
ActiveSheet.Range(S$).Select
which will work properly for the example you provided.
There are other methods possible. For example, you can use...
- Cells(row,column) to refer to a single cell. Range requires two
cells, which looks something like
ActiveSheet.Range(Cells(3,2),Cells(3,8)).Select
to select the same cells as above. Note that Cells uses Row/Column
format and string values to Range use Column/Row format.
- If the value you need is in a Cell, use .Value of a cell reference
and assign it to a variable for manipulation. For example, if R1 above
was cell R1, you can use...
Dim R1 As Integer
R1 = ActiveSheet.Range("R1").Value
and the remainder of the above statements are the same. Note that R1
must be an Integer or you will get something like
B 5.000000 :G 5.000000
as the value of S$ (assuming cell R1 was 5) which is not a valid cell
reference.
If any of this is unclear, please let me know with a clarification
request so I can provide additional explanations. Good luck with your
work.
--Maniac |
Clarification of Answer by
maniac-ga
on
03 Jun 2003 15:07 PDT
Hello Rooknyc
I don't understand completely what you are trying to do, but a few
things to fix include...
- Dim Z$ as String is not a valid statement. When I pasted that into
Visual Basic, it is shown in red (invalid statement). You can declare
Z$ (which is a string already), but then you cannot use it as an
object later (assignment of a range). Since you don't use Z$ anywhere
else, I can't say for sure what it is for.
- In several places you use something like
Range("B" & A).Select
which is trying to concatenate a string with an integer. Use
Range("B" & Format(A)).Select
if you want a string "B2" (or whatever number A is) instead.
- at the end of the loop, you increment A and B, but then at the top
of the loop assign them again. Thus the increment is lost. If you
intend to change the value of the cell that A was set to (e.g.,
Range("A2").Value), you have to use something that references the
cell, not the variable.
Those are the problems I see without understanding what you are trying
to do. To do more, I may need to understand the purpose of the
subroutine and I would then suggest a solution that may be easier to
implement.
Another comment, as I mentioned in the original message, I suggest
trying the steps you are working with in the immediate window, see
what happens and then put them into the subroutine. The other method I
often use in debugging macros is to set a breakpoint (click on the
left side of the module window at the line you want to stop at); a red
circle (or stop sign) appears. When you reach that point in your code,
you can single step and point at variables to determine their value; a
pop up will show the value. Alternatively, you can use the immediate
window to print values, do calculations, etc.
--Maniac
|
