column variable into formula woes

[Susan]

in transferring data in these 2 sheets, the rows will always stay the
same, but the column will change..........

Private Sub CommandButton1_Click()

Dim wsRpt As Worksheet, wsCmp As Worksheet
Dim wb As Workbook
Dim q1 As Range, q2 As Range, q3 As Range
Dim q4 As Range, q5 As Range, q6 As Range
Dim q7 As Range, q8 As Range, q9 As Range
Dim q10 As Range, q11 As Range, q12 As Range
Dim q13 As Range
Dim r1 As Range, r2 As Range, r3 As Range
Dim r4 As Range, r5 As Range, r6 As Range
Dim r7 As Range, r8 As Range, r9 As Range
Dim r10 As Range, r11 As Range, r12 As Range
Dim r13 As Range
Dim rNumber As Range, rTotalNumber As Range
Dim OriginalNumber As Long
Dim Lx As Long, Cx As Long
Dim UpdatedAs As Date
Dim rUpdate As Range

'set everything up
Set wb = ActiveWorkbook
Set wsCmp = wb.Worksheets("Comparisons")
Set wsRpt = wb.Worksheets("Report")
Set rNumber = wsRpt.Range("w8")

OriginalNumber = rNumber.Value
UpdatedAs = Format(Now, "mm/dd/yy")

'the command .cells always has (row) first and then (column)

Cx = wsCmp.Cells(7, 1).Row
Lx = wsCmp.Cells(7, 200).End(xlToLeft).Offset(0, 1).Column
'MsgBox Cells(Cx, Lx).Address

Set rUpdate = wsCmp.Cells(6, Lx)
Set rTotalNumber = wsCmp.Cells(13, Lx)

'*****EVERYTHING ABOVE HERE WORKS

now comes going down in flames.......... first i tried
Set r1 = wsCmp.Range(Lx & "7:" & Lx & "12")
Set r2 = wsCmp.Range(Lx & "18:" & Lx & "23")

but that didn't work, because Lx is an integer, not a letter. (i
realize i can turn Lx into a string, but i want to see how you make it
work this way.)

after searching the newsgroup, i tried

With wsCmp
Set r1 = .Range(.Cells(7, Lx) & .Cells(12, Lx))
Set r2 = .Range(.Cells(18, Lx) & .Cells(23, Lx))
End With

but that won't work, either.
i also tried
Set r1 = .Range(.Range(.Cells(7,Lx) & .Cells(12,Lx))))
but no dice, there, either.

i'm getting a run-time 1004 error

can somebody please correct whatever syntax error i've got going on?
thank you very much!
susan

 

[JLGWhiz]

Try:

Set r1 = wsCmp.Range(Cells(7, Lx), Cells(12, Lx))
Set r2 = wsCmp.Range(Cells(18, Lx),Cells(23, Lx))

 

[Cory]

Susan,

I think you almost had it at the end there. Give this a try:

With wsCmp
Set r1 = .Range(.Cells(7, Lx) , .Cells(12, Lx))
Set r2 = .Range(.Cells(18, Lx), .Cells(23, Lx))
End With

If you pass two range objects to Range, it's like manually typing in the
absolute address. In other words Range("A1","B2") is the same as
Range("A1:B2"). Hope this helps.

-Cory

 

[Tom Ogilvy]

That will raise an error if wsCmp isn't the active sheet and if it is, then
you don't need to qualify. If you qualify one, you should qualify all (or
use With as she was already doing).

 

[Susan]

tom - yes, wsCmp is the active sheet. but this is just the first
round of range-setting. then there's another round on wsRpt before
actually transferring stuff. but i see what you mean, since this is a
private worksheet sub, then i shouldn't have to qualify which range
i'm setting if it's already on this worksheet. (can i do it anyway,
just for my own sake of mind? (using the with-end with))

cory - changing the "&" to a "," worked fine (using the with).

 

[Tom Ogilvy]

I think you missed the point.

First, I don't like preaching, so don't consider this preaching - merely my
option provided for illumination/consideration.

Given the specific situation you described,

It is always best to qualify all range references. Using With is certainly
an excellent approach to do this as you attempted and Cory showed. It is a
huge waste of time and an invitation for errors to partially qualify range
references such as suggested by JLGWhiz (which I am sure was just an
oversight while focusing on the real problem).
1) if on the active sheet or refers to ranges on the sheet associate
with the sheet module containing the code, it is unnecessary although it will
work - thus it is a waste of time and resources (although fully qualifying is
not a waste of time or resources but an attempt a robustness and clarity).
2) if it isn't on the active sheet or it is on the active sheet, but the
code is in a sheet module that won't be the activesheet, then it can and
probably will lead to errors and will be a point of consternation to the
programmer (or for people who inherit the code). (And if they knew the
difference probably wouldn't do it in the first place, so they won't
understand why they have the error - many post in this forum).

