assign active cell address to range variable

Question

I get Error 1004 "Application-defined or Object-defined error" on this line:

 `ws2.range(dstRef).offset(srn,0).value= srn+1`

Why is that?

Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Packinglist_Annexure-1")

Dim srcRef, dstRef, tempAdr As Range
Set dstRef = Range("C19")

Dim k, srn As Integer
k = reqRow
srn = 0
For k = reqRow To row1
    ws2.Activate
    ws2.Range(dstRef.Address).Offset(srn, 0).Value = srn + 1
    ws1.Activate
    ws1.Range(reqAddr.Address).Offset(0, srn).Copy Destination:=ws2.Range(dstRef.Address).Offset(1, srn)
    srn = srn + 1
Next k

error 1004 is thrown At line ws2.range(dstRef).offset(srn,0).value= srn+1 — Spd, Mar 31 '18 at 10:00
You'd be best editing and expanding your question rather than adding comments beneath where they may not be noticed. — d219, Mar 31 '18 at 10:27
The code puts serial number in one column and data copied into another — Spd, Mar 31 '18 at 11:34
Your original question was valid and was answered, you've now altered the code for that original question - do you have a further question here? If so please make the original question clear and either add the additional question as subsequent text or as a new question. Thanks — d219, Mar 31 '18 at 12:18
@Spd, hey I've got the answer to your 2018-03-31 11:29:12Z state of the art question. Now, what's your next revised question? — DisplayName, Mar 31 '18 at 15:51

score 1 · Answer 1 · answered Mar 31 '18 at 10:04

you either use

ws2.Range(dstRef.Address).Offset(srn, 0).Value = srn + 1

or

ws2.Range("C19").Offset(srn, 0).Value = srn + 1

BTW you'd better explicily declare all your variables or they will be implicitly assume as of Variant type:

Dim ws1 , ws2 As Worksheet ' w1 is of Variant type and w2 is of Worksheet type

Dim ws1 As Worksheet, ws2 As Worksheet ' both w1 and w2 are of Worksheet type

score 1 · Accepted Answer · 2018-03-31T10:30:43.980

1

I believe you are looking for the range.address property although I'm unclear on why Range("C19") has no parent worksheet reference.

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Packinglist_Annexure-1")

Dim srcRef As Range, dstRef As Range, tempAdr As Range
Set dstRef = ws1.Range("C19")

Dim srn As Integer
srn = 0
ws2.Activate
ws2.Range(dstRef.ADDRESS).Offset(srn, 0).Value = srn + 1

Is the . in .Range necessary when defined by .Cells?

You need to declare all of the vartypes in a dim line.

dim a, b, c, d as string

The above only dimms d as a string; everything else is a variant.

edited Mar 31 '18 at 10:30

answered Mar 31 '18 at 10:08

Thanks address function worked but copy function is not working – Spd Mar 31 '18 at 10:42
If by *copy* you mean the value assignment (e.g. `...Value = srn + 1`) then it is working for me. – Mar 31 '18 at 10:48
text contents of one list initiated by ' apostrophe need to be transferred.Individual cell gets copied, but in loop it fails – Spd Mar 31 '18 at 11:36
1

Well, since I cannot see any loop in the code you provided I'll reserve judgement on that part of the question (which in any event has been added after the fact). – Mar 31 '18 at 11:39

score 0 · Answer 3 · answered Mar 31 '18 at 10:49

0

The error is occurring as you are trying to set the string (that specifies the cells to be used) as a range rather than a string. You can declare dstRef as a string and use this on that line e.g.

Dim srcRef, tempAdr As Range
Dim dstRef As String
Dim srn As Integer

dstRef = "C19"
srn = 0
ws2.Activate

ws2.Range(dstRef).Offset(srn, 0).Value = srn + 1

answered Mar 31 '18 at 10:49

d219

2,707
5
31
36

you are the third answering the same. from how it's going on here, since you are the last one then you get more chances to be marked as the accepted answer... – DisplayName Mar 31 '18 at 10:52
@DisplayName - hadn't seen the other answers, they didn't seem to show up when I came here from my review queue. Accepted answer has already been selected by the look of it anyhow though. – d219 Mar 31 '18 at 10:54
_"Accepted answer has already been selected by the look of it anyhow though"_, yes and it was the 2nd one – DisplayName Mar 31 '18 at 10:56
@spd - have you edited the code in the questions to a version that now works? If so you shouldn't do that as should someone come up with a similar issue they may not understand the problem you have from your question. – d219 Mar 31 '18 at 11:37

assign active cell address to range variable

3 Answers3