My opinion of course.

 

[Susan]

i am trying hard to learn to qualify all ranges, all the time, as you
described.

while qualifying sheet code ranges may be a waste of time & effort (to
some), i heartily agree with you that

a) it's a good habit to be in
b) ultimately it leads to better readability
c) is easier to debug

thanks for your opinions! the code works great now!
susan

finished code for reference:
=========================
Option Explicit

Private Sub CommandButton1_Click()

Dim wsRpt As Worksheet, wsCmp As Worksheet
Dim wb As Workbook
Dim q1 As Range, q2 As Range, q3 As Range
Dim q4 As Range, q5 As Range, q6 As Range
Dim q7 As Range, q8 As Range, q9 As Range
Dim q10 As Range, q11 As Range, q12 As Range
Dim q13 As Range
Dim r1 As Range, r2 As Range, r3 As Range
Dim r4 As Range, r5 As Range, r6 As Range
Dim r7 As Range, r8 As Range, r9 As Range
Dim r10 As Range, r11 As Range, r12 As Range
Dim r13 As Range
Dim rNumber As Range, rTotalNumber As Range
Dim OriginalNumber As Long
Dim Lx As Long, Cx As Long
Dim UpdatedAs As Date
Dim rUpdate As Range

If MsgBox("This will paste all current values on the Report " _
& "sheet into the next available column and date it. Continue?" _
, vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If

'set everything up
Set wb = ActiveWorkbook
Set wsCmp = wb.Worksheets("Comparisons")
Set wsRpt = wb.Worksheets("Report")
Set rNumber = wsRpt.Range("w8")

OriginalNumber = rNumber.Value
UpdatedAs = Format(Now, "mm/dd/yy")

'the command .cells always has (row) first and then (column)
Cx = wsCmp.Cells(7, 1).Row
Lx = wsCmp.Cells(7, 200).End(xlToLeft).Offset(0, 1).Column

Set rUpdate = wsCmp.Cells(6, Lx)
Set rTotalNumber = wsCmp.Cells(13, Lx)

'If you pass two range objects to Range, it's like manually typing in
the
'absolute address. In other words Range("A1","B2") is the same as
'Range("A1:B2"). - Corey

With wsCmp
Set r1 = .Range(.Cells(7, Lx), .Cells(12, Lx))
Set r2 = .Range(.Cells(18, Lx), .Cells(23, Lx))
Set r3 = .Range(.Cells(28, Lx), .Cells(33, Lx))
Set r4 = .Range(.Cells(38, Lx), .Cells(43, Lx))
Set r5 = .Range(.Cells(48, Lx), .Cells(53, Lx))
Set r6 = .Range(.Cells(58, Lx), .Cells(63, Lx))
Set r7 = .Range(.Cells(68, Lx), .Cells(70, Lx))
Set r8 = .Range(.Cells(75, Lx), .Cells(77, Lx))
Set r9 = .Range(.Cells(82, Lx), .Cells(84, Lx))
Set r10 = .Range(.Cells(89, Lx), .Cells(91, Lx))
Set r11 = .Range(.Cells(96, Lx), .Cells(98, Lx))
Set r12 = .Range(.Cells(103, Lx), .Cells(105, Lx))
Set r13 = .Range(.Cells(110, Lx), .Cells(113, Lx))
End With

With wsRpt
Set q1 = .Range("L8:L13")
Set q2 = .Range("L18:L23")
Set q3 = .Range("L28:L33")
Set q4 = .Range("L38:L43")
Set q5 = .Range("L48:L53")
Set q6 = .Range("L58:L63")
Set q7 = .Range("L68:L70")
Set q8 = .Range("L75:L77")
Set q9 = .Range("L82:L84")
Set q10 = .Range("L89:L91")
Set q11 = .Range("L96:L98")
Set q12 = .Range("L103:L105")
Set q13 = .Range("L110:L113")
End With

'now start moving everything
rUpdate.Value = UpdatedAs
rTotalNumber.Value = OriginalNumber
r1.Value = q1.Value
r2.Value = q2.Value
r3.Value = q3.Value
r4.Value = q4.Value
r5.Value = q5.Value
r6.Value = q6.Value
r7.Value = q7.Value
r8.Value = q8.Value
r9.Value = q9.Value
r10.Value = q10.Value
r11.Value = q11.Value
r12.Value = q12.Value
r13.Value = q13.Value

MsgBox "All values have been updated.", vbOKOnly

End Sub
=============================